Yii Criteria: condition for relation

Question

i have two tables: User and User_works (User HAS_MANY User_works).

How can I add a condition to be displayed only users with certain works

User contains fields: id | name | Other information

User_works: id | user_id | work_id

User Model:

public function relations()
{       
  return array(
    'works'=>array(self::HAS_MANY, 'UserWorks',
    '',
    'on' => 'works.user_id=t.id',
    'together'=>false,
    ),
  )
}

Controller:

$criteria = new CDbCriteria();
$criteria->with = array('works');
$criteria->compare = ????

you want data from users with certain works mean you want to search from work_id from User_works table ? — Muhammad Rizwan Kaim Khani, Mar 11 '14 at 11:48

Taram · Accepted Answer · 2014-03-12T06:35:11.280

Solution:

I wanted something like that:

SQL "Select user.id from User, User_works Where User_works.user_id=User.id AND User_works.work_id=$SOMEVALUE"

User Model:

public function relations()
{       
  return array(
    'works'=>array(self::HAS_MANY, 'UserWorks',
    '',
    'joinType' => 'INNER JOIN', 
    'on' => 'works.user_id=t.id',
    'together'=>true,
    ),
  )
}

Controller:

$criteria = new CDbCriteria();
$criteria->with = array('works'=>array('on' => 'works.user_id=t.id AND (works.work_id=$SOMEVALUE OR ...)'));

As a result, I get the users with the necessary works.

But а new problem arose. The number of pages in the Listview not correctly displays. List view does not consider the condition of necessary works. As a result, a number of page is wrong.

Solution :

 $dataProvider=new CActiveDataProvider('User', array(
        'criteria'=>$criteria,      
        'pagination'=>array(
            'pageSize'=>1,
        ),
    ));
 $dataProvider->setTotalItemCount(count(User::Model()($criteria)));

or

Instead of setting the dataprovider criteria:

$dataProvider->criteria = $criteria

I set dataprovider->model criteria:

$dataProvider->model->setDbCriteria($criteria)

score 1 · Answer 2 · answered Mar 11 '14 at 08:20

1

$criteria = new CDbCriteria();
$criteria->with = array('works');
$criteria->compare('works.theField_name' , $someThing);

answered Mar 11 '14 at 08:20

Developerium

7,155
5
36
56

2

You have to set `$criteria->together = true`. – topher Mar 11 '14 at 08:21
Can i do it without option "together = true"? – Taram Mar 11 '14 at 08:23
I think you don't need `together`, when you have `with`, it's done – Developerium Mar 11 '14 at 08:27
where do you want to use this criteria? – Developerium Mar 11 '14 at 08:33
@Taram you should try out both. I've had a scenario where my filters weren't working as expected because `together` was not set to `true`. Read the [Yii Relation guide](http://www.yiiframework.com/doc/guide/1.1/en/database.arr#relational-query-options) for a better explanation. – topher Mar 11 '14 at 08:33
As a result i want: Two tables ("together=false" help me in this). The first table: Users with their personal information. And the second table: Users_id with work_id, where users_id can be repeated many times, because the user can have many works. – Taram Mar 11 '14 at 08:36
@tinybyte: $dataProvider=new CActiveDataProvider('Promouter', array( 'criteria'=>$criteria)); – Taram Mar 11 '14 at 08:39
what seems to be the problem? that dataprovider will make an array of Promouter objects, do you have problem showing it? – Developerium Mar 11 '14 at 09:00
i want that as a result were only users with certain work_id. I can not create this condition – Taram Mar 11 '14 at 09:04
I think you have to define the join type of that relation to `inner join`, because by default it's `left join`. – Developerium Mar 11 '14 at 09:09

Yii Criteria: condition for relation

2 Answers2