Support » Fixing WordPress » Filter for where with join with wpostmeta in query ?
Filter for where with join with wpostmeta in query ?
-
Hi everyone,
I try to do a custom search form and I have a problem to do a between width custom fields for this Real Estate website. I try to put a filter where in functions.php like this :
function filter_where($where = '') { if(!empty($_REQUEST['prix_mini'])) $where .= " AND wposts.ID = wpostmeta.post_id AND wpostmeta.meta_key = 'mprix' AND wpostmeta.meta_value >= '".$_REQUEST['prix_mini']."'"; if(!empty($_REQUEST['key'])) $where .= " AND (post_title LIKE '%".$_REQUEST['key']."%' OR post_content LIKE '%".$_REQUEST['key']."%')"; return $where; } add_filter('posts_where', 'filter_where');
But I have no results
Any ideas ?
Thanks for your help.
-
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é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è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é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.
- The topic ‘Filter for where with join with wpostmeta in query ?’ is closed to new replies.