Support » Fixing WordPress » Filter for where with join with wpostmeta in query ?

  • Resolved tprod

    (@tprod)


    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.

Viewing 8 replies - 1 through 8 (of 8 total)
  • 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.

    Thread Starter tprod

    (@tprod)

    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’.

    Thread Starter tprod

    (@tprod)

    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.

Viewing 8 replies - 1 through 8 (of 8 total)
  • The topic ‘Filter for where with join with wpostmeta in query ?’ is closed to new replies.