Filter for where with join with wpostmeta in query ?

We do not have enough information. I suspect that you need to add a join to the postmeta table. Please post the query and parameters, and $wp_query->request after the query.

This thread may also provide some help.

Thanks for your answer and sorry about lake of information about my request.

Here is my searchform :

<form id="searchform" method="get" action="<?php echo $_SERVER['REQUEST_URI']; ?>">
		<input id="key" name="key" type="text" onfocus="this.value='';" value="<?php if(!empty($_REQUEST['key'])): ?><?php echo $_REQUEST['key']; ?><?php else: ?>Recherche par mots cl&eacute;s<?php endif; ?>" size="25" />
		<select name="nb" id="nb">
		<option value="" <?php if(empty($_REQUEST['nb'])):?>selected="selected"<?php endif; ?> onfocus="this.value='';">Nombre de pi&egrave;ces</option>
		<option <?php if(!empty($_REQUEST['nb']) && $_REQUEST['nb'] == 1):?>selected="selected"<?php endif; ?>>1</option>
		<option <?php if(!empty($_REQUEST['nb']) && $_REQUEST['nb'] == 2):?>selected="selected"<?php endif; ?>>2</option>
		<option <?php if(!empty($_REQUEST['nb']) && $_REQUEST['nb'] == 3):?>selected="selected"<?php endif; ?>>3</option>
		<option <?php if(!empty($_REQUEST['nb']) && $_REQUEST['nb'] == 4):?>selected="selected"<?php endif; ?>>4</option>
		<option <?php if(!empty($_REQUEST['nb']) && $_REQUEST['nb'] == 5):?>selected="selected"<?php endif; ?>>5</option>
		<option <?php if(!empty($_REQUEST['nb']) && $_REQUEST['nb'] == 6):?>selected="selected"<?php endif; ?>>6</option>
		</select>
		<input id="prix_mini" name="prix_mini" type="text" onfocus="this.value='';" value="<?php if(!empty($_REQUEST['prix_mini'])): ?><?php echo $_REQUEST['prix_mini']; ?><?php else: ?>Prix mini<?php endif; ?>" size="10" />
		<input id="prix_maxi" name="prix_maxi" type="text" onfocus="this.value='';" value="<?php if(!empty($_REQUEST['prix_maxi'])): ?><?php echo $_REQUEST['prix_maxi']; ?><?php else: ?>Prix maxi<?php endif; ?>" size="10" />
		<input id="searchsubmit" name="searchsubmit" type="submit" value="Trouver" onclick="if(key.value == 'Recherche par mots cl&eacute;s')key.value='';if(prix_mini.value == 'Prix mini')prix_mini.value='';if(prix_maxi.value == 'Prix maxi')prix_maxi.value='';" />
</form>

Then my request :

<?php global $query_string; ?>
<?php
if(!empty($_REQUEST['nb'])) $search = '&meta_key=qpieces&meta_compare=&meta_value='.$_REQUEST['nb'];
if(!empty($_REQUEST['prix_mini'])) $search = '&meta_key=mprix&meta_compare=>=&meta_value='.$_REQUEST['prix_mini'];
if(!empty($_REQUEST['prix_maxi'])) $search = '&meta_key=mprix&meta_compare=<=&meta_value='.$_REQUEST['prix_maxi'];
?>
<?php query_posts($query_string.'&orderby=meta_value&meta_key=mprix&order=ASC'.$search); ?>

I’m doing a filter where but I only want to search with all these elements at the same time et not one a time.

And the $wp_query -> request

SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) INNER JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id) JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) WHERE 1=1 AND wp_term_taxonomy.taxonomy = 'category' AND wp_term_taxonomy.term_id IN ('7') AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') AND wp_postmeta.meta_key = 'mprix' GROUP BY wp_posts.ID ORDER BY wp_postmeta.meta_value ASC LIMIT 0, 5

How can I do an inner join in the filter where with custom fields (wppostmeta table) ? Any ideas ?

I see also the link of the post you give me. It’s very interesting but how can I add a supplementary search with : search by terms (LIKE on title or content), number of rooms (meta compare with egal)

Thanks for your help

This is a lot of code, and I cannot test it, but it should be close to correct.

First, add these functions to your functions.php:

<?php function mam_posts_fields ($fields) {
   global $mam_global_fields;
   if ($mam_global_fields) $fields .= $mam_global_fields;
   return $fields;
}
function mam_posts_join ($join) {
   global $mam_global_join;
   if ($mam_global_join) $join .= $mam_global_join;
   return $join;
}
function mam_posts_where ($where) {
   global $mam_global_where;
   if ($mam_global_where) $where .= $mam_global_where;
   return $where;
}
function mam_posts_orderby ($orderby) {
   global $mam_global_orderby;
   if ($mam_global_orderby) $orderby = $mam_global_orderby;
   return $orderby;
}
add_filter('posts_fields','mam_posts_fields');
add_filter('posts_join','mam_posts_join');
add_filter('posts_where','mam_posts_where');
add_filter('posts_orderby','mam_posts_orderby');
?>

Then, for your nb, prix_mini, and prix_maxi, use code like this:

<?php
if(!empty($_REQUEST['nb'])) {
 $mam_global_fields = ', nb.meta_value nb_value';
 $mam_global_join = " JOIN $wpdb->postmeta nb
    ON ({$wpdb->posts}.ID = nb.post_id AND nb.meta_key = 'qpieces'
       AND nb.meta_value = {$_REQUEST['nb']})";
}
if(!empty($_REQUEST['prix_mini'])) {
  $mam_global_fields .= ', mini.meta_value mini_value';
  $mam_global_join .= " JOIN $wpdb->postmeta mini
     ON ({$wpdb->posts}.ID = mini.post_id AND mini.meta_key = 'mprix'
        AND mini.meta_value >= {$_REQUEST['prix_mini']})";
}
if(!empty($_REQUEST['prix_maxi'])) {
  $mam_global_fields .= ', maxi.meta_value maxi_value';
  $mam_global_join .= " JOIN $wpdb->postmeta maxi
     ON ({$wpdb->posts}.ID = maxi.post_id AND maxi.meta_key = 'mprix'
        AND maxi.meta_value <= {$_REQUEST['prix_maxi']})";
}
?>

Finally, use any other parameters you need in your query:

<?php query_posts('posts_per_page=10');

This is so great i want to cry! The mysql-queries can be such a pain in the ass sometimes!

Thanks a million!

You are welcome! Now, please use the dropdown at top right to mark this topic ‘Resolved’.

Thanks again vtxyzzy I’m going to test you script I think it’ll work.

Thanks again for your help

Thanks vtxyzzy, it works perfectly with the custom fields.

Still, it has a little issue: it’s not taking into consideration the s= parameter, the search text input.

Thanks a lot vtxyzzy! Your examples were really helpful.