CODEWITHSUNDEEP
mysqlwordpress
SgtSlaughter
SgtSlaughter

Reputation: 11

How to mysql query a list of all posts and the categories they belong to (includeing the parent categories) in wordpress

I'm developing an app that I'm basically syncing parts the wordpress database to the app. I was hoping instead of create the entire table structure of wordpress I would create a CategoryRelationship table, which included just 2 columns (POST_ID), and (CATEGORIES_ID). Categories ID is a comma seperated value of all categories the post belonged to (including parent categories). Please note my wordpress database is part of a multisite hence the WP_2_ prefix.

Attempt 1) is the following, which almost works and gives me the result closest to how I want, but doesn't seem to get the 1st parent (Please note I only have categories with max hierarchy of 3 parents:

SELECT DISTINCT ID, 
(SELECT CONCAT_WS(',',group_concat(tt.term_id separator ','),tt.parent,tt2.term_id, tt3.term_id, tt4.term_id) 
FROM wp_2_terms 
JOIN wp_2_term_taxonomy tt on wp_2_terms.term_id = tt.term_id 
JOIN wp_2_term_relationships wpr on wpr.term_taxonomy_id = tt.term_taxonomy_id 
LEFT JOIN wp_2_term_taxonomy tt2 on tt.parent = tt2.term_id 
LEFT JOIN wp_2_term_taxonomy tt3 on tt2.parent = tt3.term_id 
LEFT JOIN wp_2_term_taxonomy tt4 on tt3.parent = tt4.term_id 
WHERE tt.taxonomy != 'category' and wp_2_posts.ID = wpr.object_id 
) AS 'Categories' 
FROM wp_2_posts 
WHERE post_type = 'post' 
AND post_status = 'publish'
AND post_author = 2

This gives me a result like:

ID  Categories
10  21,101,166,183,311,350,356,357,360,363,0
19  69,123,166,352,354,356,132,358,360,362,68,68

The above example mostly works, although I'm missing the 1st parents, I know this query can give me duplicates but I clean that up later (if I could do it all in 1 go that would be best obviously).
Also if I change the line: 

LEFT JOIN wp_2_term_taxonomy tt2 on tt.parent = tt2.term_id

TO

JOIN wp_2_term_taxonomy tt2 on tt.parent = tt2.term_id

Give me results like:

ID  Categories
10  101,93,93
19  69,123,68,68

Note I get Category 93 now for ID 10 (which I was missing in the first), but not the rest. I've tried all the different joins but to no avail...

Attempt 2) I modified this code from here, this gets me the categories and parent categories but I have to specify the ID. I'm not sure how to plug my code in above to loop through to get all the post IDs with the categories. Currently it just gets me all the parent categories & the category of term_id = 165.

SELECT group_concat(T2.term_id separator ',') AS 'Cats'
FROM (
    SELECT
        @r AS _id,
        (SELECT @r := parent FROM wp_2_term_taxonomy WHERE term_id = _id) AS parent,
        @l := @l + 1 AS lvl
    FROM
        (SELECT @r := 165, @l := 0) vars,
        wp_2_term_taxonomy tt
    WHERE @r <> 0) T1
JOIN wp_2_term_taxonomy T2
ON T1._id = T2.term_id
ORDER BY T1.lvl DESC;

This gives me a result like:

Cats
165,142

Attempt 3) This query is still quite basic, gets me all the categories and the 1st parent in a separate column, but I would need to iterate through the parent category some how... Any ideas? I'm hoping a mix between Attempt 2 and 3 will work but I've been working on this for way too long.

SELECT DISTINCT ID, TT.term_id, TT.parent
FROM wp_2_posts p
JOIN wp_2_term_relationships TR
ON TR.object_id = p.ID
JOIN wp_2_term_taxonomy TT
ON TR.term_taxonomy_id = TT.term_taxonomy_id
WHERE p.post_type = 'post'
AND p.post_status = 'publish'
AND p.post_author = 2

This gives me a table looking like:

ID  term_id parent
10  1   0
10  21  0
10  101 93
10  166 0
10  183 0
10  311 0
10  350 0
10  356 0
10  357 0
10  360 0
10  363 0
19  1   0
19  69  68
19  123 122
19  166 0
19  352 0
19  354 0
19  356 0
19  132 0
19  358 0
19  360 0
19  362 0

Thanks in advance for any help.

Upvotes: 0

Views: 2463

Answers (1)

SgtSlaughter
SgtSlaughter

Reputation: 11

So the following query worked for me, although I don't feel it is the best solution. I had to add compares that taxonomy is the same on each join. For some reason when it did the 3rd join on the wp_2_term_taxonomy table it was joining invalid values... Can't explain. Hopefully someone has a better solution.

SELECT DISTINCT ID, (
SELECT CONCAT_WS( ',', GROUP_CONCAT( t.term_id
SEPARATOR ',' ) , GROUP_CONCAT( DISTINCT tt2.term_id
SEPARATOR ',' ) , GROUP_CONCAT( DISTINCT tt3.term_id
SEPARATOR ',' ) , GROUP_CONCAT( DISTINCT tt4.term_id
SEPARATOR ',' ) ) 
FROM wp_2_terms t
JOIN wp_2_term_taxonomy tt ON t.term_id = tt.term_id
JOIN wp_2_term_relationships wpr ON wpr.term_taxonomy_id = tt.term_taxonomy_id
LEFT JOIN wp_2_term_taxonomy tt2 ON tt.parent = tt2.term_id
AND tt2.taxonomy = tt.taxonomy
LEFT JOIN wp_2_term_taxonomy tt3 ON tt2.parent = tt3.term_id
AND tt2.taxonomy = tt3.taxonomy
LEFT JOIN wp_2_term_taxonomy tt4 ON tt3.parent = tt4.term_id
AND tt3.taxonomy = tt4.taxonomy
WHERE tt.taxonomy != 'category'
AND wp_2_posts.ID = wpr.object_id
) AS 'Categories'
FROM wp_2_posts
WHERE post_type = 'post'
AND post_status = 'publish'
AND post_author =2

Upvotes: 1

Related Questions