Cakephp model associastion

I have thee following simple model:

I'm trying to get counts of items, grouped by catalogitem, for a certain section- the equivalent of:

SELECT catalogitem.id, count(*) FROM section LEFT JOIN catalogitem ON section.id=catalogitem.section_id LEFT JOIN item ON item.catalogitem_id=catalogitem.id WHERE section.id=5 GROUP BY catalogitem.id

So simple in sql, yet I can't get it to work with cake models. Can anyone point as to how to do it with cake models, using the model->find? I can't get it to group by correctly or join correctly on 3 tables :(

Edit: highly prefer to get the info in single query

Here's a longer way, "cakeish" way:

class Item extends AppModel
{
    /* snip */
    var $virtualFields = array('item_count' => 'count(Item.id)');

    function getCountForSection($sectionId)
    {
        $ca = $this->Catalogitem->find
            (
                'all',
                array
                (
                    'fields' => array('Catalogitem.id'),
                    'conditions' => array('Catalogitem.section_id' => $sectionId),
                    'recursive' => -1
                )
            );

        $ca = Set::extract('/Catalogitem/id', $ca);

        $ret = $this->find
            (
                'all',
                array
                (
                    'fields' => array('Item.catalogitem_id', 'item_count'),
                    'conditions' => array('Item.catalogitem_id' => $ca),
                    'group' => array('Item.catalogitem_id'),
                    'recursive' => -1
                )
            );

        return $ret;
    }
}

Then simply use it in your controller:

$ret = $this->Item->getCountForSection(1);
debug($ret);

How does it work:

• Define a virtual field (cake 1.3+ only AFAIK) which will count items
• Fetch all the Catalogitems belonging to a Section you're interested in
• Use Set::extract() to get the Catalogitems in a simple array
• Use the array of Catalogitems to filter Items while counting and grouping them

NB: You don't seem to be using Cake's naming conventions in your database. This may hurt you.

Sorry, in my first answer I somehow missed your GROUP BY requirement, which was the whole point of the question, I now realize. I haven't used this yet, but I came across it recently, and it looks like it might accomplish what you are looking for: Linkable Behavior.

http://planetcakephp.org/aggregator/items/891-linkable-behavior-taking-it-easy-in-your-db

Like Containable, but works with only right and left joins, produces much more compact queries and supports GROUP BY.

http://github.com/rafaelbandeira3/linkable

@azv

Would this work for you:

$section_id = 5;
$fields     = array('CatalogItem.id as CatalogItemId', 'count(*) AS SectionCount');
$conditions = array('Section.id' => $section_id);
$joins      = array(
    array('table' => 'catalogitem',
          'alias' => 'CatalogItem',
          'type' => 'LEFT',
          'conditions' => array('Section.id' => 'CatalogItem.section_id')
    ),
    array('table' => 'item',
          'alias' => 'Item',
          'type' => 'LEFT',
          'conditions' => array('Item.catalogitem_id' => 'CatalogItem.id')
));

$data = $this->Section->find('all',
                  array('fields' => $fields,
                        'conditions' => $conditions,
                        'joins' => $joins,
                        'group' => 'CatalogItem.id',
                        'recursive' => -1)
         );

// access your data values
foreach ($data['Section'] as $i => $datarow) {
    $catalogitem_id = $datarow['CatalogItemId'];
    $section_count  = $datarow['SectionCount'];
}

This way you are explicitly setting your joins and doing it all in one query. See here for more info on joins in Cake:

http://book.cakephp.org/view/1047/Joining-tables

Hope this helps. All the best,
-s_r