Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2002
    Posts
    189

    Unanswered: Grouping AND / OR clauses for sub-categories

    I'm building a custom query against a Wordpress database, to fetch posts by sub-category.

    The logic should work thus: subcategories in different categories should be "AND", but subcategories in the same category should be "OR". Perhaps this can be better described as matching:

    Code:
    match(
    (subcategory 4 OR 35)
    AND
    (subcategory 12 OR 8)
    )
    This is what I have, and it's not working right. It's not pulling results that match accross different parent categories: the query below returns no results, when there are posts that match one subcategory in each of the two IN() clauses. Can anyone explain what's going on, and how to put it right?

    Code:
    SELECT DISTINCT
      wposts.*
    FROM wp_posts wposts
    LEFT JOIN wp_term_relationships ON (wposts.ID = wp_term_relationships.object_id)
    LEFT JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)
    WHERE wp_term_taxonomy.taxonomy = 'category'
    AND wposts.post_type = 'project' AND (
      1=1
      AND wp_term_taxonomy.term_id IN(5,8)
      AND wp_term_taxonomy.term_id IN(16,17)
    )
    ORDER BY wposts.post_date DESC LIMIT 0, 16
    Any advice much appreciated

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    What field contains the category name? What is the relationship between category and subcategory? If you provide table layouts then we could probably help you.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Jan 2002
    Posts
    189
    Perhaps I've made this sound more complex than I need to:

    I don't think the relationship between categories and subcategories is important to the query; rather, I simply need to pass lists of subcategory ID's to the query. The results returned should match ANY subcategory in a given list, but it MUST match at least one category in each list. Does that help?

    I've pasted the CREATE statements for each of the relevant tables below, if that's any use:

    Code:
    CREATE TABLE `wp_posts` (
      `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
      `post_author` bigint(20) unsigned NOT NULL DEFAULT '0',
      `post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
      `post_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
      `post_content` longtext NOT NULL,
      `post_title` text NOT NULL,
      `post_excerpt` text NOT NULL,
      `post_status` varchar(20) NOT NULL DEFAULT 'publish',
      `comment_status` varchar(20) NOT NULL DEFAULT 'open',
      `ping_status` varchar(20) NOT NULL DEFAULT 'open',
      `post_password` varchar(20) NOT NULL DEFAULT '',
      `post_name` varchar(200) NOT NULL DEFAULT '',
      `to_ping` text NOT NULL,
      `pinged` text NOT NULL,
      `post_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
      `post_modified_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
      `post_content_filtered` text NOT NULL,
      `post_parent` bigint(20) unsigned NOT NULL DEFAULT '0',
      `guid` varchar(255) NOT NULL DEFAULT '',
      `menu_order` int(11) NOT NULL DEFAULT '0',
      `post_type` varchar(20) NOT NULL DEFAULT 'post',
      `post_mime_type` varchar(100) NOT NULL DEFAULT '',
      `comment_count` bigint(20) NOT NULL DEFAULT '0',
      PRIMARY KEY (`ID`),
      KEY `post_name` (`post_name`),
      KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`),
      KEY `post_parent` (`post_parent`),
      KEY `post_author` (`post_author`)
    ) ENGINE=MyISAM AUTO_INCREMENT=848 DEFAULT CHARSET=utf8
    Code:
    CREATE TABLE `wp_term_relationships` (
      `object_id` bigint(20) unsigned NOT NULL DEFAULT '0',
      `term_taxonomy_id` bigint(20) unsigned NOT NULL DEFAULT '0',
      `term_order` int(11) NOT NULL DEFAULT '0',
      PRIMARY KEY (`object_id`,`term_taxonomy_id`),
      KEY `term_taxonomy_id` (`term_taxonomy_id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8
    Code:
    CREATE TABLE `wp_term_taxonomy` (
      `term_taxonomy_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
      `term_id` bigint(20) unsigned NOT NULL DEFAULT '0',
      `taxonomy` varchar(32) NOT NULL DEFAULT '',
      `description` longtext NOT NULL,
      `parent` bigint(20) unsigned NOT NULL DEFAULT '0',
      `count` bigint(20) NOT NULL DEFAULT '0',
      PRIMARY KEY (`term_taxonomy_id`),
      UNIQUE KEY `term_id_taxonomy` (`term_id`,`taxonomy`),
      KEY `taxonomy` (`taxonomy`)
    ) ENGINE=MyISAM AUTO_INCREMENT=33 DEFAULT CHARSET=utf8

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try this --
    Code:
    SELECT wposts.*
      FROM wp_posts AS wposts
     WHERE post_type = 'project' 
       AND id IN
           ( SELECT wp_term_relationships .object_id
               FROM wp_term_taxonomy
             INNER
               JOIN wp_term_relationships 
                 ON wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
              WHERE wp_term_taxonomy.taxonomy = 'category'
                AND wp_term_taxonomy.term_id IN ( 5,8,16,17 )
             GROUP
                 BY object_id
             HAVING COUNT(CASE WHEN wp_term_taxonomy.term_id IN( 5,8 )
                               THEN 'humpty'
                               ELSE NULL END) > 0
                AND COUNT(CASE WHEN wp_term_taxonomy.term_id IN( 16,17 )
                               THEN 'dumpty'
                               ELSE NULL END) > 0
           )
    ORDER 
        BY wposts.post_date DESC LIMIT 0, 16
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2002
    Posts
    189
    Wow. That works beautifully, thank you. I did get an error on first trying it, something about truncated integer value for 'humpty', so I changed it to THEN 0 and bingo. I had no idea it'd be that complex a query; thank you again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •