Extensive filtering

Yii 1.1 General Discussion
1

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!

2

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:


$requiredGroups=array(1,15,26);


$groupJoins=array();

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

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


$joins=implode("\n",$groupJoins);

EDIT:

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.

3

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.

4

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:


$user->inGroups(array(1,2,5))->findAll(....)