yii2 active record add limit with relation

Yii 2.0 General Discussions
1

One destinations has many activities.

I want to put a limit a limit of 3 activities on the amount of returned activities for each destination

the way I insert the limit within WITH does not work

$destinations = Destination::find()

            ->select(['des_id','des_name','des_type_classification'])


            ->with([


                'activities' => function ($query) {


                    $query->andWhere(['status' => Order::STATUS_ACTIVE]);


                    $query->limit(3); /PROBLEM HEEEEEEERE


                },


            ])


            ->offset($offset)


            ->limit($100)


            ->all();
2

Try to write a plain SQL query that returns the result set you need and you’ll understand why it’s not possible. There’s no such thing in (standard) SQL as


LIMIT 3 FOR EACH related_id
3

Yii uses 2 sqls for your query that tries to eagerly load the relation.

They are something like the following:

[sql]

select

des_id, des_name, des_type_classificatio

from destination

offset 0

limit 100

;

[/sql]

[sql]

select *

from activity

where des_id in (…)

and status = 1

limit 3

;

[/sql]

With the 1st sql, the main records are fetched.

And with the 2nd sql, the related records are fetched at once. It uses the result of the 1st query in ‘in (…)’ to specify what records to retrieve, where (…) stands for the des_ids found with the 1st sql.