CODEWITHSUNDEEP
phpcakephpgroup-byinner-join
Jannie Theunissen
Jannie Theunissen

Reputation: 30164

INNER JOIN with GROUP BY the CakePHP way

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');

Upvotes: 1

Views: 8592

Answers (2)

Jannie Theunissen
Jannie Theunissen

Reputation: 30164

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

Upvotes: 1

bfavaretto
bfavaretto

Reputation: 71918

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

Upvotes: 3

Related Questions