Thinking Sphinx, sorting and subselects

Question

I'm using the thinking-sphinx gem (3.1.0), to implement a search function. I'd like to use a text attribute of my model to order the results, eg: rows with the property attribute set to 'smiley' first. This is the syntax I'm using:

Model.search(query, 
             select: "*, (SELECT COUNT(*) FROM models WHERE models.property = 'smiley') AS prop",
             order: "prop desc, created_at desc")

It all looks nice to me, yet the machine is always right, and it gives this error message:

sphinxql: syntax error, unexpected SELECT, expecting IDENT (or 11 other tokens) near 'SELECT COUNT(*) FROM etc.

Where is my syntax wrong?

pat pat · Accepted Answer · 2014-08-20T06:26:26

I think what you're trying to do is only possible with Sphinx 2.2.3 or newer (which is when string attribute filtering was added as a feature). If you've got that, then the following should hopefully work:

Model.search query,
  select: "*, weight() as rank, IF(property = 'smiley', 1, 0) as smiley",
  order:  "smiley DESC, rank DESC"

It might work with earlier versions of Sphinx (given it's not technically a filter, because it's not in the WHERE clause), but I very much doubt it.

Thinking Sphinx, sorting and subselects

1 Answers