Use createFilter to build an advanced query using AND and OR with the search API

Question

I have a search index of about 15 fields (profile2 fields) build with the search API indexer (mysql) and I need to build an advanced query. All fields are fulltext. The query input form would have about 6 fields:

name, postal code, zip, city, keywords

The complete sql query (pseudo code), conditionally based on what the user entered in the form fields, would look like this

SELECT 
  * 
FROM 
  {index} 
WHERE 
  (field_last_name = 'name' OR field_first_name = 'name') 
AND 
  field_postal_code = 'zip' 
AND 
  field_city = 'city' 
AND 
  (field_interests = 'keywords' OR field_hobbies = 'keywords' OR ...) 
ORDER BY ...

Is this possible at all to build a filter like this?

I try to find a way to build an advanced search and I first tried Views using the filter criteria but I can not build an exposed form like I want. Also as a next step I need to integrate a proximity search on the postal code field. After some reading and hours of using google I thought the search API would give me a go to get a solution on that problem.

To completely understand my problem please refer to this question I asked yesterday: Views 3 Custom filter for Profile2 data

I would really appreciate some hints if I am on the right track or if I have to code a completely distinct module to build that kind of search engine.

Thanks to you all for your help!

Answer 1

This should be possible:

<?php
$or1 = $query->createFilter('OR');
$or1->condition('field_last_name', 'name');
$or1->condition('field_first_name', 'name');
$query->filter($or1);

$query->condition('field_postal_code', 'zip');
$query->condition('field_city', 'city');

$or2 = $query->createFilter('OR');
$or2->condition('field_interests', 'keywords'); 
// if field is fulltext, the default '=' operator is interpreted as 'contains'. 
// to search over all fulltext fields: $query->keys('keywords')
$or2->condition('field_hobbies', 'keywords');
//...
$query->filter($or2);
$query->sort('field', 'ASC');
$query->execute();

But IMHO and unfortunately you won’t be able to further nest queries/filters like AND ((field1 = 1 AND field2 = 1) OR (field1 = 2 AND field2 = 2)). I’m going to open another thread for this problem.