mySQL query. ORDER BY meta_key

3 Views

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:

Read More
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.

Post Views: 3

Related posts

Leave a Reply

1 comment

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