can we get the raw SQL query based on the search API views?

Actually, the search result didn't return the result (sorting) in the way I need it to be done.

An example I have a product name "Bicycle 100" and there are other product names such as "Bicycle 100 spare parts", "Bicycle 1100", "Bicycle 1000", "Bicycle 2100".

If user search for "Bicycle 100" and the first result I would like it to display "Bicycle 100", "Bicycle 100 spare parts", then only by the others like "Bicycle 1000", "Bicycle 1100", etc.

I am thinking whether I can write a raw query with union with the search api query.

  • If you're using views module, you can enable views settings by going to views settings page "Admin => Structure => Views => Settings", enable "Show the SQL query" and Click on "Save configuration".
    – Yogesh
    Dec 9 '16 at 4:53
  • @Yogesh I have tried that, but it shows "No query was run", however on below preview section there is node id display on the content field. It is strange, so I wonder if this only happen to Search API query
    – cww
    Dec 9 '16 at 17:30
  • drupal.org/project/views_show_query gives the actual query that you can use in the database but I'm pretty sure it won't make it show. Dec 10 '16 at 2:05

The latest stable version of Search API module (7.x-1.20) has a known bug when showing the SQL query that is performed. You will have to apply the patch that appears on that issue or upgrade to the latest development version.

Take into account that even when patch is applied, the information that will appear on Views UI will not be the raw SQL query itself. Instead, you will have the properties of a SelectQueryInterface element:

Index: test_search_api_index_node_index
Keys: 'university'
Parsed keys: array (
    '#conjunction' => 'OR',
    0 => 'university',
Searched fields: field_test_1, ...
status = '1'
Options: array (
    'parse mode' => 'single',
    'conjunction' => 'OR',
    'filter class' => 'SearchApiQueryFilter',
    'search id' => 'search_api_views:test_view_search:test_search_page',

In order to get the raw SQL query, you will have to implement a custom module that alters the Search API query and use a debug tool like Devel module.

 * Implements hook_search_api_db_query_alter().
function yourmodule_search_api_db_query_alter(SelectQueryInterface &$db_query, SearchApiQueryInterface $query) {
  dsm($db_query, 'The SelectQueryInterface element');
  dsm((string) $db_query,'The raw SQL query with tokens');
  // Below I am changing the way Search API performs this search.
  $conditions =& $db_query->conditions();
  if (isset($conditions[0]) && is_string($conditions[0]['field']) && $conditions[0]['field'] == 'word') {
    $conditions[0]['operator'] = 'LIKE';
    $conditions[0]['value'][0] = '%' . $conditions[0]['value'][0] . '%';

When printed, you will see the query has some token strings. Those tokens are the same that Views UI has printed above:

The raw SQL query with tokens =>
SELECT t.item_id AS item_id, SUM(score) AS score
FROM {search_api_db_test_search_api_index_node_index_text} t
LEFT OUTER JOIN {search_api_db_test_search_api_index_node_index} t_2 ON t.item_id = t_2.item_id
WHERE  (word LIKE :db_condition_placeholder_0 ESCAPE '\\') AND (field_name IN  (:db_condition_placeholder_1, :db_condition_placeholder_2, ...)) AND(( (t_2.status = :db_condition_placeholder_12) ))
GROUP BY t.item_id

I hope this helps you.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.