1

I have created a custom post type and taxonomy for a goods catalogue for a hire website. This works fine. I am trying to generate a report that lists all items and their category and sub category. I came up with this to generate a table with the main category:

SELECT 
wp_posts.ID as ID, 
wp_posts.post_title as post_title,
wp_terms.name as main_category,
wp_terms.term_id as main_category_id
FROM 
wp_posts, wp_term_relationships, wp_terms, wp_term_taxonomy
WHERE
wp_term_relationships.object_id = wp_posts.ID
AND wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id 
AND wp_term_taxonomy.term_id = wp_terms.term_id
AND wp_term_taxonomy.parent = '0'
AND wp_posts.post_type = 'goods' 
AND wp_posts.post_status = 'publish'
ORDER BY wp_terms.term_id ASC

I just loop through and its fine. But I want to show the sub category too - which I can do by putting another query in the foreach php loop.

BUT, I need to order by main category and then sub category.

So I figured I need it all in one query. And it must be possible. I have tried experimenting with JOINing tables in the query but can't get the syntax right. Heres the latest one I tried.

SELECT 
wp_posts.ID as ID, 
wp_posts.post_title as post_title,
s1.name as main_category,
s1.term_id as main_category_id,
s2.name as main_category,
s2.term_id as main_category_id
FROM 
wp_posts, wp_term_relationships, wp_terms s1, wp_term_taxonomy
LEFT JOIN wp_terms s2
ON s1.term_id = s2.id
WHERE
wp_term_relationships.object_id = wp_posts.ID
AND wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id 
AND wp_term_taxonomy.term_id = s1.term_id
AND wp_term_taxonomy.parent = '0'
AND wp_posts.post_type = 'goods' 
AND wp_posts.post_status = 'publish'
ORDER BY s1.term_id ASC
0

The problem is, there might be 1, 2, 3, 4 or N levels of depth in the subcategories. So, writing the query is possible, but inefficient because to be this flexible, left joins will be required which are not fast.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.