1

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 quaery 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.

8
  • PS: There's a typo in your question/code - the 'tdlrm_commands' = should be 'tdlrm_commands' =>. Also, it seems you forgot to close the meta_query array which then causes the orderby and tdlrm_commands be part of the meta_query..
    – Sally CJ
    Jul 30 at 2:49
  • Sally, the typos are just here, not on my site. The code actually works, it produces different queries as expected, when I set my 'tdlrm_orderby' to true or false. The orderby part you wrote appears in the query. What I'm trying to wrap my head around is why it works in the query I wrote manually, but doesn't work in the query WordPress produces.
    – Artem
    Jul 30 at 3:26
  • Thank you for pointing out my typos, though.
    – Artem
    Jul 30 at 3:33
  • Have you actually tried running the SQL command in the $query->request via phpMyAdmin (but replace the SQL_CALC_FOUND_ROWS wp_posts.ID with wp_posts.ID, wp_posts.post_title) - how's the outcome; did the sorting work correctly?
    – Sally CJ
    Jul 30 at 4:52
  • 1
    I have not sorted it out yet, but I found out that WP uses LEFT JOIN to join wp_postmeta several times, so the 1C_quantity_total meta is actually in the THIRD joined wp_postmeta table, its alias is mt2. Something like CASE mt2.meta_key WHEN '1C_quantity_total' THEN mt2.meta_value+0 END DESC would work, I guess... I'm currently trying to find a way to change the orderby filter without hardcoding the alias (what if it changes in the future).
    – Artem
    Jul 31 at 11:55
1

Turns out, WordPress joins the postmeta table several times, depending on the meta query arguments.

Yes that's correct, and it's also the reason why I said (in my other answer) that in an orderby array, you should use the key of an array in the meta_query which contains a direct item named/keyed key (where the value is a meta key) — and if you don't, then WordPress will not know which table join/alias should be used.

This means the 1C_quantity_total meta key has to be referenced as mt2.meta_key, not wp_postmeta.meta_key.

Yes, you're absolutely right!

( And sorry, my bad, I revised my other answer several times and yet, I actually still forgot to correct the table alias 😅 )

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?

Yes, there is: use WP_Meta_Query::get_clauses() to get the correct alias used for each meta query clause. And with a WP_Query instance, you can access the meta query class instance using the $meta_query property, i.e. WP_Query::$meta_query.

But remember to give a unique array key to your meta query clauses like the has_tdlrm_mp and has_1C_quantity_total:

$args = array(
    ...
    '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',
            ),
        )
   ),
   ...
);

So with that meta query, you can get the correct table alias like so:

$query = new WP_Query( $args );

$meta_clauses = $query->meta_query->get_clauses();

var_dump(
    $meta_clauses['has_tdlrm_mp']['alias'],
    $meta_clauses['has_1C_quantity_total']['alias'],
);
// Sample output: string(11) "wp_postmeta" string(3) "mt2"

And inside the posts_orderby filter/callback, you can use the same $query->meta_query->get_clauses() above, like so:

$meta_clauses = $query->meta_query->get_clauses();

$has_tdlrm_mp          = $meta_clauses['has_tdlrm_mp']['alias'];
$has_1C_quantity_total = $meta_clauses['has_1C_quantity_total']['alias'];

return "
CASE {$has_tdlrm_mp}.meta_key
    WHEN 'tdlrm_mp' THEN 1
    ELSE 2
END ASC,
CASE {$has_tdlrm_mp}.meta_key
    WHEN 'tdlrm_mp' THEN {$has_tdlrm_mp}.meta_value+0
END ASC,
CASE {$has_1C_quantity_total}.meta_key
    WHEN '1C_quantity_total' THEN {$has_1C_quantity_total}.meta_value+0
END DESC
";
3
  • Yes and sorry about my initial answer - I was just being confused (and seemed to have continued being so... confused).. Anyway, I hope the revised answer helps. :)
    – Sally CJ
    Aug 1 at 4:59
  • Yea, the question that keeps people confused is either a bad one or a great one )))
    – Artem
    Aug 1 at 5:00
  • 1
    Phew... Sally, we did it! You're just great, thank you for your patience and your knowledge!
    – Artem
    Aug 1 at 7:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.