CODEWITHSUNDEEP
sqldatabasesqlite
Gustavo
Gustavo

Reputation: 1019

Get all children with all parents in SQL tree structure with SQLite

I have the following database model:

CREATE TABLE IF NOT EXISTS account (
    name VARCHAR PRIMARY KEY,
    parent INTEGER REFERENCES account(name) ON UPDATE CASCADE ON DELETE CASCADE 
);

CREATE TABLE IF NOT EXISTS permission (
    name VARCHAR PRIMARY KEY
);

CREATE TABLE IF NOT EXISTS account_permission (
    account VARCHAR NOT NULL REFERENCES account(name) ON UPDATE CASCADE ON DELETE CASCADE,
    permission VARCHAR NOT NULL REFERENCES permission(name) ON UPDATE CASCADE ON DELETE CASCADE
);

DELETE FROM account;
DELETE FROM permission;
DELETE FROM account_permission;

INSERT INTO account(name) VALUES('a1');
INSERT INTO account(name) VALUES('a2');
INSERT INTO account(name, parent) VALUES('a3', 'a1');

INSERT INTO permission(name) VALUES('dummy');
INSERT INTO permission(name) VALUES('edit');
INSERT INTO permission(name) VALUES('delete');

INSERT INTO account_permission(account, permission) VALUES('a1', 'dummy');
INSERT INTO account_permission(account, permission) VALUES('a2', 'edit');
INSERT INTO account_permission(account, permission) VALUES('a3',  'delete');

What I want is a query which returns all accounts which have a permission and their permissions inherited from the parent account. In this case I expect a result like this:

account | permission
a1 | dummy
a2 | edit
a3 | dummy
a3 | delete

a1 and a2 is nothing special but I have no idea how to write the recursive query to include a3 with all parent permissions and its own permission. Any help is appreciated.

Upvotes: 1

Views: 1295

Answers (2)

David דודו Markovitz
David דודו Markovitz

Reputation: 44951

P.s.
You should redesign the tables.
Account should have an ID and a NAME.
Permission should have an ID and a NAME.
Tables should be connected by IDs.

The iterative query returns tuples of accounts and ancestors (including self)

+---------+----------+
| account | ancestor |
+---------+----------+
| a1      | a1       |
+---------+----------+
| a2      | a2       |
+---------+----------+
| a3      | a1       |
+---------+----------+
| a3      | a3       |
+---------+----------+

The rest of the query is for matching each account`s ancestor with its permissions and removing redundancies. 

with        t 
            as
            (
                select      name     as account   
                           ,name     as ancestor

                from        account

             -- where       name = ... /* for a specific account */      

                union all

                select      t.account
                           ,a.parent    as ancestor

                from                t   
                            join    account as a
                            on      a.name = t.ancestor
                where       a.parent is not null
            )

select      distinct

            t.account
           ,ap.permission

from                    t
            join        account_permission  as ap
            on          ap.account  = t.ancestor

order by    t.account
           ,ap.permission     
;

Upvotes: 1

user7154703
user7154703

Reputation:

You will need one more query, which fetch parent permission and that can be merged with union. Here it is 

select * from  account_permission 
union 
select  a.name, ap.permission  from account_permission  ap,  account a where        a.parent = ap.account

Upvotes: 0

Related Questions