CakePHP Convert SQL To Paginate

Question

I have an SQL statement that returns the results I want, however I need to paginate these reults and have been having trouble the past couple days trying to get the query converted into $this->paginate

I am working with two tables that have a habtm relationship; items and item_owns. item_owns table contains 3 columns: id, item_id and user_id, while the items table contains id, collection_id, and name.

SQL that returns results I need:

SELECT it.id, it.name, COALESCE(count,0) as count 
FROM items as it 
LEFT OUTER JOIN (
    SELECT count(ito.item_id) as count, ito.item_id 
    FROM item_owns as ito 
    WHERE ito.user_id='4ffb9027-8c0c-4086-a13e-01a0cab1739a' 
    GROUP BY ito.item_id
) as ct ON ct.item_id = it.id
WHERE it.collection_id='4fc923f5-0a58-453f-9507-0c0c86106d80';

The below snippet gets me close, however I end up having to perform the counting of item_owns by user_id (grouped by item_id) outside the paginate function which means I can't sort based on the count...

$this->paginate = array(
'limit' => 5,
            'conditions' => array('Item.collection_id'=> $id ),
            'fields' => array('Item.id', 'Item.collection_id', 'Item.name'),
            'contain' => array(
                'ItemOwn'=>array(
                    'fields' => array('ItemOwn.id, ItemOwn.item_id, ItemOwn.user_id'),
                    'conditions' => array('ItemOwn.user_id' => $this->Session->read('Auth.User.id')),

                    )
                )
            );

I've also tried using 'joins' => array(), but not sure how I specify the "count" call in the fields array

$this->paginate = array(
    'limit' => 5,
    'fields' => array('Item.id', 'Item.collection_id', 'Item.name', 'count(ItemOwn.item_id)'),
    'conditions' => array('Item.collection_id'=> $id, 'ItemOwn.user_id' => $this->Session->read('Auth.User.id') ),
    'joins' => array(
        array(
            'alias' => 'ItemOwn',
            'table' => 'item_owns',
            'foreign_key' => false,
            'type' => 'LEFT OUTER',
            'conditions' => array('ItemOwn.item_id = Item.id'),
            'group' => 'ItemOwn.item_id'
            )
        )
    );

I suspect I may need to use custom pagination function, but again I'm not sure where some of the JOINs and conditions would go in that...

Any ideas are greatly appreciated! Thanks MK

Nick Zinger Nick Zinger · Accepted Answer · 2013-02-12T09:40:12

You're using contain which is good, but you should also be using Cake's counter cache for the counts.

CakePHP Convert SQL To Paginate

1 Answers