With kind help of Sally CJ who answered my question, I was able to create a WP Query, but it still gives me unexpected results. When I write the query in raw MySQL, it works fine, but with the query WordPress creates, it doesn’t.
Here are my WP query arguments:
$args = array(
'post_type' => 'tdlrm_store_item',
'post_status' => 'publish',
'tax_query' => array(
array(
'taxonomy' => 'store-category',
'field' => 'term_id',
'terms' => 514,
'include_children' => false
)
),
'meta_query' => array(
array(
'relation' => 'OR',
'has_tdlrm_mp' => array(
'key' => 'tdlrm_mp',
'type' => 'NUMERIC',
),
'no_tdlrm_mp' => array(
'key' => 'tdlrm_mp',
'compare' => 'NOT EXISTS',
),
),
array(
'relation' => 'OR',
'has_1C_quantity_total' => array(
'key' => '1C_quantity_total',
'type' => 'NUMERIC',
),
'no_1C_quantity_total' => array(
'key' => '1C_quantity_total',
'compare' => 'NOT EXISTS',
),
)
),
'orderby' => 'none',
'tdlrm_commands' => array('tdlrm_orderby' => true)
);
Here’s my posts_orderby filter:
add_filter( 'posts_orderby', function ( $orderby, $query ){
if (!isset($query->query_vars['tdlrm_commands']['tdlrm_orderby'])
|| $query->query_vars['tdlrm_commands']['tdlrm_orderby'] !== true) return $orderby;
global $wpdb;
return "
CASE {$wpdb->postmeta}.meta_key
WHEN 'tdlrm_mp' THEN 1
WHEN '1C_quantity_total' THEN 2
ELSE 3
END ASC,
CASE {$wpdb->postmeta}.meta_key
WHEN 'tdlrm_mp' THEN {$wpdb->postmeta}.meta_value+0
END ASC,
CASE {$wpdb->postmeta}.meta_key
WHEN '1C_quantity_total' THEN {$wpdb->postmeta}.meta_value+0
END DESC,
{$wpdb->posts}.post_date DESC
";
}, 10, 2 );
Here’s the MYSQL query I wrote trying to figure out what’s going on. It orders posts in such a way that the ones that have tdlrm_mp meta go first, ordered by meta value, lowest to highest, then the others, ordered by the 1C_quantity_total meta value, highest to lowest, then the ones that have no 1C_quantity_total meta, ordered by post date.
SELECT
* FROM wp_postmeta
LEFT JOIN wp_posts on wp_postmeta.post_id = wp_posts.ID
LEFT JOIN wp_term_relationships on wp_posts.ID = wp_term_relationships.object_id
WHERE wp_posts.post_type = 'tdlrm_store_item'
AND wp_posts.post_status = 'publish'
AND wp_term_relationships.term_taxonomy_id = 514
ORDER BY
CASE wp_postmeta.meta_key
WHEN 'tdlrm_mp' THEN 1
WHEN '1C_quantity_total' THEN 2
ELSE 3
END ASC,
CASE wp_postmeta.meta_key
WHEN 'tdlrm_mp' THEN wp_postmeta.meta_value+0
END ASC,
CASE wp_postmeta.meta_key
WHEN '1C_quantity_total' THEN wp_postmeta.meta_value+0
END DESC,
wp_posts.post_date DESC
LIMIT 0,12
It works as expected. However, what I get with $query->request is this, and it doesn’t work:
SELECT SQL_CALC_FOUND_ROWS wp_posts.ID
FROM wp_posts
LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
LEFT JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id )
LEFT JOIN wp_postmeta AS mt1 ON ( wp_posts.ID = mt1.post_id AND mt1.meta_key = 'tdlrm_mp' )
LEFT JOIN wp_postmeta AS mt2 ON ( wp_posts.ID = mt2.post_id )
LEFT JOIN wp_postmeta AS mt3 ON ( wp_posts.ID = mt3.post_id AND mt3.meta_key = '1C_quantity_total' )
WHERE 1=1 AND ( wp_term_relationships.term_taxonomy_id IN (514) )
AND ( ( wp_postmeta.meta_key = 'tdlrm_mp' OR mt1.post_id IS NULL ) AND ( mt2.meta_key = '1C_quantity_total' OR mt3.post_id IS NULL ) )
AND wp_posts.post_type = 'tdlrm_store_item'
AND ((wp_posts.post_status = 'publish'))
GROUP BY wp_posts.ID
ORDER BY
CASE wp_postmeta.meta_key
WHEN 'tdlrm_mp' THEN 1
WHEN '1C_quantity_total' THEN 2
ELSE 3
END ASC,
CASE wp_postmeta.meta_key
WHEN 'tdlrm_mp' THEN wp_postmeta.meta_value+0
END ASC,
CASE wp_postmeta.meta_key
WHEN '1C_quantity_total' THEN wp_postmeta.meta_value+0
END DESC,
wp_posts.post_date DESC LIMIT 0, 12
The first posts are the ones that have the tdlrm_mp meta, and they get ordered just fine, but the rest get ordered by date, and that’s it. What causes the difference between the MYSQL query I wrote and the WP MYSQL query, and how do I make it all work?
Update.
Turns out, WordPress joins the postmeta table several times, depending on the meta query arguments. This is how:
//php
$args['meta_query'] = array(
'relation' => 'AND',
array(
'relation' => 'OR',
array(
'key' => 'tdlrm_mp',
'type' => 'NUMERIC',
),
array(
'key' => 'tdlrm_mp',
'compare' => 'NOT EXISTS',
),
),
array(
'key' => '1C_quantity_total',
'type' => 'NUMERIC',
)
);
//MySQL query by WordPress
"...LEFT JOIN wp_postmeta ON (
wp_posts.ID = wp_postmeta.post_id
)
LEFT JOIN wp_postmeta AS mt1 ON (
wp_posts.ID = mt1.post_id
AND mt1.meta_key = 'tdlrm_mp'
)
LEFT JOIN wp_postmeta AS mt2 ON (wp_posts.ID = mt2.post_id)
WHERE..."
This means the 1C_quantity_total meta key has to be referenced as mt2.meta_key, not wp_postmeta.meta_key. I rewrote the orderby filter like this, and it works:
add_filter( 'posts_orderby', function ( $orderby, $query ){
if (! isset( $query->query['tdlrm_commands'] ) ||
! $query->query['tdlrm_commands']['default_filter']
) return $orderby;
global $wpdb;
return "
CASE {$wpdb->postmeta}.meta_key
WHEN 'tdlrm_mp' THEN 1
ELSE 2
END ASC,
CASE {$wpdb->postmeta}.meta_key
WHEN 'tdlrm_mp' THEN {$wpdb->postmeta}.meta_value+0
END ASC,
CASE mt2.meta_key
WHEN '1C_quantity_total' THEN mt2.meta_value+0
END DESC
";
}, 10, 2 );
Now, I don’t feel like hard-coding the table’s alias, mt2. What if the alias changes in future versions of WordPress (e.g. mtB)? Also, I’m not sure 1C_quantity_total will be in exactly the third joined postmeta table in any circumstances. Is there a way to do it better? Like, "find the joined table 1C_quantity_total is actually in, then order by the value in that table".
Update 2
Thank you Sally CJ for helping me out, you’re great!
While trying to solve this problem, I came across a number of questions that may be useful to whoever comes here:
question, question, question, JOIN speed, question, question, question.