Listing Categories, Counting Articles With One Query?

Logoleptic · 2007-12-05 05:58:40

I’m rewriting a category navigation plugin to make it compatible with rss_unlimited_categories, and decided I’d also try to cut back the number of queries it runs.

Instead of doing one query for each category to see how many articles the category contains (17 queries per page view in my case), I want to take care of fetching the categories and their article counts in one query. Here’s what I tried:

SELECT
  cat.id,
  cat.title,
  cat.parent,
  cat.name,
  postcount.articles
FROM
  txp_category AS cat
  LEFT JOIN textpattern_category AS uc ON cat.id = uc.category_id
  LEFT JOIN ( 
    SELECT
      uc1.category_id,
      COUNT(DISTINCT textp1.ID ) AS articles
    FROM
      textpattern AS textp1,
      textpattern_category AS uc1
    WHERE
      textp1.ID = uc1.article_id AND
      textp1.Status = '4'
    GROUP BY uc1.category_id ) AS postcount ON cat.id = postcount.category_id
WHERE
  cat.name != 'root' AND
  cat.type = 'article'
GROUP BY cat.id
ORDER BY cat.title ASC;

This worked just fine on localhost (MySQL 5.0.21), but produced the following error on my hosting provider’s server (MySQL 4.1.13):

SQL query: SELECT FOUND_ROWS( ) AS count;

MySQL said: #2013 - Lost connection to MySQL server during query

Have I tried something that 4.1 can’t do, or is joining to a subquery causing me to hit resource caps on the remote server? Some other problem?

My past SQL experience involved writing queries against DB2 on an AS/400, and I’m still learning where the compatibility and performance issues are with MySQL on a web server. Thanks in advance for any insights on this.

Mary · 2007-12-05 07:45:27

Sub-queries are still multiple queries. Yes, your query is probably somewhat intense.

Here ya go:

SELECT c.id, c.name, c.title, c.parent, COUNT(DISTINCT a.ID) as article_count 
	FROM txp_category AS c INNER JOIN textpattern_category AS c2a ON(c.id = c2a.category_id) INNER JOIN textpattern AS a ON(c2a.article_id = a.ID) 
	WHERE c.type = 'article' AND c.name != 'root' AND a.Status = '4' 
	GROUP BY c2a.category_id 
	ORDER BY c.title ASC;

ruud · 2007-12-05 08:21:15

If you also want to show categories that have 0 articles, use a LEFT JOIN between textpattern_category and textpattern.
Using a JOIN instead of a subquery has the advantage that the plugin will also work on MySQL versions below 4.1

Mary · 2007-12-05 08:55:53

Meh, none of these are working. Here’s the other one which comes closer:

SELECT c.id, c.name, c.title, c.parent, COUNT(a.ID) AS article_count 
	FROM txp_category AS c LEFT OUTER JOIN textpattern_category AS c2a ON(c.id = c2a.category_id) LEFT OUTER JOIN textpattern AS a ON(c2a.article_id = a.ID) 
	WHERE c.type = 'article' AND c.name != 'root' AND (a.Status = '4' OR a.Status IS NULL) 
	GROUP BY c2a.category_id 
	ORDER BY c.title ASC

Logoleptic · 2007-12-05 17:46:09

Thanks, guys. It’s been about a year and a half since I’ve touched much SQL code, and I can’t believe how rusty I’ve gotten. I appreciate the help.

Mary’s second query works almost perfectly. I just changed it to group by txp_category.id so that every single category shows up in the result set. I’ve also added (a.Posted <= NOW() OR a.Posted IS NULL) to the WHERE clause.

I’m basically trying to make an expanded version of getText() that includes a count of articles per category in the result array. Depending on what function_exists('rss_unlimited_admin_tab') has to say, it’ll run one of two queries.

Here’s what I’ve come up with for counting articles in non-unlimited categories. It works, but I’m wondering if it can be simplified. (The final version will include lft and rgt.)

SELECT
  cat.id,
  cat.title,
  cat.parent,
  cat.name,
  COUNT(textp.ID) AS articles
FROM
  txp_category AS cat
  LEFT OUTER JOIN textpattern AS textp
    ON ( cat.name = textp.Category1 OR cat.name = textp.Category2 )
WHERE
  cat.type = 'article' AND
  cat.name != 'root' AND
  (textp.Status = '4' OR textp.Status IS NULL) AND
  (textp.Posted <= NOW() OR textp.Posted IS NULL)
GROUP BY cat.id
ORDER BY cat.title ASC

Thanks again. :-)

Edit: Fixed query, which was counting articles wrong. Now it works.

Update: I’ve finished the modified getTree, and it works like a charm. The plugin now runs only two queries, regardless of how many categories there are. Thanks for your help!

Last edited by Logoleptic (2007-12-06 03:36:42)

Textpattern CMS

Textpattern CMS support forum

#1 2007-12-05 05:58:40

Listing Categories, Counting Articles With One Query?

#2 2007-12-05 07:45:27

Re: Listing Categories, Counting Articles With One Query?

#3 2007-12-05 08:21:15

Re: Listing Categories, Counting Articles With One Query?

#4 2007-12-05 08:55:53

Re: Listing Categories, Counting Articles With One Query?

#5 2007-12-05 17:46:09

Re: Listing Categories, Counting Articles With One Query?

Board footer