Building an Advanced Search (text, tags, category, custom fields) – Getting the wrong SQL query

by

I’m building an advanced search form, where I want to use the normal text field, tags,categories and also some custom fields.
I created a form with all the values and i created a url parameters to make it nicer and i use pre_get_posts to add the custom fields if it is needed… but the query is not what i expect and it is really weird in my opinion.

I will explain the steps that i did:

1) create a form (I skip this part)

2) make it nicer I build a url and i use the following code to rewrite and do it:

URL:
"/cerca/text/$text/provincia/$provincia/comarca/$comarca/municipi/$municipi/filtres/$filtres/"

Rewrite code

add_action('init', 'create_cerca_url_querystring_parameters');
function create_cerca_url_querystring_parameters()
{
    add_rewrite_rule(
        '^cerca\/text\/([a-zA-Z0-9-+]+)\/provincia\/([a-zA-Z0-9-+]+)\/comarca\/([a-zA-Z0-9-+]+)\/municipi\/([a-zA-Z0-9-+]+)\/filtres\/([a-zA-Z0-9-+,]+)\/?',
        'index.php?s=$matches[1]&provincia=$matches[2]&category_name=$matches[3]&municipi=$matches[4]&tag=$matches[5]',
        'top'
    );
}

3) post_get_post function

add_action('pre_get_posts', 'advanced_search_query');
function advanced_search_query($query)
{
    if (! is_admin() && $query->is_search && $query->is_main_query()) {

        //Get Parameteres
        //$text      = getSearchParameter('text');
        $provincia = getSearchParameter('provincia');
        $municipi  = getSearchParameter('municipi');

        //Configuration
        $query->set('post_type', array( 'post' ));
        $query->set('post_status', array( 'publish' ));

        if(!empty($provincia) && !empty($municipi)){

            $mainArray = array('relation' => 'OR');

            //Set Parameters

            $provArray = array(
                  'key'   => 'provincia',
                  'value' => $provincia,
                  'compare'    => 'LIKE'
                  );
            array_push($mainArray, $provArray);



            $municipiArray = array(
                'key'   => 'municipi',
                'value' => $municipi,
                'compare'    => 'LIKE'
                );
            array_push($mainArray, $municipiArray);


            $query->set('meta_query', $mainArray);

            var_dump($mainArray);
        }
    }
}

/**
 * Return the given parameter value from the search URL
 */
function getSearchParameter($parametre, $decode=TRUE)
{
    $valueToReturn = '';
    $path  = $_SERVER['REQUEST_URI'];
    $split = explode("https://wordpress.stackexchange.com/", $path);
    $key   = array_search($parametre, $split);
    if ($key > 0) {
        $value = $split[$key+1];
        $urlValues = array("text", "provincia", "comarca", "municipi", "filtres","none","");
        if (!in_array($value, $urlValues)) {
          if($decode){
            $valueToReturn = urldecode($value);
          }else{
            $valueToReturn = $value;
          }
        }
    }
    return $valueToReturn;
}

4) then in the search.php I print the query result using

<?php echo $GLOBALS['wp_query']->request; ?>

And here is where my surprise arrive and i don’t know the query look like that:

Query with municipi and provincia:

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) WHERE 1=1 AND ( 0 = 1 ) AND ( ( wp_postmeta.meta_key = ‘provincia’ AND wp_postmeta.meta_value LIKE ‘%barcelona%’ ) OR ( wp_postmeta.meta_key = ‘municipi’ AND wp_postmeta.meta_value LIKE ‘%sant%’ ) ) AND wp_posts.post_type=”post” AND ((wp_posts.post_status=”publish”)) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 18

Another query with only text:
/cerca/text/test/provincia/none/comarca/none/municipi/none/filtres/none/

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts WHERE 1=1 AND ( 0 = 1 AND 0 = 1 ) AND (((wp_posts.post_title LIKE ‘%test%’) OR (wp_posts.post_excerpt LIKE ‘%test%’) OR (wp_posts.post_content LIKE ‘%test%’))) AND wp_posts.post_type=”post” AND ((wp_posts.post_status=”publish”)) GROUP BY wp_posts.ID ORDER BY wp_posts.post_title LIKE ‘%test%’ DESC, wp_posts.post_date DESC LIMIT 0, 18

Here goes my question:

Does anyone know why i get this SQL ? if i remove the condition (0=1) then it works, but i don’t know why it is there.
Am I doing something wrong ? I just want to filter by provincia and municipi if this parameters are set, any idea ?

Inspired in the following posts:

How to use custom field to search for wordpress post type?

Advanced search form with filters for custom taxonomies and custom fields

Custom search for custom post type, custom meta and search fields

Thanks in advance,

1 Answer
1

SOLUTION:

I will post my solution and explain what was the problem and I hope that this post can help someone in the future.

As we talk in the small comments the problem, was that I was sending a non existing taxonomy by error and this was causing the AND (0=1).

To fix the whole problem I created a add_rewrite_rule for each case when I have parameter or not like the following example:

add_rewrite_rule(
   '^cerca/comarca/([a-zA-Z0-9-+]+)/?',
   'index.php?s=&category_name=$matches[1]',
   'top'
);

 add_rewrite_rule(
   '^cerca/municipi/([a-zA-Z0-9-+]+)/?',
   'index.php?s=&municipi=$matches[1]',
   'top'
);
   add_rewrite_rule(
   '^cerca/provincia/([a-zA-Z0-9-+]+)/?',
   'index.php?s=&provincia=$matches[1]',
   'top'
);
add_rewrite_rule(
   '^cerca/provincia/([a-zA-Z0-9-+]+)/comarca/([a-zA-Z0-9-+]+)/municipi/([a-zA-Z0-9-+]+)/?',
   'index.php?s=&provincia=$matches[1]&category_name=$matches[2]&municipi=$matches[3]',
   'top'
);

….

Thanks to @janh2 for all the helpful comments.

Leave a Comment