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

Question

I am trying to allow my users to sort the search results by the various custom fields that I have.

I use the pre_get_posts filter and everything works fine except for one thing.

The problem I am facing is that when a custom field is used for sorting, only messages that have a custom set of fields are displayed in the search list .

Obviously, this is unacceptable because the number of search results changes when the user changes the sort order.

Instead, I want messages that have a custom field to be displayed first, in order, and then the rest of the messages are displayed sorted by date.

Here is the relevant code that 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: this is what the actual MySQL query looks like. How can I change this so that it sorts by wp_postmeta.meta_value if it exists - if not, sort by 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 

It is advisable that I decide to use it using the WP_Query methods, but if I need it so that I can run my own SQL.

Edit2: It seems to me that I need to use something like IF NOT NULL - how would you do this with WP_Query ? Is it possible?

Edit (again): Here is 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

The problem is not that the data is not sorted by date column, the problem is that there is no data with empty meta_value .

The reason you cannot see the record without metadata is that it can be filtered by INNER JOIN for cases where wp_postmeta.post_id is null . Change join to 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 the connection type in WP_Query , use the $join property to set the posts_join filter, as described in the plugin API / Filter Link / join messages

See also Reference to the WP_Query Class .

Answer 2

This will not be nice (or optimized), but I think you can use 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 have not tried this myself, so a syntax error may occur, but it should work theoretically.

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

Answer 3

I think I have a solution.

use two meta_keys, one of which has all messages (for example, "_thumbnail_id") and the meta key that you want to use as a filter. so your arguments are:

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

Answer 4

I know this topic has been around for 6 months, but it seems that there has never been an answer. I just ran into the same problem as the original author of the problem. So, this is my last SQL query that 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 request was trying to get a slightly different result), your request should look like it would work (if I did not include any new errors, but my request 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 

Answer 5

For Edit2: Yes, this is possible using meta_query. Try

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