CakePHP (2.0) - Paginate Model with $hasMany Association - with custom join, group, order by other model
Yeah, that's right. Try to digest that title for a moment.
Looking for this solution took me like a day before I could even formulate my specific problem and was the reason to open this blog. To give you what I wish I had.
And once you know what you are trying to do it's not even that complicated. (Cut me some slack, I'm still starting out and this is my first CakePHP project)
So we have a website that displays high scores of users:
UserModel
// id, username, ...
$hasMany = 'Ranking';
RankingModel
// id, score, ..., user_id
$belongsTo = 'User';
Task: Simple. Now we want to display the users in the UsersController along with their highest score, the highest ranking user first. Only some at a time, of course.
The View looks like this:
<?php foreach($users as $user): ?>
<div class="userindex">
<h1><?php echo $user['User']['username']; ?></h1>
<p>
Best Score:
<?php
echo ($user['Ranking']['score']) ? $user['Ranking']['score'] : "None"; ?>
</p>
</div>
<?php endforeach; ?>
Sooo how do we get the data in?
First you have to understand how the hasMany relationship works. While belongsTo requests both associated tables (users and rankings) with an SQL JOIN right away (so you can access them in the pagination immediately), the hasMany requests all the user data first, then caches the IDs of those users, and then does a new request for all the rankings relating to those user IDs.
But we don't want ALL the rankings that belong to the displayed users! We don't need all their columns and most of all we want to display the users by the RANKINGS.
That means the users would be requested up to the set LIMIT regardless of their rankings, ordered by their ID or something else, and after you get their ranking information the best you could do is order the users you already have by the rankings they got, even if those users only own mediocre rankings compared to all other users.
Solution: To get what we want we have to force a table join in the first request, then find the highest rankings of unique users on the database level, and then get them ordered by this newly generated column.
Generally it helps if you type out the database query step by step and test it in our admin panel:
SELECT User.username , MAX( Ranking.score ) AS score
/* That MAX gives us the highest score in the table. Giving it an alias is just good practice.*/
FROM users AS User
LEFT JOIN rankings AS Ranking ON User.id = Ranking.user_id
GROUP BY User.id
/* If we didn't group by User.id, it would only return the highest ranking overall. */
ORDER BY score DESC
LIMIT 18
To get this query in a CakePHP pagination (or find('all'); for that matter) we'd have to do the following:
// UsersController
public $paginate = array(
'fields' => array(
'User.username',
'MAX(Ranking.score) as score'
),
'limit' => 18,
'joins' => array('LEFT JOIN rankings AS Ranking ON User.id = Ranking.user_id'),
'group' => 'User.id',
'order' => 'score DESC',
'recursive' => -1
);
Yes, turned out simpler than I would have thought earlier. Now don't ask me about the strange 'joins'. This must be a bug in CakePHP 2.0stable. It only worked this way for me, the pure SQL code inside an array. They will probably fix this at some point so you can use the standard parameters that joins-option allows you.
And there you have it: Users listed by their greatest achievements. Hope that helped! Comments are welcome.
gummygunk
areudirty
baratheonpilled
prguitarman
jbbartram-illu
andulkaphoto
izzy806