Limit contained record - not working!

I have Users model which has many CovidTests.

I query all users containing only last CovidTests of each user with query below:

$users = $this->Users->find('all',
             'contain' => ['Vaccinations', 'Healths', 'Vaccine1', 'Vaccine2', 'Groups', 'CovidTests' => function ($q) {
                              return $q->order(['test_date' => 'DESC'])
                                            ->limit(1)
                             ;}]
             ,'order' => ['firstname' => 'ASC']
             ,'conditions' => ['is_deleted' => false ]
             ]);

The result is that I have all users, but only the 1 user content CovidTest while there are a lot of users has CovidTest in database.

That means the statement ->limit(1) applied to main function find of Users model, not the contained CovidTests

Is this a bug or ORM is designed that way?

Its a known thing. Can’t use the limit like that. As far as I know

1 Like

That’s due to how the ORM works, associated data is either retrieved in a separate query (hasMany/belongsToMany), or in a join (hasOne/belongsTo, this can also be changed to use a separate query). For the former if you apply a limit, it will limit the overall number of associated records, not the records “per parent”.

You could give icings/partitionable a try. Also check cakephp - How to limit contained associations per record/group? - Stack Overflow for some additional workarounds.

1 Like

thank you so much NDM