Yii2 ActiveDataProvider with leftJoin in query doesnt return the pagination pagesize items

Question

Am trying to return users with paycheck and loggedout at a certain time and return 10 items per each page but it fails to work

I have tried with the following code

          $filters = $arr["filters"];

        $timev = [strtotime($filters["duration_from"]), strtotime($filters["duration_to"])]
        
       $query = Users::find()
        ->leftJoin('tbl_truck_history', 'tbl_paychecks.user_id = users.id')
        ->leftJoin('tbl_security_login', 'tbl_security_login.user_id = users.id')
        ->where(["users.department"=>3])
       ->andWhere(['between', 'tbl_security_login.time_out', min($timev), max($timev)]);

         
    $data = new ActiveDataProvider([
        'query' => $query,
        'pagination' =>[
            'pageSize' => 10, //here per_page is
            'page' => $arr['page']
        ],
    ]);


    return ["data" => $data->getModels(),"totalRecords" => (int)$query->count()]

When i check on $data->getModels() it returns only 3 items in the array. What am i missing out

Answer 1

The problem: Your ids are not unique (which will be used as a key). Primary key will appear multiple times in your result, and it will treated as a same row.

Overcome this problem: You will need to "group by" your records. Maybe use users.id for this.

$query = Users::find()
...
->groupBy(['users.id']);

If group by is not an option for you, you will need to specify the key for the lines

class ActiveDataProvider extends BaseDataProvider 
...
    /**
     * @var string|callable|null the column that is used as the key of the data models.
     * This can be either a column name, or a callable that returns the key value of a given data model.
     *
     * If this is not set, the following rules will be used to determine the keys of the data models:
     *
     * - If [[query]] is an [[\yii\db\ActiveQuery]] instance, the primary keys of [[\yii\db\ActiveQuery::modelClass]] will be used.
     * - Otherwise, the keys of the [[models]] array will be used.
     *
     * @see getKeys()
     */
    public $key;

in your case:

    $data = new ActiveDataProvider([
        'query' => $query,
        'pagination' =>[
            'pageSize' => 10, //here per_page is
            'page' => $arr['page']
        ],
        'key' => static function($model) {return [new unique key] }
    ]);