How could I create a sub-query in cakePHP?

mysql cakephp cakephp-model cakephp-2.3

Zimányi Gábor · May 4, 2013 · Viewed 10.5k times · Source

How could I create a sub-query in cakePHP with find method? For example:

SELECT *, (SELECT COUNT(*) FROM table2 WHERE table2.field1 = table1.id) AS count
FROM table1
WHERE table1.field1 = 'value'

!!! table2.field1 = table1.id !!!

Answer

Just for addition, you can build subquery using Cake's ORM. It will be more easy. Please read CakePHP retrieving data doc

In general you can use buildStatement() method of DataSource object. You can save all logic, including pagination etc.