Cakephp, Select top 5 results then group the rest to "Other"?
How do i view the top 5 results and group the rest under "Other" in
Cakephp 2? If i have a table "visits" with the field "page", it has 100
rows and i need results to be like:
Other 40
/ 20
/about 15
/contact 10
/welcome 10
/test 5
This is one way to do it with regular Mysql:
SELECT case when v2.page is not null then v1.page else 'other' end AS
viewpage, COUNT(*) AS count
FROM visits v1
LEFT JOIN
(
SELECT page, COUNT(*) as pcount
FROM visits
GROUP BY page
ORDER BY pcount DESC
LIMIT 5
) v2 on v1.page = v2.page
GROUP BY viewpage
ORDER BY count DESC
But how to do this in Cakephp 2? Thought it would be easy when i had the
Mysql query made up...
No comments:
Post a Comment