How to view search API raw sql query

Question

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.

Answer 1

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, ...
Filters:
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.