[FIXED] How could I create a sub-query in cakePHP?

Issue

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 !!!

Solution

You can do this one of two ways:

1. Use $this->Model->query(...)

This allows you execute SQL directly using the query you posted above but be aware that it can make your application quite brittle if the database schema were to change. Probably the fastest.(Documentation)

Example

$this->Model1->query("SELECT * FROM model;");

2. Separate the Calls

This is probably not as fast as option 1 but it does give you the ability to break your query down into a number of steps. You're also unlikely to get SQL injection which is potential risk with option 1.

Example

$model1s = $this->Model1->find
(
    'all', 
    array
    (
        'conditions' => array('Model1.field1' => 'value')
    )
);
foreach($model1s as $model1)
{
    $model1['model2_count'] = $this->Model2->find
    (
        'count', 
        array
        (
            'conditions' => array
            (
                'Model2.field1' => $model1['id']
            )
        )
     );
}

Answered By - Sam Delaney

Answer Checked By - Dawn Plyler (PHPFixing Volunteer)