CakePHP 3 limit of association does not works

Question

Below are the lines from cakephp documentation which does not work.

Changing Fetching Strategies

As you may know already, belongsTo and hasOne associations are loaded using a JOIN in the main finder query. While this improves query and fetching speed and allows for creating more expressive conditions when retrieving data, this may be a problem when you want to apply certain clauses to the finder query for the association, such as order() or limit().

For example, if you wanted to get the first comment of an article as an association:

$articles->hasOne('FirstComment', [
     'className' => 'Comments',
     'foreignKey' => 'article_id'
]);

In order to correctly fetch the data from this association, we will need to tell the query to use the select strategy, since we want order by a particular column:

$query = $articles->find()->contain([
    'FirstComment' => [
        'strategy' => 'select',
        'queryBuilder' => function ($q) {
            return $q->order(['FirstComment.created' =>'ASC'])->limit(1);
        }
    ]
]);

THanks

"_doesn't work_" is not a proper problem description! Even if the problem might be obvious for people that know the CakePHP internals, please always be as specific as possible as to what _exactly_ happens, and what _exactly_ you'd expect to happen instead. Please don't just dump Cookbook examples, show us _your_ associations, _your_ code, _your_ data, and describe your specific problem, even if you're trying to replicate a Cookbook example - thanks! — ndm, Dec 13 '17 at 13:05
in my case there are many comments for a single article, now I need to know how to get latest comment for each article — Pankaj, Dec 18 '17 at 11:33
That is what you should elaborate on in your question. **https://stackoverflow.com/questions/30241975/how-to-limit-contained-associations-per-record-group** — ndm, Dec 18 '17 at 13:49
I am not getting that can you customize according to my need thanks — Pankaj, Dec 20 '17 at 13:25

score 2 · Answer 1 · answered Dec 13 '17 at 17:34

When working with hasOne note that CakePHP will strip the ORDER BY clause from the query after the queryBuilder is called. The queryBuilder is used to create the joining conditions for the JOIN clause. There is no SQL syntax that allows a ORDER BY clause inside an ON (expression) for a join.

You also have to use a SELECT strategy for hasOne if you want to use ORDER BY.

You can get around this issue by using a custom finder.

    $articles->hasOne('FirstComment', [
        'className' => 'Comments',
        'foreignKey' => 'article_id',
        'strategy' => Association::STRATEGY_SELECT,
        'finder' => 'firstComment'
    ]);

In your CommentsTable class define a custom finder which sets the order.

    public function findFirstComment($q) {
        return $q->order([$this->aliasField('created') =>'ASC']);
    }

CakePHP won't strip the ORDER BY clauses for hasOne when added by custom finders.

Note: The custom finder has to be in the association's target, not the source table.

same time can you explain how to get three latest comments for each article — Pankaj, Dec 18 '17 at 11:35
applying limit in query builder add a limit clause in 2nd query so this is not useful — Pankaj, Dec 27 '17 at 06:35
adding a limit applies the limit to the entire query. So if there are 5 articles and if you want first comment of each article, you will only get only one comment in total. — Raj, Jun 13 '20 at 11:44

CakePHP 3 limit of association does not works

1 Answers1