Only select results with rows in another table

Hey all,

Looks like I need your help once more ;)

I create a CActiveDataProvider as follows:




        $dataProvider = new CActiveDataProvider(Member::model()->search(), array(

            'pagination' => array(

                'pageSize' => 24,

            ),

        ));



The Member model has a scope named search:




    public function scopes()

    {

        return array(

            'search' => array(

                'with'      => 'user',

                'condition' => 'active = 1 AND approved = 1,

                'order'     => 'update_time'

            )

        );

    }



So far so good, everything works.

In a third table photo however, the members can have none, one or more photos.

For my dataProvider I now would like to select only those members with at least one photo.

Both Member and Photo have a column user_id which is the User’s id.

I tried adding another scope with ‘photo’ and a condition, but only got “Integrity constraint violation” warnings. I guess because both member and photo have a user_id column.

Any ideas?




$criteria->addCondition( 'EXISTS ( SELECT * FROM photo WHERE photo.user_id = t.user_id )' );



Awesome redguy, works like a charm!

Thanks for the idea of EXISTS and t.user_id :)

"t" is default alias for leading table in AR query. no matter if you query for User::model()->findAll(), or Article::model()->findAll() - every time main table will have alias "t" (user table in first case, article table in second). You can change this default alias, but there will always be one… this is specific to Yii ActiveRecord implementation.

EXISTS is SQL operator which return true if specified subquery returns any rows and false if subquery result is empty.