Reputation: 5146
I have a custom table that is used to track the access to a website. The table is as following:
date,post_slug,user
I want to create a query (mysql) that from this table add a 4th column containing all the categories of the post.
I don't want to create a new row for each category just a row with all the categories added to each row of this table.
The informations should be stored in wp_term_relationship and wp_terms but I don't know how to create just one row and not n rows for each category
Upvotes: 1
Views: 1040
Reputation: 2855
Main thing you should do is group by wp_posts.id
. This command merges all equal id
to one row. Now, you should use group_concat( FIELDNAME )
to getting all desire fields in one column.
You need category names or category id?
If you wish have category name:
SELECT tbl.*,GROUP_CONCAT(term.name) FROM
YOURTABLE as tbl
left join wp_posts as p
on p.post_name = tbl.post_slug
left join wp_term_relationships as rels
on rels.object_id = p.ID
left JOIN wp_term_taxonomy as tx
on tx.term_taxonomy_id = rels.term_taxonomy_id
left join wp_terms as term
on term.term_id = tx.term_id
where tx.taxonomy = "category"
group by p.ID
If you need category id:
SELECT tbl.*,GROUP_CONCAT(tx.term_taxonomy_id) FROM
YOURTABLE as tbl
left join wp_posts as p
on p.post_name = tbl.post_slug
left join wp_term_relationships as rels
on rels.object_id = p.ID
left JOIN wp_term_taxonomy as tx
on tx.term_taxonomy_id = rels.term_taxonomy_id
-- left join wp_terms as term
-- on term.term_id = tx.term_id
where tx.taxonomy = "category"
group by p.ID
Note:
YOURTABLE
is your 3-column table.
If you want get all tags, just edit where tx.taxonomy = "category"
to where tx.taxonomy = "post_tag"
If you are using custom post types and you want wordpress custom post types tags or categories, add and p.post_type ="YOURPOSTTYPE"
after where ..
line.
Upvotes: 3