A bit less than a year ago I last did a presentation about a telephone book application where we used SQL to do some fairly advanced filtering over about 30 tables of data. The app generated SQL statements that filled pages, the more terms the more pages, but on a 10k dataset it still came back within a few milliseconds, thanks to a ton of indexing and denormalization tricks (SQL Server is a lot more powerful here than MySQL) I had applied. Now in a more recent project I am dealing with 10M+ dataset running on MySQL and so decided to learn about Solr. Wow, that thing is amazing and way more flexible in terms of query language than I expected. As a result I do not see it any more for just projects that are too big for an RDBMS, but more as the way to do search in general. I have mentioned resolutionfinder.org a few times (used to be called UN-informed.org). Solr is a key piece there and more importantly I am looking to expand the use of Solr query language quite a bit. Actually for those who know, you can already do a lot more powerful queries, something Liip will be investing some more time to make more accessible to end users with some UI tweaks planned in July. But in this blog post I want to talk about a prototype class I threw together (Look ma', I'm using git!) by working ezcSearch to help me in parsing and transforming end user queries into complex Solr queries.
Right now we have 3 input boxes on the resolutionfinder.org start page. The first one lets user input terms which are searched in the document title and the clause content. Here I recently exposed some of the syntax supported by the dismax handler like "+" to require and "-" to prohibit terms. Next is an input box that doesn't directly affect the search filtering. What it does is use auto suggest to build up a list of tag to filter on. The third input box actually is a separate search form entirely which is used for searching for specific documents by document code. My vision is to bring these all together into one input box. So if someone wants to do a fulltext search they could do something like the following:
"security council" +africa tag:malaria code:A/RES/*
This query would be parsed and result in looking for documents that must contain "africa" in the document title or clause content (scoring those that also contain "security council" higher), which are tagged with "malaria" and who's document code started with "A/RES/". This requires quite a bit of transformation. Actually the final queries I want to construct looks like this:
q=tag_ids:23 AND document_code_prefix:(A/RES/) AND (_query_:"{!dismax qf='content document_title' pf='content document_title' mm=0 v=$qq}")&qq="security council" +africa
So what happened here? As you can see I transformed "tag" to "tag_ids" and "code" to "document_code_prefix". These are the internal fields I use in the Solr index. There is actually also a "document_code" field, which I would use if the code would not end with a "*". The "document_code_prefix" just copies the "document_code" and applies an ngram filter to make prefix searches super fast. Also you may notice that instead of "malaria" I am filtering for 23, this is because right now I just index the tag id's and not the names. The next bit is taking all the un-fielded filters and apply a dismax search handler on them. In order to not require fancy escaping I use "v=$qq" to define that the actual search terms are passed in the "qq" GET parameter.
With the above mentioned class this sort of thing becomes fairly simple. I am using the symfony sfSolrPlugin, which provides query construction tools via the sfLuceneCriteria object. All I have to do is use the parser to parse the end user query into tokens and then use a custom term class that uses the sfLuceneCriteria instance to handle the serialization.
<?php
$criteria = new sfLuceneCriteria;
$stack = array(new phpSolrQueryTermCustom('AND', $criteria));
$parser = new phpSolrQueryParser($factory);
$tokens = $parser->parse($q, $stack);
$terms = $parser->processTerms($tokens);
var_dump($q);
var_dump($terms->serialize());
?>
You can check out the examples to get some more details about what is possible. Following is the code to the example on github, but here is a link to the actual term class and action class used in production.
<?php
class phpSolrQueryTermCustom extends phpSolrQueryTerm {
protected $criteria;
public function __construct($op = '', $criteria = null)
{
$this->op = $op;
$this->criteria = $criteria;
}
protected function processTerm($term) {
$prefix = $term->prefix;
$field = $term->field;
$term = (string)$term;
$op = $this->op;
$op = rtrim(" $op ").' ';
if (!empty($field)) {
switch ($field) {
case 'code':
$field = substr($term, -1, 1) === '*' ? 'document_code_prefix' : 'document_code';
$term = $field === 'document_code_prefix' ? substr($term, 0, -1) : $term;
break;
case 'tag':
$field = 'tag_ids';
/*
$q = Doctrine_Query::create()
->select('t.id')
->from('Tag t')
->where('t.name = ?', array($term));
$term = $q->execute(array(), Doctrine_Core::HYDRATE_SINGLE_SCALAR);
*/
$term = rand(1, 100);
break;
default:
throw new Exception('Unsupported field: '.$field);
}
$this->criteria->addField($prefix.$field, $term, $op, true);
return;
}
$term = parent::processTerm($term);
return $prefix.$term;
}
public function serialize()
{
$terms = $this->terms;
$op = $this->op;
$op = rtrim(" $op ").' ';
$dismax = array();
foreach ($terms as $key => $term) {
if ($term instanceOf phpSolrQueryTerm) {
$term = $this->processTerm($term);
if (!is_null($term)) {
$dismax[] = $term;
}
} else {
$dismax[] = parent::processTerm($term);
}
}
if (!empty($dismax)) {
$dismax = implode(' ', $dismax);
$subcritieria = new sfLuceneCriteria;
$subcritieria->add('_query_:"{!dismax qf=\'content document_title\' pf=\'content document_title\' mm=0 v=$qq}"', $op, true);
$this->criteria->add($subcritieria, 'AND');
$this->criteria->addParam('qq', $dismax);
}
$q = $this->criteria->getParams();
$q['q'] = array($this->criteria->getQuery());
return $q;
}
}
?>
PlanetMySQL Voting:
Vote UP /
Vote DOWN