Mysql – How to filter posts with multiple category names in WordPress

databaseMySQLWordpress

I am trying to work around a plugin for Word Press called "Event Calendar 3". This plugin lets you create events and then feeds them into a SQL table in the WordPress database. However, this plugin does not discriminate between different types of events, eg repeating events, one-off events.

The beginning of my solution was to create a category called "Repeating Event", and when I created an Event post, also tagged it Repeating Event. I then tried to structure a query that would return posts that were categorized "Events," but would also exclude "Repeating Events."

$query = SELECT *
         FROM wp_posts p, wp_ec3_schedule s, wp_terms ts, wp_term_taxonomy t, wp_term_relationships r
WHERE p.ID = s.post_ID
AND r.term_taxonomy_id = t.term_taxonomy_id
AND ts.term_id = t.term_id
AND name != "Repeating"

The trouble is, posts that have the category "Repeating" also have the category "Events" (they have to in order to show up in the calendar). So the posts that have the category "Repeating" still show up because their category 'name' is not just "Repeating," it's "Events" as well!

The WordPress function query_post() doesn't help because not only do I have to filter by category (which query_post() does), but I also need to be able to specify that the rows returned by the query are only those that have a start date after today (there is a column in the wp_ec3_schedule table called 'start' that lists the start time of the event).

Any suggestions?

Thx.

Best Answer

Not sure that I understand everything, but take a look at this example. This lists post_id of posts on my site which are in category how-to, but are not in categories database and code.

SELECT  `object_id` AS PostID
FROM    `wp_term_relationships` AS r
        JOIN `wp_term_taxonomy` AS x ON x.`term_taxonomy_id` = r.`term_taxonomy_id`
        JOIN `wp_terms` AS t ON t.`term_id` = x.`term_id`
WHERE   x.`taxonomy` = 'category'
        AND `slug` = 'how-to'
        AND `object_id` NOT IN
           (
            SELECT `object_id`
            FROM `wp_term_relationships` AS r
            JOIN `wp_term_taxonomy` AS x ON x.`term_taxonomy_id` = r.`term_taxonomy_id`
            JOIN `wp_terms` AS t ON t.`term_id` = x.`term_id`
            WHERE   x.`taxonomy` = 'category'
            AND `slug` IN  ( 'database', 'code' )
            );

Related Solutions

Mysql – How to find all the tables in MySQL with specific column names in them

To get all tables with columns columnA or ColumnB in the database YourDatabase:

SELECT DISTINCT TABLE_NAME 
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE COLUMN_NAME IN ('columnA','ColumnB')
        AND TABLE_SCHEMA='YourDatabase';

MySQL Group by Multiple Columns on Joined Tables (WordPress)

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;

Best Answer

Related Solutions

Mysql – How to find all the tables in MySQL with specific column names in them

MySQL Group by Multiple Columns on Joined Tables (WordPress)

Related Topic