Mysql query, WP custom fields

Question

I need to select some things from few WP tables, one of which is custom field value with a certain key. Problem is, some of posts don't have custom field with this key but aside of that they match query. So question is, how do I select even posts that doesn't have custom field with this key, but other than that match query? Value of field for the posts that do not have custom field should be set 0. I figured it's something like COALESCE, but I'm either using it wrong or it can't be used in this case. My query so far is:

select lists.id,lists.`type`,lists.`status`,lists.watched_eps, lists.score,wp_posts.post_title,wp_posts.post_name,wp_terms.slug,wp_postmeta.meta_value from anime_lists
INNER JOIN wp_posts ON (wp_posts.ID = lists.post_id ) 
INNER JOIN wp_postmeta ON (wp_postmeta.post_id= lists.post_id )
INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) 
INNER JOIN wp_term_taxonomy ON(wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)
INNER JOIN wp_terms ON(wp_terms.term_id = wp_term_taxonomy.term_id)
where lists.id=1
and wp_term_taxonomy.taxonomy = 'category'
and wp_postmeta.meta_key='eps'
group by lists.`post_id`
order by lists.`status`, wp_posts.post_title

As it is, query outputs everything that I need but without these posts that do not have custom field 'eps'. If I remove and wp_postmeta.meta_key='eps' it outputs these posts, but that way I get wrong value in wp_postmeta.meta_value field.

Joachim Isaksson · Accepted Answer · 2013-08-15 20:28:32Z

3

As far as I understand what you want, you can just change your wp_postmeta join to a LEFT JOIN and move the WHERE condition on that table to the join, leaving;

SELECT lists.id, lists.`type`, lists.`status`, lists.watched_eps, 
       lists.score,wp_posts.post_title,wp_posts.post_name,wp_terms.slug,
       wp_postmeta.meta_value 
FROM anime_lists
INNER JOIN wp_posts 
  ON wp_posts.ID = anime_lists.post_id
LEFT JOIN wp_postmeta 
  ON wp_postmeta.post_id = anime_lists.post_id 
 AND wp_postmeta.meta_key='eps'
INNER JOIN wp_term_relationships 
  ON (wp_posts.ID = wp_term_relationships.object_id) 
INNER JOIN wp_term_taxonomy 
  ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)
INNER JOIN wp_terms 
  ON(wp_terms.term_id = wp_term_taxonomy.term_id)
WHERE lists.id=1
  AND wp_term_taxonomy.taxonomy = 'category'
GROUP BY lists.`post_id`
ORDER BY lists.`status`, wp_posts.post_title

answered Aug 15, 2013 at 20:28

Joachim Isaksson

182k28 gold badges297 silver badges307 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Igor Yavych Over a year ago

Seems that have done the trick. Changed wp_postmeta.meta_value to COALESCE(wp_postmeta.meta_value, 0) and it seems to do what I need. Thanks a lot.

Collectives™ on Stack Overflow

Mysql query, WP custom fields

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related