Cdbcriteria Sql Query

Yii 1.1 General Discussion
1

I am implementing this MYSQL Query using criteria but not getting the results which i am supposed to get.

SQL query:




SELECT `t`.`id`,`t`.`profile_id`,`t`.`type_id`,`t`.`status`

FROM `staff` as `t`, `hk_users_pending_invitation`

WHERE (`t`.`organisation_id`='value1') 

AND 

(`t`.`id` != `hk_users_pending_invitation`.`staff_id`);

Query using Criteria:


$criteria=new CDbCriteria();

$criteria->select='id,profile_id,type_id,status';

$criteria->compare('t.organisation_id',Yii::app()->user->organisationId);

$criteria->with=array('UsersPendingInvitations');

$criteria->together=true;

$criteria->addCondition('t.id != UsersPendingInvitations.staff_id');

Relation between Staff and HkUsersPendingInvitation model class is:

Staff model class:


return array(

	'UsersPendingInvitations' => array(self::HAS_MANY, 'HkUsersPendingInvitation', 'staff_id'),

);

Using MySql i am getting the results but not with the criteria query.

Thanx in advance.

2

How do you use the criteria to execute the query?

3

Using CActivedataProvider


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

	'criteria'=>$criteria,

	'pagination'=>array(

		'pageSize'=>15,

	),

));
4

In your manual query your inner join looks really weird. Usualy a join gets a condition like ‘table.fk = joinedTable.pk’. You reversed the operator. I think that is bad database schema design. Yii doesn’t support such relations.