Cakephp 4 Sort belongstomany by joinData

Need Help
1

Hello,

I have a btm “through” association between 2 tables. The jointable has an extra field. I can access the joindata in the views and i can also save via _joinData to the jointable.

But I need to sort the retrieved data from the tables by the extra field of the jointable. (_joinData)

Is this possible and how to do it?

Thanks

2

If you look at the SQL generated for the query, you’ll see what name Cake is using to reference the join table, and you can then use that in your order call, which would need to be attached to the queryBuilder query for the contained table. It’s not clear from your question which part of this you have problems with; if this doesn’t get you to a solution, please share your find code for more specific help.

3

Thanks for the quick reply. To be honest, I really have no idea how to use the query builder since I am very new to cakephp. But I will try to explain my problem a little more.
Lets take the example from https://book.cakephp.org/4/en/orm/associations.html#using-the-through-option

Student BelongsToMany Course
Course BelongsToMany Student

and the association

id | student_id | course_id | days_attended | grade

I would like to show all Students with their associated Courses, BUT the courses should be ordered by the days_attended.

What I tried is:

$students = $this->Students->find(‘all’, [
‘contain’ => [‘Courses’],
‘order’ => [‘Students._joinData.days_attended’ => ‘DESC’]
]);

But this is not working.

4

Try this:

$students = $this->Students->find(‘all’, [
    ‘contain’ => [‘Courses’ => [
        'queryBuilder' => function (Query $q) {
            return $q->order([‘XXXX.days_attended’ => ‘DESC’]);
        },
    ]],
]);

The XXXX is not what you want there, but I don’t know what it should be. It might be StudentsCourses, it might be CoursesStudents, it might be something else. If you remove the entire order clause from what you have to far, and look at the actual SQL that gets generated for the query (by enabling query logging, or through DebugKit, for example), you should spot the name of your join table along with an “AS XXXX”, that XXXX is what you want in the code I’ve given above.

5

Worked like a charm :+1:
Thanks

6

I have similar issue.

My Users has many Bets, Bets belongTo Matches, Matches has TeamAs and TeamBs.
Now I want my Users View page to display all the Matches that user has bets , sorting by Matches.Match_Time

I tried - worked fine, but of course no sorting

$user = $this->Users->get($id, [
            'contain' => ['Bets.Matches.TeamAs' , 'Bets.Matches.TeamBs']]);

Below also works fine, but no sorting as well

$user = $this->Users->find('all', [            
            'conditions' => ['id' => $id],
            'contain' => ['Bets' => ['Matches' => ['TeamAs' , 'TeamBs']]] ])
->first();

After adding order clause, it says unknown column Matches.Match_Time

$user = $this->Users->find('all', [
            'conditions' => ['id' => $id],
            'contain' => ['Bets' => ['Matches' => ['TeamAs' , 'TeamBs']]],
            'order' => ['Matches.Match_Time' => 'DESC']
])->first();

Then I tried your approach, now page can not even load, it said: An Internal Server Error occured

$user = $this->Users->find('all', [
            'conditions' => ['id' => $id],
            'contain' => ['Bets' => ['Matches' => ['queryBuilder' => function (Query $q) {
                        return $q->order(['Matches.Match_Time' => 'DESC']);
            }]]]
        ])->first();

Please help, thank you very much.

7

Your last one looks pretty close. Your Cake logs should tell you more about the specific error, or else enable debug mode. Best guess is you have forgotten to use the Query class.

8

dear Zuluru,

My dev box enabled debug, and has debug kit installed. But this one seems bypass all of that, no any familiar GUI rendered, only 1 line of error message in white page returned.

9

Please excuse my poor English.

Instead of order in a subquery, you should use sort in the main query.

So this:

should work this way:

$user = $this->Users->get($id, [
            'contain' => ['Bets'=>['Matches'=>['TeamAs','TeamBs','sort'=>['Matches.Match_Time'=>'DESC']]]],
        ]);

edited the following::
here is the link to the documentation:
https://book.cakephp.org/4/en/orm/retrieving-data-and-resultsets.html#sorting-contained-associations

10

Nothing in logs/error.log?

11

Thank you Dirk,

You code run without any error, but the Matches are not sorted at all. I think your statement only sort matches related to each Bet, within each Bet. Another word, the sorting only apply to level 1 association, not level 2 and further, I think

12

The log has error as below

2021-06-27 06:28:43 Error: [TypeError] Argument 1 passed to App\Controller\UsersController::App\Controller\{closure}() must be an instance of App\Controller\Query, instance of Cake\ORM\Query given,
13

So, yeah. This guess was correct. Did you try that? Do you understand what I meant by it?

14

I added use clause on top of Controller file

namespace App\Controller;
use Cake\ORM\Query;

The code below works without runtime error but still not sorted

$user = $this->Users->find('all', [
            'conditions' => ['id' => $id],
            'contain' => ['Bets' => ['Matches' => ['TeamAs','TeamBs','queryBuilder' => function (Query $q) {
                        return $q->order(['Matches.Match_Time' => 'DESC']);
            }]]]
        ])->first();

What should I do next?

15

Your order clause is being applied to the list of matches (which should only be one?) on each bet, not to the list of bets on the user. I’d think the following might work. (I’ve added more line breaks and indentation to make it clearer what applies to what.)

$user = $this->Users->find('all', [
    'conditions' => ['id' => $id],
    'contain' => [
        'Bets' => [
            'Matches' => ['TeamAs','TeamBs'],
            'queryBuilder' => function (Query $q) {
                return $q->order(['Matches.Match_Time' => 'DESC']);
            }
        ]
    ]
])->first();

This wouldn’t work if there was a one-to-many relation between Bets and Matches (in which case the matches would be loaded by a separate query). But because Bets belongTo Matches, it should get both records with a join, and therefore the Matches.Match_Time field will indeed exist when it’s doing the Bets query.

16

Thanks a lot, Zuluru for your help.