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

Stoleg · Answer 1 · 2013-06-23T22:29:09.337

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.

edited Jun 23 '13 at 22:29

answered Jun 18 '13 at 09:07

Stoleg

8,972
1
21
28

Thanks for your answer. Do you know if there is any way to change the `INNER JOIN` to a `LEFT OUTER JOIN` using the `WP_Query` class? I would very much prefer _not_ to use my own SQL but rather the WP API. – powerbuoy Jun 20 '13 at 02:41
@powerbuoy Set `$join` property to `posts_join` filter value. See upted answer. – Stoleg Jun 20 '13 at 07:38
I tried this in PHPMyAdmin but unfortunately both queries (`INNER JOIN` and `LEFT OUTER JOIN`) return the exact same number of results. If I remove the sorting entirely I get all posts, even those that don't include the `item_price` key. – powerbuoy Jun 23 '13 at 22:17
1

I'm definitely no SQL expert, but couldn't the `WHERE` clause "`AND (wp_postmeta.meta_key = 'item_price')`" stop every post not having that meta_key set from showing up? – powerbuoy Jun 23 '13 at 22:21
I think you are right, change it to `AND (wp_postmeta.meta_key = 'item_price' OR wp_postmeta.meta_key is NULL)`, to include empty rows after tables are joined. You can also remove `1=1 AND` bit as it is always `true` and is just a waste. – Stoleg Jun 23 '13 at 22:28

score 2 · Answer 2 · edited May 23 '17 at 12:32

2

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?

edited May 23 '17 at 12:32

Community

1
1

answered Jun 20 '13 at 22:13

landons

9,502
3
33
46

I've never used `CASE` and the others before so not sure why, but unfortunately I'm getting an SQL error when I try to replace my own `ORDER BY ... ASC` with yours: `You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IS NOT NULL THEN wp_postmeta.meta_value END ASC, CASE wp_postmeta.meta_value WHE' at line 1` – powerbuoy Jun 23 '13 at 22:19
@powerbuoy I haven't tried this before either, so I'm shooting blind. Maybe try without the `IS`'s? – landons Jun 23 '13 at 22:22

score 1 · Answer 3 · answered Jan 03 '14 at 08:20

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

score -1 · Answer 4 · answered Jun 20 '13 at 21:59

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' => '!='
                        )
    ),

score -1 · Answer 5 · answered Aug 22 '13 at 18:12

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');

Setting `meta_key` will ignore your `meta_query` statement – Amin Jul 07 '20 at 08:42 — Amin, Jul 07 '20 at 08:42

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

5 Answers5