Select criteria?

Yii 1.1 General Discussion
1

is there a way to select all database results with a many many relationship that do not have a relation?

I have a Role model and Permissions model, but how would I get all permissions that are not in the jointable?

Something like…




Permissions::model()->with(

            array(

                'roles'=>array( 'condition' => Yii::app()->controller->module->tbl_role_has_permissions.'.role_id != '.$role_id),

        ))->findAll();

will not work cause it does not do the parameter on the join table :(

The normal sql would probably look something like…


select * from permissions

	left join role_has_permissions

		on permissions.id=role_has_permissions.permissions_id

	where role_has_permissions.permissions_id is null;
2

I didn’t understand clearly what you have to do.

Maybe you can simply specify the join condition with CDbCriteria->join?

Btw it seems that your sql is equivalent to:




select * from permissions

where id NOT IN (SELECT permissions_id FROM role_has_permissions);

and you can do with where (but I think that the first one solution is more efficient)

3

You would likely want to specify ‘joinType’ => ‘LEFT JOIN’, then in your condition, specify the column that would be NULL.

Or instead of using where for the condition, you could specify joinType as mentioned, and then also specify ‘join’ => ‘col1 = col2 AND col2 IS NULL’.