0

This question describes exactly what I want to do, but I want to do it in CakePHP 2.10. Is it possible? Is it in general possible to 'unroll' sub-expressions in SQL? I've tried just about every combination of which I can think, and generally receive the

Expression #n of SELECT list is not in GROUP BY clause and contains nonaggregated column 'database.table.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

I understand why (there are plenty of explanations on StackOverflow), and want to fix it properly, but within the constraints of the CakePHP 2.10 framework. I also want to avoid having to rewrite or restructure a lot of existing code into which this usage must fit, so don't want to use prepared statements or direct query() calls. Is it possible?

Michael restore Monica Cellio
  • 851
  • 1
  • 9
  • 21
  • You probably want to explain what you mean by "_unrolling subexpressions_", and also show your query builder code. Without any details, there's not much besides "yes", "no", "maybe" that one could give you as an answer. – ndm Aug 28 '21 at 12:29
  • Based on the error, you're probably using MySQL or MariaDB. `sql_mode=only_full_group_by` is a configuration setting for those databases. When you have that kind of issue, there are two (2) basic paths. 1) that the noted expressions are properly functionally dependent on the group by terms and the database just doesn't handle the issue, or 2) they are not and your intended / desired result can not be known without more logic. Both have solutions, but will depend on requirements you haven't yet specified. Update the question with your full requirement, SQL, test case, etc. – Jon Armstrong Aug 28 '21 at 12:37
  • For case (1), the expression(s) can be written in the form of an aggregate: MIN(expression), MAX(expression), see also ANY_VALUE(x), etc, and the result should be as you expected. For case (2), more detail is required. – Jon Armstrong Aug 28 '21 at 12:40
  • So what you are asking for is basically "_how to use subqueries in joins?_". You're almost there with your "workaround": **https://stackoverflow.com/questions/49168867/how-to-create-a-join-that-uses-a-subquery** – ndm Aug 29 '21 at 12:16

1 Answers1

0

Let's say you have a social media app. where you have users, and users have 0 or posts. You want a list of users with their most recent (highest-numbered) post, excluding users who have no posts.

To get a list of (user, post-id) pairs:

select U.id,MAX(P.id) as maxPid from users U join posts P on U.id=P.user_id group by U.id;

then what you'd like to do is get all the details of the post along with its owner/user. So the first attempt is

select U.id,MAX(P.id),P.headline as maxPid from users U join posts P on U.id=P.user_id group by U.id;

which doesn't work as we all know because of Expression #n of SELECT list is not in GROUP BY clause.... What you have to write is:

select uid,C.headline from posts C join (select U.id,MAX(P.id) as maxPid from users U join posts P on U.id=P.user_id group by U.id) as M on C.id=M.maxPid;

and what I was asking is if there isn't some way of flattening-out the query to a three-way join, because Cake 2 can't really express the nested query. I don't think there is, so here's how I worked around it:

// Cake find('all', ...) term for the sub-selection.
$selection = [ 'conditions' => ..., 'joins' => ... ];
$dbUser = $this->User->getDataSource();
// Create SQL therefrom:
$joinSubExpr = $dbUser->expression(' join (' . $dbUser->buildStatement($selection, $this->User) . ') as subJ')->value;
// 'Normal' Cake query with generated sub-selection text.
$results = $this->Post->find('all', [
    'conditions' => [ 'Post.id= subJ.maxPid' ],
    'joins' => [ $joinSubExpr ],
    ...]);

That's all highly condensed-down from my real code, for the sake of simplicity. Please comment if it is unclear.

Michael restore Monica Cellio
  • 851
  • 1
  • 9
  • 21