Wordpress SQL - post category and tags

Question

I used this query , to receive posts with his category and tags.

SELECT
    p.id,
    p.post_name,
    c.name,
    GROUP_CONCAT(t.`name`)
FROM wp_posts p
JOIN wp_term_relationships cr
    on (p.`id`=cr.`object_id`)
JOIN wp_term_taxonomy ct
    on (ct.`term_taxonomy_id`=cr.`term_taxonomy_id`
    and ct.`taxonomy`='category')
JOIN wp_terms c on
    (ct.`term_id`=c.`term_id`)
JOIN wp_term_relationships tr
    on (p.`id`=tr.`object_id`)
JOIN wp_term_taxonomy tt
    on (tt.`term_taxonomy_id`=tr.`term_taxonomy_id`
    and tt.`taxonomy`='post_tag')
JOIN wp_terms t
    on (tt.`term_id`=t.`term_id`)
GROUP BY p.id

It is from Wordpress SQL: get post category and tags

But i have a problem, the query ignores posts without tags. I tried many other queries but no SUCSSES. Can someone help me?

Answer 1

You are very close to a good solution to your problem.

Another answerer mentioned this: Use LEFT JOIN. Why? Ordinary inner JOIN suppresses rows from the first table that don't match rows from the second table.

Your code also shows signs of misusing MySQL's notorious extension to GROUP BY. If you say

   SELECT id, name, value
     FROM tbl
    GROUP BY id, name

you're actually saying

  SELECT id, name, ANY_VALUE(value)
    FROM tbl
   GROUP BY id, name

In other words, the server picks whatever value it wants from within the group. You should use predictable aggregate result like MAX(value) or GROUP_CONCAT(value). Version 8 of MySQL and other makes and models of SQL table server reject the syntax you use.

Pro tip for the sake of your sanity don't use the backticks around table or column names unless they are reserved words in SQL. And, Pro tip, don't use reserved words for table or column names.

Use this query instead.

SELECT
    p.id,
    p.post_name,
    GROUP_CONCAT(c.name ORDER BY c.name) categories,
    GROUP_CONCAT(t.name ORDER BY t.name) tags
FROM wp_posts p
LEFT JOIN wp_term_relationships cr
    on (p.id=cr.object_id)
LEFT JOIN wp_term_taxonomy ct
    on (ct.term_taxonomy_id=cr.term_taxonomy_id
    and ct.taxonomy='category')
LEFT JOIN wp_terms c on
    (ct.term_id=c.term_id)
LEFT JOIN wp_term_relationships tr
    on (p.id=tr.object_id)
LEFT JOIN wp_term_taxonomy tt
    on (tt.term_taxonomy_id=tr.term_taxonomy_id
    and tt.taxonomy='post_tag')
LEFT JOIN wp_terms t
    on (tt.term_id=t.term_id)
GROUP BY p.id, p.post_name