Site icon Hip-Hop Website Design and Development

What’s wrong with this WP query?

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.