Question:
How can I display a list of items per category?
Answer:
You can do this via an SQL query:
{exp:query sql="SELECT count( exp_category_posts.entry_id ) AS count,
exp_category_posts.cat_id, exp_categories.cat_name
FROM exp_categories, exp_category_posts
WHERE exp_category_posts.cat_id = exp_categories.cat_id
GROUP BY exp_categories.cat_name"}
<a href="{path=weblog/page}C{cat_id}/">{cat_name}</a> ({count})
<br />
{/exp:query}
You may need to change the path= to fit your particular needs.
NOTE: This code does not take into acount that you can reuse the same categories in different weblogs. Neither table listed here has any sort of weblog information in it.
If you need to get a count of entries from a specific site (MSM only), weblog and category group, use the following query:
{exp:query sql="SELECT count( exp_category_posts.entry_id ) AS total_items,
exp_category_posts.cat_id, exp_categories.cat_name as category_name, exp_categories.cat_url_title as category_url_title
FROM exp_categories, exp_category_posts, exp_weblog_titles
WHERE exp_category_posts.cat_id = exp_categories.cat_id
AND exp_categories.site_id = 8
AND exp_categories.group_id = 40
AND exp_weblog_titles.entry_id = exp_category_posts.entry_id
AND exp_weblog_titles.weblog_id = 83
GROUP BY exp_categories.cat_name"}
If your site is not running the Multiple Site Manager, remove the first “AND” clause. Replace the number 40 with the category group ID you’d like to list and specify your target weblog_id in place of the number 83.
The query above will populate “total_items” with the number of items assigned to that specific category. “category_name” and “category_url_title” are also returned to allow easy category page linking.
You’ll need to have your landing page not have a weblog= item in it, and you’d need to check for the weblog before deciding what to display. You could make the landing area just a link of titles instead.
Here’s a little more complex query. This one returns categories only that have a certain parent, and sorts them in the order that they’d normally appear.
{exp:query sql="SELECT count( exp_category_posts.entry_id ) AS count,
exp_category_posts.cat_id, exp_categories.cat_name, exp_categories.cat_description
FROM exp_categories, exp_category_posts
WHERE exp_category_posts.cat_id = exp_categories.cat_id
AND exp_categories.parent_id = 3
GROUP BY exp_categories.cat_name
ORDER BY exp_categories.cat_order"}
<li> <a href="{path=candyblog/category}C{cat_name}/">{cat_description}</a> ({count})</li>
{/exp:query}
You can find number of entries posted into category by using Entries Number plugin.
You can get all categories(including categories which have no entry) by this SQL:
{exp:query sql="SELECT
count( exp_category_posts.entry_id ) AS count,
exp_category_posts.cat_id,
exp_categories.cat_name
FROM exp_categories LEFT OUTER JOIN exp_category_posts
ON exp_category_posts.cat_id = exp_categories.cat_id
GROUP BY exp_categories.cat_name
"}
<li> <a href="{path=candyblog/category}C{cat_name}/">{cat_description}</a> ({count})</li>
{/exp:query}