Learning SQL

I did a lot of playing around with solr to get this right. Unfortunately there is not a lot of documentation on searching within a certain field. With the SQL language you can search for multiple terms in a given field using the IN operator. In Solr you have the option of specifying a field using the qf operator, but what if you have more search terms searching other fields? The problem becomes far more complex. Given the following SQL statement how would we query Solr?

SELECT *
FROM table_name
WHERE column_name IN (value1,value2,...) AND other_column='foo'

There are couple of ways to do this, but I think there is one very good and much easier way. The first obvious way to query solr is to match the fields to columns manually.

/select?q=*:*&fq=column_name:value1 AND column_name:value2 AND other_colum:'foo'

This is all fine and dandy but what if I want to be able to add a list of content to the query? Adding the column name every time can become a chore and make the query unnecessarily large. To do an IN query try this:

/select?q=*:*&fq=column_name:+(value1 value2) AND other_colum:'foo'

Having used this one before, I can tell you it works for me using Solr 3.6 and that the placement of the + symbol is key.

And what about a NOT IN query?

SELECT *
FROM table_name
WHERE column_name NOT IN (value1,value2,...) AND other_column='foo'

The ‘bad’ way:

/select?q=*:*&fq=-column_namevalue1 AND -column_name:value2 AND other_colum:'foo'

This one I found from working with eZ Publish and eZ Find, and I happen to like it a lot. It is a clever little Solr query that selects all from the column then filters out what we don’t want.

/select?q=*:*&fq=other_colum:'foo' AND column_name:[* TO *] -column_name:(value1 value2)

column_name:[* TO *] is like saying select all from the column, but then we add -column_name:(value1 value2) to filter out what we don’t want.

Cheers and good luck!