Multiple conditions on same column in JOIN

Hi,

I’m googling a lot on this but I’ve not yet found a correct solution to adopt in Yii. My problem: I have table user, table groups and table post. Now, every post can be assigned to one or more group and i would like to show to the current user only the posts assigned to the groups to which he belongs.

I have managed to do this in this way:





$posts = array();

        foreach($this->_user->groups as $group)

        {

            foreach($group->posts as $post)

                $posts[$post->id] = $post;

        }



but it’s not useful because I cannot perform a search for a particular filter (example: on category=x) given those conditions. In one word, I need a CActiveDataProvider query. The query should be similar to:





$dataProvider=new CActiveDataProvider('Post', array(

            'criteria'=>array(

                'with'=>array('groups'=>array(

                    'on'=>'groups.id=1',

                    'together'=>true,

                    'joinType'=>'INNER JOIN',

           	),

           	),

            ),

       	'pagination'=>array(

                'pageSize'=>3,

            ),

        ));



but of course with, like, ‘on’=>‘groups.id=1’, and ‘on’=>‘groups.id=2’, etc… so as many as the current user’s groups. Someone has an idea?

I’ve managed to solve this issue in this way:





$dataProvider=new CActiveDataProvider('Post', array(

            'criteria'=>array(

                'alias'=>'p',

                'with'=>array(

                    'groups'=>array(

                        'where'=>"groups.group_id IN ({$this->_user->getUserGroups()})",

                        'together'=>true,

                        'joinType'=>'INNER JOIN',

                    ),

                ),

                'group'=>'post_id',

                'order'=>'p.create_time DESC'

            ),

            'pagination'=>array(

                'pageSize'=>10,

            ),

        ));



basically I’ve used the in() operator in the JOIN statement. That works nicely. I’ve also implemented this on the search to keep the results coherents.