mySQL query. ORDER BY meta_key

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*.

Related Posts: