yii2 active record add limit with relation

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()



                'activities' => function ($query) {

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

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






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

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

They are something like the following:



des_id, des_name, des_type_classificatio

from destination

offset 0

limit 100




select *

from activity

where des_id in (…)

and status = 1

limit 3



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.