I hunted for awhile but never could find a simple example online showing how to do a subquery. For example suppose you have the usual Posts and Comments models, but for whatever reason could not bind them on the usual “id” foreign keys. So if you wanted to query for all the Posts that have at least 10 Comments, while joining them on some key condition, you’d want to use a subquery like:
SELECT Post.id, Post.title FROM posts Post WHERE Post.id IN (SELECT p.id FROM posts p, comments c WHERE c.col_x = p.col_y AND c.total > 10)
I couldn’t find any sample code for this. After a lot of trial and error I was finally able to do this (using pagination):
$blocks = $this->paginate(
array(
'field1' => 'value1', // etc.
'1' => '1 AND Post.id IN (SELECT p.id FROM posts p, comments c WHERE c.col_x = p.col_y AND c.total > 10)'
));
Enjoy.
Hi ,
It makes no sense .. If you use find instead of paginate like this
$blocks = $this->ModelName->find (
array(
‘field1’ => ‘value1’, // etc.
‘1’ => ‘1 AND Post.id IN (SELECT p.id FROM posts p, comments c WHERE c.col_x = p.col_y AND c.total > 10)’
)
);
It will work ..
hello,
Getting some issue with the cakephp-3 panigation hope you can help me. My array is like this:
[business] => Array
(
[55] => Array
(
[id] => 1
[name] => abc
[contact] => 1325467897
),
[96] => Array
(
[id] => 5
[name] => xyz
[contact] => 9876543210
)
)
and its coming from multiple queries though. So the paginate definition says that you must pass the “Repository object” to it. So dont know what to do next. Could you just help me on this.
Thanks.
Sorry, this has only been tested with CakePHP 2.x – and the 3.0 branch is very different in this area. So I’d suggest you try one of the CakePHP 3.0 forums.