WordPress - order by meta value if it exists, else date

Question

I'm trying to allow my users to sort search results by different custom fields I have.

I'm using the pre_get_posts filter and everything works fine except for one thing.

The problem I'm having is that when a custom field is used to sort by, only the posts that have that custom field set will show up in the search.

Obviously this is not acceptable as the number of search result changes when the user changes how to sort them.

What I want instead is that the posts that have the custom field show up first, in order, and then the rest of the posts show up sorted on date.

Here's the relevant code I have:

<?php
add_filter('pre_get_posts', 'h5b_search_pre_get_posts');

function h5b_search_pre_get_posts ($qry) {
    $validOrders    = array('price', 'date', 'popularity');
    $orderBy        = (isset($_GET['myorder']) and in_array($_GET['myorder'], $validOrders)) ? $_GET['myorder'] : 'price';

    if ($qry->is_main_query() and $qry->query_vars['s'] != '') {
        # This only includes the posts that have "item_price" set
        if ($orderBy == 'price') {
            $qry->set('orderby', 'meta_value_num date');
            $qry->set('order', 'ASC DESC');
            $qry->set('meta_key', 'item_price');
        }
        # This works fine and includes all posts (obviously)
        elseif ($orderBy == 'date') {
            $qry->set('orderby', 'date');
            $qry->set('order', 'DESC');
        }
    }
}

Edit: Here's what the actual MySQL Query looks like. How can I change this so that it sorts on wp_postmeta.meta_value if it exists - if not, sort on date?

SELECT 
    SQL_CALC_FOUND_ROWS wp_posts.ID 
FROM 
    wp_posts 
INNER JOIN 
    wp_postmeta 
ON 
    (wp_posts.ID = wp_postmeta.post_id) 
WHERE 
    1=1 AND 
    ((
        (wp_posts.post_title LIKE '%lorem%') OR 
        (wp_posts.post_content LIKE '%lorem%')
    )) AND 
    wp_posts.post_type IN ('post', 'page', 'attachment', 'items', 'locations') AND 
    (wp_posts.post_status = 'publish' OR wp_posts.post_author = 1 AND wp_posts.post_status = 'private') AND 
    (wp_postmeta.meta_key = 'item_price' ) 
GROUP BY 
    wp_posts.ID 
ORDER BY 
    wp_postmeta.meta_value+0,wp_posts.post_date DESC LIMIT 0, 6

Preferably I'd solve it using the WP_Query methods but if I have to I might consider running my own SQL.

Edit2: I have a feeling I need to use something like IF NOT NULL - how would you do that with WP_Query? Is it even possible?

Edit (again): Here's the same question (I think :P): https://wordpress.stackexchange.com/questions/28409/way-to-include-posts-both-with-without-certain-meta-key-in-args-for-wp-query

Answer 1

It's not going to be pretty (or optimized), but I think you can use an IF or CASE here:

ORDER BY 
CASE wp_postmeta.meta_value WHEN IS NOT NULL THEN wp_postmeta.meta_value END ASC,
CASE wp_postmeta.meta_value WHEN IS NULL THEN wp_posts.post_date END DESC

Note: I haven't tried this out myself, so there might be a syntax error, but it should work in theory

Further reading: Can you add an if statement in ORDER BY?

Answer 2

Problem here is not that data is not sorted by date column, problem is that there is no data with empty meta_value returned.

The reason why you do not see entries with no meta specified, is that it may be filtered out by INNER JOIN for those cases, where wp_postmeta.post_id is null. Change join to be LEFT OUTER JOIN.

SELECT 
    SQL_CALC_FOUND_ROWS wp_posts.ID 
FROM 
    wp_posts 


LEFT OUTER JOIN

    wp_postmeta 
ON 
    (wp_posts.ID = wp_postmeta.post_id) 
WHERE 
    1=1 AND 
    ((
        (wp_posts.post_title LIKE '%lorem%') OR 
        (wp_posts.post_content LIKE '%lorem%')
    )) AND 
    wp_posts.post_type IN ('post', 'page', 'attachment', 'items', 'locations') AND 
    (wp_posts.post_status = 'publish' OR wp_posts.post_author = 1 AND wp_posts.post_status = 'private') AND 
    (wp_postmeta.meta_key = 'item_price'  OR wp_postmeta.meta_key is NULL ) 
GROUP BY 
    wp_posts.ID 
ORDER BY 
    wp_postmeta.meta_value+0,wp_posts.post_date DESC LIMIT 0, 6

UPDATE

To change join type in WP_Query use $join property to set posts_join filter as described in Plugin API/Filter Reference/posts join

See also WP_Query Class reference.