[FIXED] Using limit() on contained model

Issue

The Code

Say I have two models, named Product and Image, which are linked by Product hasMany Image and Image belongsTo Product.

Now, say I want to fetch all products with the first image each. I would use this code:

$this->Products->find('all')
    ->contain([
        'Images' => function($q) {
            return $q
                ->order('created ASC')
                ->limit(1);
        }
    ]);

Looks about right, right? Except now only one of the products contains an image, although actually each product contains at least one image (if queried without the limit).

The resulting Queries

The problem seems to be with the limit, since this produces the following two queries (for example):

SELECT
    Products.id AS `Products__id`,
FROM
    products Products

and

SELECT
    Images.id AS `Images__id`,
    Images.product_id AS `Images__product_id`,
    Images.created AS `Images__created`
FROM
    images Images
WHERE
    Images.product_id in (1,2,3,4,5)
ORDER BY
    created ASC
LIMIT 1

Looking at the second query, it is quite obvious how this will always result in only one image.

The Problem

However, I would have expected the Cake ORM to limit the images to 1 per product when I called limit(1).

My question: Is this an error in how I use the ORM? If so, how should I limit the number of images to one per image?

Solution

The cleanest way you can do this is by creating another association:

$this->hasOne('FirstImage', [
    'className' => 'Images',
    'foreignKey' => 'image_id',
    'strategy' => 'select',
    'sort' => ['FirstImage.created' => 'DESC'],
    'conditions' => function ($e, $query) {
        $query->limit(1);
        return [];
    }
])

Answered By - José Lorenzo Rodríguez