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):

1
2
3
4
5
6

$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.