Extensive filtering

We’ve started the project for learning purposes at university’s computer club. We’re going to base it on Yii. Here’s the complete description of it: http://programmersnotes.info/2010/03/18/requirements-use-cases-sms-notification/

But I’m here not to showcase the project, but to discuss the filtering approach.

Here’s DB we came up with that allows flexibility and porting the solution to other sphere: http://programmersnotes.info/2010/03/19/database-sms-notification/

The problem appears when I want to find users that are both in, say, group 401 AND computer club. See the case #3 in the post above.

I came up with several possible approaches, but I feel they are not optimal. What good solution I can use here? How Yii’s AR features may simplify it? I need to search VERY fast and I’m OK to create helper tables in the background if needed, the data will not be changed frequently.

So the question is: how to solve this task efficiently in Yii or in plain SQL or PHP?

Thanks beforehand!

Just brainstorming: I’d try to add another INNER JOIN for every group a user must be in. If it should be fast, i’d use DAO to build that SQL quickly. Something like:



foreach($requiredGroups as $i=>$rg)

  $groupJoins[]="INNER JOIN UserGroup ug$i ON u.userID=ug$i.userID AND ug$i.ID=$rg";



Oh, and since i never needed that to be really fast, i never benchmarked it. Would be interested to hear, wether performance is degraded much, when you have a very long chain of INNER JOINs. Actually, i doubt that.

I’ve got the same proposal in other forum, on sitepoint and posted this variant on the blog as well.

Of all variants I see here, this is the fastest. The only drawback is using DAO which means I’ll not be able to use relations in the AR and similar things.

You could try to add the INNER JOINS to the join property of a CDbCriteria. That way you could also use it with AR. Then put that logic in a parameterized scope and you could use it like: