Yii: Pagination error when together = true. How to fix?

Question

Yii

I've been fighting for the third day. Googled ad nauseam. Kindly point me in the right direction.
Situation: there are users on the site. Users have certain characteristics that are stored in related tables. You need to find users that match certain characteristics.
Problem: shown below in the screenshots
Model Users

public function relations() {
    return array(
        'usersMotivations' => array(self::HAS_MANY, 'UsersMotivations', 'userID'),
    );
}

Controller Action

public function actionIndex() {
    $criteria = new CDbCriteria;
    $criteria->with = array('usersMotivations');
    $criteria->addInCondition('motivationID',array(1,2));
    $criteria->together = true;

    $dataProvider = new CActiveDataProvider('Users',array(
        'criteria'=>$criteria,
        'pagination'=>array('pageSize'=>10)
    ));

    $this->render('index', array( 'dataProvider' => $dataProvider));
}

Performance

$this->widget('zii.widgets.CListView', array(
    'dataProvider'=>$dataProvider,
    'itemView'=>'_view',
));

And this is where the most interesting thing happens. With 'pagination'=>array('pageSize'=>10) the result page looks like this:

With 'pagination'=>array('pageSize'=>2) one of the results is duplicated on two pages

Now if you remove the line $criteria- >addInCondition('motivationID',array(1,2)); then one of the results is duplicated, and the other one disappears altogether

. Empirically, I found out that $criteria->together = true is to blame; and if you remove it, then everything becomes normal with pagination, but then the search condition stops working

$criteria->addInCondition('motivationID',array(1,2));

How to be, tell me please?

Answer 1

Try

$criteria->group='t.user_id';

where t.user_id is the PK of the Users model.
Plus, because you have the task of not choosing all the motivations of the user, you can make such a feint: add a relay

'usersMotivation' => array(self::HAS_ONE, 'UsersMotivations', 'userID'),

And use the same criteria

$criteria = new CDbCriteria;
$criteria->with = array('usersMotivation');
$criteria->addInCondition('motivationID',array(1,2));

if I'm not mistaken, it will work even without

$criteria->together = true;

Answer 2

This is because of the one-to-many relationship. You need something like this:

public function actionIndex() {
    $criteria = new CDbCriteria;
$criteria->addCondition(" (SELECT COUNT(*) FROM `UsersMotivations` WHERE userID = t.ID AND `motivationID` IN (:motivationID)  ) > 0 ");
$criteria->params[":motivationID"] = implode(',',array(1,2));

    $dataProvider = new CActiveDataProvider('Users',array(
        'criteria'=>$criteria,
        'pagination'=>array('pageSize'=>10)
    ));

    $this->render('index', array( 'dataProvider' => $dataProvider));
}