I want to perform a textsearch on a table containing posts belonging to topics within groups. Depending on the privacysettings for these groups I need to run a subquery to check if the requesting user is a member of the groups containg search matches.
Databasescheme:
Table: posts
Columns: id, group_id, title, text
Table: groups
Columns: group_id, privacy
Table: group_memberships
Columns: group_id, is_member
The privacy column in the group table contains an integervalue.
1 = public, anyone can access the data
2 = system, anyone can access the data
3 = private, only members can access the data
What the query should do:
1. Find some matches in the post table
2. Check the group privacy in the groups table -> a value HIGHER THAN 2 requires a membership check
3. Do a membership check on the group_memberships table if required
I really don't know how do handle this.
It looks like mysql supports two ways? IF statements and case expressions?
What would be a correct way for this?
PS: The subquery for membership checking should be optional and only firing if needed.
Aucun commentaire:
Enregistrer un commentaire