CODEWITHSUNDEEP
cakephpcountgroup-byhas-and-belongs-to-manycakephp-2.2
Nicolas
Nicolas

Reputation: 2814

CakePHP GROUP BY with WHERE and COUNT on HATBM table

Although I'm aware of the group, contain, fields and order options on a find(), I just cannot seem to make the following query with CakePHP 2.2.3 :

SELECT `User`.*, SUM(`Influence`.`id`) AS matches
FROM `db`.`users` AS `User` 
    LEFT JOIN `db`.`influences_users` AS `InfluencesUser` ON (`User`.`id` = `InfluencesUser`.`user_id`) 
    LEFT JOIN `db`.`influences` AS `Influence` ON (`InfluencesUser`.`influence_id` = `Influence`.`id`)
WHERE 1 = 1
AND `Influence`.`id` IN (1, 2, 3, 4)
GROUP BY `User`.`id` 
ORDER BY COUNT(`Influence`.`id`) DESC

Basically, I'm trying to retrieve an array of Users having the Influences 1, 2, 3 and 4, with the COUNT() function on the Influences tables, then order them by COUNT() DESC.

Is there a clean way (I'd rather not want to use any hacks or functions like Model::query()) to do so in CakePHP?


EDIT: It wasn't actually a SUM() but rather a COUNT() although I doesn't change anything to my problem.

Upvotes: 3

Views: 3019

Answers (3)

Nicolas
Nicolas

Reputation: 2814

I managed to find a solution, not too hacky and unless someone has a better solution (not a lot when you see the number of answers I got), here it is:

$this->User->virtualFields['matches'] = 'COUNT(InfluencesUser.influence_id)';
$matches = $this->User->find(
        'all',
        array(
            'joins' => array(
                array(
                    'alias' => 'InfluencesUser',
                    'table' => 'influences_users',
                    'type' => 'LEFT',
                    'conditions' => array(
                        'InfluencesUser.influence_id' => $userInfluences, //$userInfluences contains the list of influences I want to do the restriction on
                        '`InfluencesUser`.`user_id` = `User`.`id`'
                    )
                )
            ),
            'group' => 'User.id',
            'order' => array('User__matches' => 'DESC'),
        ));


I manually join the InfluencesUser table (I don't actually need the Influence one, only the list of influence_id) to make a big table. I just had to do the restriction (influence_id) on the join table, then I can do my goup by, the count() and append the count() field to my results array, thanks to mark's answer.

Obviously I would have prefered using something like the contain parameter, but it doesn't seem to allow me to do what I can achieve with the join in this particular situation.

Hope this could save some time to some people.

Upvotes: 2

mark
mark

Reputation: 21743

you can add virtual fields dynamically for only the next queries using

$this->virtualFields['matches'] = 'SUM(Influence.id)';

then you have it directly in your result array to work with:

echo $user['User']['matches']; // if you query on the User model that is

also you can use it on your order/conditions etc:

'order' => array('User__matches' => 'DESC')

You need to work on the model, that has all the other models as "belongsTo", though! So work with "InfluencesUser" and contain "Influence" and "User"

So in your case:

$this->User->InfluencesUser->virtualFields['matches'] = 'SUM(Influence.id)';
...
$results = $this->User->InfluencesUser->find(...)

and

echo $result['InfluencesUser']['matches']; //in the view

and

'order' => array('InfluencesUser__matches' => 'DESC')

Upvotes: 5

Leo
Leo

Reputation: 1529

how about a sub query?

http://book.cakephp.org/2.0/en/models/retrieving-your-data.html#sub-queries

I answered a question using this approach for a HATBM issue. It is a useful approach for complex queries.

Upvotes: 0

Related Questions