CODEWITHSUNDEEP
phpsqlwordpress
powerbuoy
powerbuoy

Reputation: 12838

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

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

Upvotes: 6

Views: 8763

Answers (5)

Florian Ubr
Florian Ubr

Reputation: 141

I know, this thread is around 6 months old already, but it seems there never was a sufficient answer. I was just running into the exactly same problem as the original author of the problem. So this is my final SQL query I found to work and return the desired result:

SELECT SQL_CALC_FOUND_ROWS * 
FROM wp_posts 
INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) 
LEFT JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id AND mt1.meta_key = 'showPostInBanner' AND mt1.meta_value='1') 
LEFT JOIN wp_postmeta AS mt2 ON (wp_posts.ID = mt2.post_id) 
WHERE 1=1 AND ( wp_term_relationships.term_taxonomy_id IN (34,47,51,50,68,78,82,90,97,155,227,253,285,294,314,373,425,436,452,456,521,627,667,680,694,710,730,741,751) ) 
AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private')
GROUP BY wp_posts.ID 
ORDER BY mt1.meta_value+0 DESC, wp_posts.post_date DESC LIMIT 0, 4

Based on this (my query was trying to get a slightly different result) your query should look like this to make it work (if I didn't include any new errors - but my query definitely works now):

SELECT SQL_CALC_FOUND_ROWS * 
FROM wp_posts 
LEFT JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id AND mt1.meta_key = 'showPostInBanner' AND mt1.meta_value='1') 
LEFT JOIN wp_postmeta AS mt2 ON (wp_posts.ID = mt2.post_id) 
WHERE 1=1 AND ((
        (wp_posts.post_title LIKE '%lorem%') OR 
        (wp_posts.post_content LIKE '%lorem%')
    )) AND 
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')
GROUP BY wp_posts.ID 
ORDER BY mt1.meta_value+0 DESC, wp_posts.post_date DESC LIMIT 0, 6

Upvotes: 1

Rafael
Rafael

Reputation: 29

I think i have a solution..

use two meta_key, one that all posts have (like "_thumbnail_id"), and the meta_key you wish use as filter.. so your args: 

$qry->set( 
'meta_query',
 array(
 'relation' => 'OR',
 array( 'key' => 'item_price', 'value' => '', 'compare' => 'EXISTS' ),
 array( 'key' => '_thumbnail_id', 'value' => '', 'compare' => 'EXISTS' )
 ));

$qry->set('orderby', 'meta_value date'); 
$qry->set('order', 'ASC DESC'); 
$qry->set('meta_key', 'item_price');

Upvotes: -1

Stoleg
Stoleg

Reputation: 9290

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.

Upvotes: 2

landons
landons

Reputation: 9547

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?

Upvotes: 2

Arslaan Ejaz
Arslaan Ejaz

Reputation: 1001

For Edit2: Yes it is possible using meta_query. Try 

'meta_query' => array(
    array(
                        'key' => 'meta_key',
                        'value' => 'some value', //try to put null here 
                        'compare' => '!='
                        )
    ),

Upvotes: -1

Related Questions