Recursive querry over 3 Tables

All we need is an easy explanation of the problem, so here it is.

I have the following table structure

create table folder
(
    id   integer
        constraint folder_pk
            primary key,
    path TEXT
);

create index folder_path_uindex
    on folder (path);

create table membership
(
    id     integer
        constraint membership_pk
            primary key,
    member integer
        references user,
    groups integer
        references user
);

create index membership_group_member_uindex
    on membership (groups, member);

create table rule
(
    id        integer
        constraint rule_pk
            primary key,
    folder    integer
        references folder,
    principal integer
        references user,
    type      INTEGER,
    level     integer
);

create table user
(
    id   integer
        constraint user_pk
            primary key,
    name integer,
    type text
);

create index principal_name_type_uindex
    on user (name, type);

I need to walk through the groups recursively to find out if a user is efectivly a member of a group and than join against the Rule table to get all rules relevant to the user. The rules may directly apply to a user or to a group. A group can be a member of another group.

How would I start with this and is it even possible in SQLite?

I am a little bit stuck on this. Any input is appreciated

–EDIT–

I think i have found a solution. I will write an awnser

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

I use a view to connect the user and membeship table in oder to self join them.

CREATE VIEW RecursoinView as
SELECT u.id as UserID, u.name  as Name, m.groups as groups , u.type as type     FROM user u join membership m on u.id = m.member;

After that i querry based on the user.id with the following SQL statement:

WITH    q AS 
        (
        SELECT  *
        FROM    RecursoinView
        WHERE   RecursoinView.UserID=398 
        UNION ALL
        SELECT  m.*
        FROM    RecursoinView m
        JOIN    q
        ON      m.UserID = q.groups
        )
SELECT  *
FROM    q join rule on q.groups = rule.principal join folder f on rule.folder = f.id

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Tags: ,

Leave a Reply