1

I am having trouble understanding the proper way to write an advanced select query. I have the following the code here.

if (!empty($_GET['3321body_type'])) {
    $bt = $_GET['3321body_type'];
    $bt_sql = "AND m1.meta_key = 'body_type' AND m1.meta_value = '$bt'";
}
else $bt_sql = '';
if (!empty($_GET['3321Make'])) {
   $make = $_GET['3321Make'];
    $make_sql = "AND m3.meta_key = 'Make' AND m3.meta_value = '$make'";
}
 else $make_sql = '';   
    if (!empty($_GET['3321Model'])) {
        $model = $_GET['3321Model'];
    $model_sql = "AND m4.meta_key = 'Model' AND m4.meta_value = '$model'";
    }
   else $model_sql = '';
  if (!empty($_GET['3321Year'])) {
  $year = $_GET['3321Year'];
  $year_sql = " AND m2.meta_key = 'Year' AND m2.meta_key = 'Year' AND   
  m2.meta_value       BETWEEN '$year' AND '9999'";

  }
  else $year_sql = ''; 
   global $wpdb;
    $wpdb->show_errors = true;

$querystr = "
SELECT * FROM wp_posts
LEFT JOIN wp_postmeta m0 on (wp_posts.ID = m0.post_id) 
LEFT JOIN wp_postmeta m1 on (wp_posts.ID = m1.post_id) 
LEFT JOIN wp_postmeta m2 on (wp_posts.ID = m2.post_id) 
LEFT JOIN wp_postmeta m3 on (wp_posts.ID = m3.post_id) 
LEFT JOIN wp_postmeta m4 on (wp_posts.ID = m4.post_id) 
WHERE wp_posts.ID = m0.post_ID
" . $bt_sql . "
" . $make_sql . "
" . $model_sql . "
" . $year_sql . "
";

$search = $wpdb->get_results($querystr, OBJECT);
foreach($search as $post) {
setup_postdata($post);

If I write it out like this

SELECT * FROM wp_posts
LEFT JOIN wp_postmeta m0 on (wp_posts.ID = m0.post_id) 
LEFT JOIN wp_postmeta m1 on (wp_posts.ID = m1.post_id) 
LEFT JOIN wp_postmeta m2 on (wp_posts.ID = m2.post_id) 
LEFT JOIN wp_postmeta m3 on (wp_posts.ID = m3.post_id) 
LEFT JOIN wp_postmeta m4 on (wp_posts.ID = m4.post_id) 
WHERE wp_posts.ID = m0.post_ID
AND m1.meta_key = 'body_type' AND m1.meta_value = 'Sedan'
AND m3.meta_key = 'Make' AND m3.meta_value = 'Acura'
AND m4.meta_key = 'Model' AND m4.meta_value = 'CL'
AND m2.meta_key = 'Year' AND m2.meta_key = 'Year' AND m2.meta_value BETWEEN '$year' AND '9999'

It shows up just fine. But When I add in the conditional statements in my first block of code. it appears the JOIN is not working properly because it returns duplicate rows.

Can anyone show me the proper way to write this statement to make the search query dynamic? Also an example of how to work wpdb->prepare into the works.

1 Answer 1

1

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.

2
  • Thanks I will restructure and see how it runs. Is there any danger of the GROUP BY slowing the query down?
    – Nathan
    Sep 12, 2014 at 20:33
  • @Nathan unlikely, it kicks in once you get results to group. Note that any query with multiple joins is likely to get sluggish easily from that.
    – Rarst
    Sep 12, 2014 at 20:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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