Union of two or more queries [#2476145]

Problem/Motivation

I have a drupal site with a content type querycollection that basically consist of a list of saved searches. When the querycollection node is viewed the result of the queries is listed with the top 5 result for each query. The queries consist of a fulltext search key and some added facet filters. The query is build and executed in preprocess node with SearchApiQuery.

I would like to add a list of the top 5 newest nodes in all the queries in the node view. So I need to make a union of the saved queries, sort by created date and limit to 5.

Is that possible with SearchApiQuery to build more queries and execute them in a union ?

The search is executed on a solr server and the query is build like this:

    $searchquery = new SearchApiQuery($index, $options);
    $searchquery->keys($query_parameters['keys']);
    if (!empty($query_parameters['f'])) {
      $filter = $searchquery->createFilter('AND');
      foreach ($query_parameters['f'] as $facet) {
        $filter_key = substr($facet,0,strrpos($facet,':',0));
        $filter_value = substr($facet,(strrpos($facet,':')+1));
        $filter->condition($filter_key, $filter_value);
      }
      $searchquery->filter($filter);
    }
    $searchquery->range(0,$range);
    ...

Now I would like to make two of these and get the union sorted by created date as result.

Comments

Comment #1

tbrix CreditAttribution: tbrix commented 22 April 2015 at 20:04

Issue summary:

View changes

Comment #2

drunken monkey

he/him

German

Vienna, Austria

CreditAttribution: drunken monkey as a volunteer commented 29 June 2015 at 13:10

Component:	Database backend	» Framework
Status:	Active	» Fixed

In general, it is not possible to just create two queries and then combine them, no.
However, when you have a fixed code for creating the queries, it's quite easy, by just combining the relevant filters (and search keywords) with OR. Something like this:

  $options['parse mode'] = 'direct';
  $searchquery = new SearchApiQuery($index, $options);
  $keys = '(' . $query_parameters[0]['keys'] . ') OR (' . $query_parameters[1]['keys'] . ')';
  $searchquery->keys($keys);
  $filters = $searchquery->createFilter('OR');
  foreach ($query_parameters as $single_query_parameters) {
    $filter = $searchquery->createFilter('AND');
    foreach ($single_query_parameters['f'] as $facet) {
      $filter_key = substr($facet,0,strrpos($facet,':',0));
      $filter_value = substr($facet,(strrpos($facet,':')+1));
      $filter->condition($filter_key, $filter_value);
    }
    $filters->filter($filter);
  }
  $searchquery->filter($filters);
  $searchquery->range(0,$range);

With Solr, you can even easily circumvent the Search API query more or less entirely and send a Solr search request via $server->getSolrConnection()->search(…).

Hope this helps!

(Oh, but if you're using Solr, why did you select "Database backend" as the component?)

Comment #3

tbrix CreditAttribution: tbrix commented 2 July 2015 at 14:26

Thanks a lot for your input. Much appreciated.

Was it just keywords it would work with the OR as suggested, but I need the facets to work on each query separately.

If for example I have
query a = keys:fulltextsearch = "horror", facets:mediatype = "books"
query b = keys:fulltextsearch = "fantasy", facets:mediatype = "movie"

The query would be
top 5 "horror" OR "fantasy" and (type=books and type=movies)
If I "AND" the facets results would be only items being both movie and books - if I OR the facets result would include fantasy books and horror movies)

What I want is a union of the two searches ie: top5 horror books AND fantasy movies.

I have solved it now by executing each search separately and then process the results afterwards. Selecting resulting entities with a db_select on entity ids.

Thanks for the pointer to $server->getSolrConnection()->search(…). Havent checked it out yet, I'll do that.

The Database backend component was a mistake :) sorry.

Have a nice one :)