ansaurus

Question

MySQL Group by Multiple Columns on Joined Tables (WordPress)

Answer 1

+1 A:

update: I apologize for flubbing my understanding of your original question. I skimmed it and thought it was another example of the "top N from each group" question I see so frequently.

Here's a solution to your original question, which is the most recent five posts, with at most one post from each category:

SELECT t.name, p1.post_title, p1.post_date
FROM wp_term_taxonomy x
INNER JOIN wp_terms t ON (t.term_id = x.term_id)
INNER JOIN wp_term_relationships r1 ON (x.term_taxonomy_id = r1.term_taxonomy_id)
INNER JOIN wp_posts p1 ON (r1.object_id = p1.id AND p1.post_type = 'post')
LEFT OUTER JOIN (wp_term_relationships r2
    INNER JOIN wp_posts p2 ON (r2.object_id = p2.id AND p2.post_type = 'post'))
  ON (x.term_taxonomy_id = r2.term_taxonomy_id AND p1.post_date < p2.post_date)
WHERE x.taxonomy = 'category'
  AND r2.term_taxonomy_id IS NULL
GROUP BY x.term_id
ORDER BY p1.post_date DESC
LIMIT 5;

Here's the output in my test:

+--------+-------------------+---------------------+
| name   | post_title        | post_date           |
+--------+-------------------+---------------------+
| Pets   | Training Puppy    | 2009-06-11 22:57:51 | 
| DIY    | Bathroom Makeover | 2009-06-11 22:57:44 | 
| Food   | Posh Nachos       | 2009-06-11 22:57:35 | 
| Crafts | Sewing Buttons    | 2009-06-11 22:57:28 | 
+--------+-------------------+---------------------+

PS: Many thanks for posting the DDL and INSERT statements in your original question! Not many folks asking SQL questions do that.

Below is my first answer, based on my incorrect understanding of the question:

So you want all posts such that there are fewer than five other posts with the same category term and a more recent post_date?

You need to join to the posts table twice:

SELECT x.*, t.*, r1.*, p1.*
FROM wp_term_taxonomy x
 INNER JOIN wp_terms t ON (t.term_id = x.term_id)
 INNER JOIN wp_term_relationships r1 ON (r1.term_taxonomy_id = x.term_taxonomy_id)
 INNER JOIN wp_posts p1 ON (p1.id = r1.object_id)
 INNER JOIN wp_term_relationships r2 ON (r2.term_taxonomy_id = x.term_taxonomy_id)
 INNER JOIN wp_posts p2 ON (p2.id = r2.object_id AND p2.post_date > p1.post_date)
WHERE x.taxonomy = 'category'
 AND p1.post_type = 'post' AND p2.post_type = 'post'
GROUP BY x.term_id, p1.id
HAVING COUNT(*) < 5;

Bill Karwin 2009-06-11 22:53:02

Hi Bill - great honor to have you answer my question! I will give this a go - however from your explanation I think my description of the problem wasn't good (I should probably ask these questions before midnight). What I'm after is simply the five latest posts, but none of them may have the same category term. If I understand correctly, your query returns all of the 'latest' posts, with a maximum of five posts from each category?

2009-06-11 23:12:03

Yes, the latter is what my query above does. I didn't understand your original problem. I'll try to amend my answer with a query that does solve that problem. Stand by...

Bill Karwin 2009-06-11 23:19:41

Okay I've posted a new solution above. I've left my original (wrong) answer too, in case it helps anyone else in the future. :-}

Bill Karwin 2009-06-11 23:36:55

ansaurus

tags:

views:

answers:

MySQL Group by Multiple Columns on Joined Tables (WordPress)

related questions