Advanced SELECT query with condtional statements

I think you are jumping a gun with custom SQL, complexity of this seems still in realm of WP_Query. Even if not it’s a good idea to start with it to have more solid base for custom SQL.

If I try something like this:

$meta_query = array(
    array( 'key' => 'body_type', 'value' => 'bt' ),
    array( 'key' => 'Make', 'value' => 'make' ),
    array( 'key' => 'Model', 'value' => 'model' ),
    array( 'key' => 'Year', 'value' => array( 2010, 9999 ), 'compare' => 'BETWEEN' ),
);

$query = new WP_Query( array( 'meta_query' => $meta_query ) );

$query->get_posts();
var_dump( $wpdb->queries );

This is the SQL generated:

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID
FROM wp_posts
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)
INNER JOIN wp_postmeta AS mt3 ON (wp_posts.ID = mt3.post_id)
WHERE 1=1
  AND wp_posts.post_type="post"
  AND (wp_posts.post_status="publish"
       OR wp_posts.post_status="private")
  AND ((wp_postmeta.meta_key = 'body_type'
        AND CAST(wp_postmeta.meta_value AS CHAR) = 'bt')
       AND (mt1.meta_key = 'Make'
            AND CAST(mt1.meta_value AS CHAR) = 'make')
       AND (mt2.meta_key = 'Model'
            AND CAST(mt2.meta_value AS CHAR) = 'model')
       AND (mt3.meta_key = 'Year'
            AND CAST(mt3.meta_value AS CHAR) BETWEEN '2010' AND '9999'))
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_date DESC LIMIT 0, 10

Note that GROUP BY at the end, it’s probably what takes care of duplicates you are getting.

See Custom Field Parameters documentation in Codex for more info on meta queries.

Related Posts: