Using limit() on contained model

Question

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?

Vierra

For future reference: I ended up simply omitting the limit() and just use the first image in the array. All the answers, while working, seem like a workaround. The Cake ORM always uses a single query for contains, even if this is undesired (like when adding a limit to the query). – Vierra

Answer 1

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 [];
    }
])

Phipps

but i am not getting the last row ,i am getting the first one .can you plz suggest more? – Plebeian

Thank you i got it .Its really working. I would like to upvote you. – Plebeian

@Plebeian - how did you solve this? I'm unable to sort in the primary hasOne array and order does not work in the conditions query either. I can only seem to get first result. – Nomi

@Nomi check i have posted the codes ,it might be helpful to you. – Plebeian

Answer 2

Check it ,this one is my code

 $this->Orders->hasOne('Collections', [
                    'className' => 'Collections',
                    'foreignKey' => 'order_id',
                    'strategy' => 'select',
                    'conditions' => function (\Cake\Database\Expression\QueryExpression $exp, \Cake\ORM\Query $query) {
                    $query->order(['Collections.id' => 'ASC']);
                    return [];
                                                                                                                         }
                    ]);


                    $Lists = $this->Orders->find('all')->where($condition)->contain(['Collections'])->order(['Orders.due_date DESC']);
                    $this->set(compact('Lists'));

Plebeian

Answer 3

If the reason you are limiting it to one image is that you want to have a defaulted image. You might consider adding a default field in the image table and doing a Alias like this:

var $hasOne = array(
    'CoverImage' => array(
        'className' => 'Image',
        'conditions' => array('CoverImage.default' => true),
    ),

It looks like you are using Cake v3 so you can just add the equivalent association as the above is a 2.x sample.

Interview

Answer 4

In the Model definition we would do the following:

public $hasMany = [
    'ModelName' => [
        'limit' => 1,
        'order' => 'ModelName.field DESC'
    ],
];

as described in the docs: https://book.cakephp.org/2/en/models/associations-linking-models-together.html#hasmany

Gynandry