SQLSTATE[42S22] in 2 variations

Rechi, can you update everything and confirm that this still exists, if you're still using fuzzysearch? Thanks.

I am getting something similar trying to use Fuzzy Search when creating a view.. My views preview gives me an error "SQLSTATE[42S22]: Column not found: 1054 Unknown column 'percent' in 'order clause'"

To remove this error I needed to add a filter in views.

I'm going to close this. For the views issue see #1472116: Views Search Error :createKeysQuery.

For me, the moment I added a sorting (not a filter) on the View, this error went away.

I'm not using views with Fuzzy Search, only Search Page. Is working the module and let me search, the problem is if I put less than 3 letters in the search input answer this error:

PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'percent' in 'order clause': SELECT t.item_id AS item_id, :score AS score FROM {fuzzysearch_default_fuzzysearch_index_search_api_language} t ORDER BY percent DESC, score DESC LIMIT 10 OFFSET 0; Array ( [:score] => 1 ) in FuzzySearchService->search() (line 712 of \misitio\sites\all\modules\fuzzysearch\includes\service.inc).

For example: if I put 'pro' find all possible combinations, but with 'p' or 'pr' answet this mistake.

For other side, I add in kategories terms in the index, but is not include in the result of searching.

My config:

• Drupal 7.14
• Fuzzy Search 7.x-1.x-dev
• Search API 7.x-1.1
• search_api_page 7.x-1.0-beta2

I'm getting the following when I try to index on the status page:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE (item_id = '3173')' at line 1

This error occurs for me too when searching for terms with 1 or 2 letters. If you enter more letters there is no problem.

It looks like the search key gets filtered if it's shorter then the setting in the fuzzysearch server (check the splitKeys function in service.inc), but there is no error thrown and/or empty result set given back.

I fixed the issue by returning an empty result set if there are no key's, or no valid keys in the search() function.

Change line 629 - 632:

    elseif ($keys_set) {
      $msg = t('No valid search keys were present in the query.');
      $this->warnings[$msg] = 1;
      $results = array();
      $results['result count'] = 0;
      $results['results'] = array();
      return $results;
    }
    else{
      $results = array();
      $results['result count'] = 0;
      $results['results'] = array();
      return $results;
    }

@sijuwi, try applying the patch for search_api_db service.inc file at http://drupal.org/node/1346846#comment-5574146 manually to the fuzzysearch service.inc file in fuzzysearch/includes/. The function FuzzySearchService->indexItem() is basically the same as SearchApiDbService->indexItem().

@sijuwi,

Scratch that. Install this patch for search_api instead: http://drupal.org/node/1414138#comment-6017252. See my comments on that issue at http://drupal.org/node/1414138#comment-6207610

Thanks, will do

I tried both #9 and #10 separately but none of them worked for me...

Keep in mind that #9 and #10 are in response to #7. The SQLSTATE error message in #7 is indicative of a different problem from the one presented in the original post.

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'percent' in 'order clause'

On a fresh module installation inside a d7search distro.

Also disappeared for me like in #5, adding a 'sort by relevance' to the view. Prior to that, the views query would not be valid.

Thanks seanB, a fix is committed. This fix isn't tested in views. There are a lot of unrelated or slightly related views errors mentioned above. Please search the issue queue and create a new issue if you can't find an existing one.

#14 solved the issue for me - thanks devyd!