Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

I'm fighting this quite long already and start to think I'm missing something big. CPT posts could have or not metadata ( _vip_post [0|1], _thumbnail_id [null|numeric] ).

If i query for _thumbnail_id it's ok and works:

SELECT SQL_CALC_FOUND_ROWS wp_posts.* 
FROM wp_posts  
LEFT JOIN wp_postmeta m ON ( wp_posts.ID = m.post_id AND m.meta_key = '_thumbnail_id')
ORDER BY 
    m.meta_key DESC,
    wp_posts.post_date DESC

Returns posts with thumbnails first, _thumbnail_id = null comes last. good enough for now. Working.

But if I query for my new _vip_post meta_key It's totally ignored in ordering. Same pattern.

SELECT SQL_CALC_FOUND_ROWS wp_posts.* 
FROM wp_posts  
LEFT JOIN wp_postmeta m ON ( wp_posts.ID = m.post_id AND m.meta_key = '_vip_post')
ORDER BY 
    m.meta_key DESC,
    wp_posts.post_date DESC 

Just ordered by post_date. Not working.

share|improve this question

1 Answer 1

up vote 4 down vote accepted

Zlatev

meta_key is the name of the custom post type, and is a VARCHAR(255)
meta_value is the value of the key, and is a longtext

With your query you are not ordering by values, but by the name of the field. The query works for _thumbnail_id because you are using LEFT JOIN and the CPT could be null.

You could see if you run this query

SELECT SQL_CALC_FOUND_ROWS wp_posts.* ,m.*
FROM wp_posts  
LEFT JOIN wp_postmeta m ON ( wp_posts.ID = m.post_id AND m.meta_key = '_thumbnail_id')
ORDER BY 
    m.meta_key ASC,
    wp_posts.post_date DESC

See the null for the meta_key

In the second case, the CPT _vip_post IS always present, so the ordering on the fieldname is not working. You must order it by meta_value.

Try something like:

SELECT SQL_CALC_FOUND_ROWS wp_posts.* ,m.*
FROM wp_posts  
LEFT JOIN wp_postmeta m ON ( wp_posts.ID = m.post_id AND m.meta_key = '_vip_post')
ORDER BY 
    m.meta_value ASC,
    wp_posts.post_date DESC

You can change the ordering by using *m.meta_value DESC*.

share|improve this answer
1  
omg. so ashamed but it worked. –  Zlatev Mar 24 '12 at 10:42

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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