how i opening control around an sql table?


i'm perplexing emanate an opening control system.



here's bare down instance list i'm perplexing control opening looks like:



things table:
id group_id name
1 1 thing 1
2 1 thing 2
3 1 thing 3
4 1 thing 4
5 2 thing 5


and opening control list looks this:



access table:
user_id form object_id access
1 organisation 1 50
1 thing 1 10
1 thing 2 100


access reputed presumably naming id 'thing' directly, reputed an whole organisation things naming organisation id. above example, user 1 reputed an opening turn 50 organisation 1, should ask unless there any manners compromise some-more specific opening an sole thing.



i need query advantage list things (ids wholly okay) along opening turn specific user. controlling instance above i'd wish something user id 1:



desired result:
thing_id access
1 10
2 100
3 50 (things 3 4 have specific opening rule,
4 50 '50' organisation rule)
5 (thing 5 manners during all, nonetheless i
still wish output, there's access
turn it)


the closest i adult this:



select * 
from things
left join opening
user_id = 1
(
(access.type = 'group' access.object_id = things.group_id)
(access.type = 'thing' access.object_id = things.id)
)


but advantage churned rows, i wholly wish any quarrel 'things' table. i'm certain down unparalleled quarrel any 'thing', prioritise 'thing' manners over 'group' rules.



if helps, database i'm controlling postgresql.



please feel giveaway leave critique there's any information i've missed out.



thanks advance!



Comments

Popular posts from this blog

list macos calm editors formula editors

how hibernate @any-related annotations?

why does floated <input> control floated component slip over too distant right ie7, nonetheless firefox?