How to get parent's most viewed child in MySQL
I have a catalogue categories with items. The main catalogue page must display the list of categories with the amount of items inside of each category, and the "preview" picture must be the most viewed item of that same category.
SELECT catalogue_cat.id_cat, catalogue_cat.name, COUNT(catalogue_item.id_item) AS q FROM catalogue_cat LEFT JOIN catalogue_item ON catalogue_item.id_cat = catalogue_cat.id_cat GROUP BY catalogue_cat.id_cat ORDER BY catalogue_cat.name
That only brings me the list of categories with the item count. I still need the picture of the most viewed item of each category. Any ideas? Thanks!
You are already doing a
group by, so the best approach is the
group by approach:
SELECT c.id_cat, c.name, COUNT(i.id_item) AS q, SUBSTRING_INDEX(GROUP_CONCAT(i.url ORDER BY i.views DESC), ',', 1) as MostViewedURL FROM catalogue_cat c LEFT JOIN catalogue_item ci ON i.id_cat = c.id_cat GROUP BY c.id_cat ORDER BY c.name;
Note that this assumes that the URL's don't have commas in them. If they do, you will need to find another separator character.
- A Linux, Apache, MySQL, PHP (LAMP) stack is a combination of open source software that is typically installed together to enable a server to host dynamic […]
- MySQL is an the most popular open-source database management system, in most of hosting company its has been bundle by LAMP(Linux, Apache, MySQL, PHP/Python/Perl) stack. Another alternative […]
- MySQL is the world’s most popular open-source database. Despite its powerful features, MySQL is simple to set up and easy to use. Below are some instructions […]