1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
SELECT
    theAro.model AS RequesterModel,
    theAro.foreign_key AS RequesterId,
    theAco.model AS ControlModel,
    theAco.foreign_key AS ControlId,
    permissions._create AS CanCreate,  
    permissions._read AS CanRead,  
    permissions._update AS CanEdit,
    permissions._delete AS CanDelete   
FROM
    acos AS theAco
INNER JOIN
    acos AS ruleAco ON (
        ruleAco.lft <= theAco.lft AND ruleAco.rght >= theAco.rght)
INNER JOIN
    aros_acos AS permissions ON (
        permissions.aco_id = ruleAco.id)
INNER JOIN
    aros AS ruleAro ON (
        permissions.aro_id = ruleAro.id)
INNER JOIN
    aros AS theAro ON (
        theAro.lft >= ruleAro.lft AND theAro.rght <= ruleAro.rght)
WHERE
    theAro.model = "$string" AND
    theAro.foreign_key = $int AND
    permissions._update != 0 /* AND
    NOT EXISTS ( similar query, checking that the found rule is the bottom rule) */
 
 
****OR****
# Even more expensive but (probably) accurate
 
SELECT
    *
FROM
    acos AS theAco
WHERE
    (SELECT
        permissions._update
    FROM
        aros_acos AS permissions
    INNER JOIN
        acos AS ruleAco ON (
            ruleAco.id = permissions.aco_id)
    INNER JOIN
        aros AS ruleAro ON (
            permissions.aro_id = ruleAro.id)
    INNER JOIN
        aros AS theAro ON (
            theAro.lft >= ruleAro.lft AND theAro.rght <= ruleAro.rght)
    WHERE
        ruleAco.lft <= theAco.lft AND
        ruleAco.rght >= theAco.rght AND
        theAro.model = "$string" AND
        theAro.foreign_key = $int
    ORDER BY
        theAro.lft DESC, ruleAco.lft DESC
    LIMIT 1
) = 1
 
****OR****
# List permissions per object remove WHERE TO find ALL permissions
 
SELECT
    theAco.id AS AcoId,
    permissions.id AS RuleId,  
    permissions.aro_id AS RuleAroId,   
    permissions.aco_id AS RuleAcoId,   
    permissions._create AS CanCreate,  
    permissions._read AS CanRead,  
    permissions._update AS CanEdit,
    permissions._delete AS CanDelete   
FROM
    acos AS theAco
INNER JOIN
    aros_acos AS permissions ON (
        permissions.id = (
            SELECT
                permissions.id
            FROM
                aros_acos AS permissions
            INNER JOIN
                acos AS ruleAco ON (
                    ruleAco.id = permissions.aco_id)
            INNER JOIN
                aros AS ruleAro ON (
                    permissions.aro_id = ruleAro.id)
            INNER JOIN
                aros AS theAro ON (
                    theAro.lft >= ruleAro.lft AND theAro.rght <= ruleAro.rght)
            WHERE
                ruleAco.lft <= theAco.lft AND
                ruleAco.rght >= theAco.rght AND
                theAro.model = "$string" AND
                theAro.foreign_key = $int
            ORDER BY
                theAro.lft DESC, ruleAco.lft DESC
            LIMIT 1
        )
    )
WHERE
    permissions._update = 1