jeudi 30 avril 2015

mysql subquery as if condition ( only if needed )

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