Sorting by deeply associated data

[LorenzoRogai]

This is a (multiple allowed):

• bug

• enhancement

• feature-discussion (RFC)

• CakePHP Version: 3.6.2

What you did

Tried to order by a deeply associated table

What happened

Even though the tables are defined with belongsTo and hasOne associations type CakePHP use subqueries on the deep levels to get the data and the fields we need to order are not present in the main query

Example:

Users.Articles.Posts

If i try to order by Articles.id it works fine because Articles is present as an INNER JOIN in the query

But ordering with Users.Articles.Posts.id (also tried with Articles.Posts.id, Posts.id) doesn't work because the Posts are being retrieved using a separate query

What you expected to happen

Could be useful to have a way to force it to use a single query so the ORDER BY clause will work or improve the documentation if there is already a way to do this

Thank you

[LorenzoRogai]

It does work by adding ->innerJoinWith('Users.Articles.Posts')

But does this additional innerJoin import the same data 2 times? Main query inner join + containable subquery?

[ADmad]

What's the association between Articles and Posts? Seems it's hasMany based on your problem description.

[LorenzoRogai]

It was only an example but they are all associated with hasOne, only the last one. "Posts" in this case, have a condition inside the table definition

[ADmad]

Please provide an actual example which can be used to reproduce your issue, not made up data.

[LorenzoRogai]

Actually i can't share these data, however this a general issue on all queries

You can create a 3 level associated tables to reproduce the problem

1 > 2 > 3

You can try to sort up to first and second level columns names but from the third it doesn't work anymore

[github-actions]

This issue is stale because it has been open for 120 days with no activity. Remove the stale label or comment or this will be closed in 15 days