INNER JOIN with GROUP BY the CakePHP way

Question

I'm writing a plug-in module for a system that uses CakePHP 1.2 and I am new to the framework. How does one do the following query the Cake way?

SELECT a.id, a.name, COUNT(a.id) AS comments
FROM articles a LEFT JOIN comments c ON a.id = c.item_id
GROUP BY a.id
ORDER BY comments DESC;

I can't edit the Article or Comment models, but I'm trying this in my plug-in's model and it doesn't give the same result:

$this->loadModel('Article');
$options['fields'] = array('Article.id', 'Article.name', 
              'COUNT(Article.id) AS comments');
$options['joins'] = array(
    array('table' => 'comments',
          'alias' => 'c',
          'type' => 'INNER',
          'conditions' => array(
          'Article.id = c.item_id')
         ));
$options['group'] = array('Article.id');
$options['order'] = 'comments DESC';
$options['limit'] = 5;
$rows = $this->Article->find('all', $options);

Also, I'm not sure but I think the Article class might already have:

 public $actsAs = array('Containable');
 public $hasMany = array('Comment');

In your plain sql you have a `LEFT JOIN`, in the Cake you define `type => INNER`. Is it intentional? — biziclop, Mar 19 '12 at 19:26
I believe INNER is more correct and you use LEFT and RIGHT only with OUTER joins, but in my experience MySQL treats JOIN, LEFT JOIN and INNER JOIN all identical — Jannie Theunissen, Mar 20 '12 at 17:42
No, `INNER` and `LEFT` (`LEFT OUTER`) joins are treated differently. However mysql will trat a `LEFT JOIN` as an `INNER JOIN` if you add further conditions on the joined table to the `WHERE` clause. — bfavaretto, Mar 20 '12 at 18:17
`INNER` vs `LEFT`: http://stackoverflow.com/questions/38549/sql-difference-between-inner-and-outer-join http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html — biziclop, Mar 20 '12 at 18:28

bfavaretto · Answer 1 · 2012-03-20T18:13:28.183

I think you also need to add foreignKey => FALSE to your join definition:

$options['joins'] = array(
    array('table' => 'comments',
          'alias' => 'c',
          'type' => 'INNER',
          'foreignKey' => FALSE,
          'conditions' => array('Article.id = c.item_id')
     )
);

Also, if you are forcing the joins yourself, you should get rid of any previous Cake-style associations, either by passing recursive => FALSE as an options, or by applying unbindModel to each associated model.

UPDATE

Based on what you said in the comments, here is what I think you need:

$options['fields'] = array(
    'Article.id', 
    'Article.name', 
    'COUNT(DISTINCT c.id) AS comments'
);
$options['joins'] = array(
    array(
        'table' => 'comments',
        'alias' => 'c',
        'type' => 'LEFT OUTER',
        'foreignKey' => FALSE,
        'conditions' => array('Article.id = c.item_id')
    )
);
$options['group'] = array('Article.id');
$options['order'] = 'COUNT(DISTINCT c.id) DESC';
$rows = $this->Article->find('all', $options);

Sorry to report that I have tried all three suggestions with no luck. Only one row is returned and it has the wrong comment count. — Jannie Theunissen, Mar 20 '12 at 18:03
+1 for the LEFT OUTER join. It didn't solve my problem, but it is a better query for my use case — Jannie Theunissen, Mar 21 '12 at 12:26

score 1 · Accepted Answer · answered Mar 21 '12 at 07:56

1

Very unexpected answer:

The version of CakePHP used by this project (which I can do nothing about) is version 1.2.0.6311 beta released on 2008-01-02 and according to this article the group by functionality in Cake's model find method was only added in May 2008

answered Mar 21 '12 at 07:56

Jannie Theunissen

28,256
21
100
127

Ha! I'd never figure that one out! So you'll have to update Cake, or forget about the "cake way" and simply run your query with `Model::query`. – bfavaretto Mar 21 '12 at 15:08
Yes, I sanitize my parameters and use $this->query(...) – Jannie Theunissen Mar 21 '12 at 19:32

INNER JOIN with GROUP BY the CakePHP way

2 Answers2