Grouping AND / OR clauses for sub-categories

Spudhead · #1 (**permalink**) 02-25-11, 06:24

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

it-iss.com · #2 (**permalink**) 02-25-11, 07:09

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.

Spudhead · #3 (**permalink**) 02-25-11, 07:22

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

r937 · #4 (**permalink**) 02-25-11, 10:34

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

Spudhead · #5 (**permalink**) 02-25-11, 11:19

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech