WordPress.org

Ready to get started?Download WordPress

Forums

WP_Query, sorting by meta_vale & multiple meta_query not giving correct results (2 posts)

  1. benners_
    Member
    Posted 6 months ago #

    WP_Query with 1 meta_query and orderby meta_value returns correct results.

    $atts = array(
        'post_status'     		=> 'publish',
        'cat'     			=> get_queried_object()->term_id,
        'meta_key'        		=> 'my_rating',
        'orderby'         		=> 'meta_value',
        'order'           		=> 'asc',
        'meta_query'            => array(
            'relation'=>'AND',
            array(
                'key' => 'nearest_tube_station',
                'value' => $myfilter_location,
                'compare' => 'LIKE'
            ),
        ),
    );

    SQL generated:
    SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) INNER JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id) WHERE 1=1 AND ( wp_term_relationships.term_taxonomy_id IN (10) ) AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') AND (wp_postmeta.meta_key = 'my_rating' AND (mt1.meta_key = 'nearest_tube_station' AND CAST(mt1.meta_value AS CHAR) LIKE '%holborn%') ) GROUP BY wp_posts.ID ORDER BY wp_postmeta.meta_value asc LIMIT 0, 10

    Add an extra meta value to query using OR relationship it incorrectly returns all rows.

    $atts = array(
        'post_status'     		=> 'publish',
        'cat'     			=> get_queried_object()->term_id,
        'meta_key'        		=> 'my_rating',
        'orderby'         		=> 'meta_value',
        'order'           		=> 'asc',
        'meta_query'            => array(
            'relation'=>'OR',
            array(
                'key' => 'nearest_tube_station',
                'value' => $myfilter_location,
                'compare' => 'LIKE'
            ),
            array(
                'key' => 'short_location',
                'value' => $myfilter_location,
                'compare' => 'LIKE'
            ),
        ),
    );

    SQL generated:
    SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) INNER JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id INNER JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id) INNER JOIN wp_postmeta AS mt2 ON (wp_posts.ID = mt2.post_id) WHERE 1=1 AND ( wp_term_relationships.term_taxonomy_id IN (10) ) AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') AND (wp_postmeta.meta_key = 'my_rating' OR (mt1.meta_key = 'nearest_tube_station' AND CAST(mt1.meta_value AS CHAR) LIKE '%holborn%') OR (mt2.meta_key = 'short_location' AND CAST(mt2.meta_value AS CHAR) LIKE '%holborn%') ) GROUP BY wp_posts.ID ORDER BY wp_postmeta.meta_value asc LIMIT 0, 10

    The problem with the SQL generated seems to be the OR should be an AND

    (wp_postmeta.meta_key = 'social_heaven_rating' OR

    Is it possible to do this query using WP_Query and avoid a custom query?

  2. vtxyzzy
    Member
    Posted 6 months ago #

Reply

You must log in to post.

About this Topic