mysql – What’s wrong with this WP query?

Question

With kind help of Sally CJ who answered my question, I was able to create a Wp Query, but it still gives me not the result that I expect. When I write it in PHPMyAdmin, 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;

    $orderby = "
    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
    ";

    return $orderby;
}, 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 its value, lowest to highest, then go 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 I expect. 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?

0
Artem 2 months 2021-07-29T12:33:31-05:00 0 Answers 0 views 0

Leave an answer

Browse
Browse