I've got an app that grants access to users in varying different ways, and I'm attempting to write a query that determines access in all the different ways, but is readable, by breaking the access out into CTEs.
This is the basic pattern I'm talking about:
WITH user_allowed_data1 AS (
SELECT u.id user_id, c.id content_id FROM …
),
user_allowed_data2 AS (
SELECT u.id user_id, c.id content_id FROM …
)
SELECT u.name, c.name FROM user_table
LEFT OUTER JOIN content_table ON 1=1
WHERE u.id, c.id in (SELECT user_id, content_id FROM user_allowed_data_1)
OR u.id, c.id in (SELECT user_id, content_id FROM user_allowed_data_2)
So, two questions:
Is there a better/faster/cleaner way to think about this query?
Is there a way to match multiple fields pulled out of a subquery in a where clause like that?
Aucun commentaire:
Enregistrer un commentaire