3

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!

2

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.

  • Interesting hint! Meanwhile I started to code a custom module using db_select directly on the profile2 fields. It is a large query because lots of left joins (field data tables), but for now that works and I have all coding-freedom like I want. My project time is short and I had to find a quick solution. But I will continue to follow the search API and filter building question of yours as well. Maybe I will have some time to try out the search API createFilter object & friends and "switch" my module using db_select to use the search API filter. I will surely report back when done! – Andreas W. Wylach May 11 '12 at 5:27

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.