1

I'm migrating search logic from using basic model scopes and the geocoder gem to use Thinking Sphinx.

I've migrated all the logic over fine with the exception of the geosearching logic. When I run a search I get the following error:

parse error: Sphinx expr: syntax error, unexpected TOK_IDENT near 'latitude, longitude)

The query that it tried to run was:

SELECT GEODIST(0.9037681385969557, 0.08613329012956664, latitude, longitude) AS geodist, * FROM refinery_marketplaces_business_core WHERE MATCH('consultants') AND sphinx_deleted = 0 ORDER BY geodist ASC LIMIT 0, 20;

Here's the relevant extracts:

Index Definition:

has 'RADIANS(latitude)', :as => :latitude,  :type => :float
has 'RADIANS(longitude)', :as => :longitude, :type => :float
group_by 'latitude', 'longitude' # Needed for PostgreSQL

Controller:

@businesses = Business.search params[:keywords], :geo => [0.9037681385969557, 0.08613329012956664], :order => "geodist ASC"

The only two similar posts I can find on here are:

Both of which were basically the result of people calling search on the wrong model - I'm calling it on the correct model.

Here are the versions of everything in play:

  • Ruby 2.0.0p247
  • Rails 3.2.14
  • Thinking Sphinx 3.1.1
  • Sphinx 2.1.4 (brew installed)
  • Postgres 9.3.2 (brew installed)

UPDATE:

I found out you can enter SphinxQL queries into the Sphinx CLI after reading this post: Connect to SphinxQL through Linux command-line

mysql -P9306 --protocol=tcp --prompt='sphinxQL> '

A couple of observations after running this:

  • Removing the GEODIST() references the query returns fine
  • Running a simple SELECT * query shows that latitude and longitude aren't in the index
  • Running \d+ refinery_marketplaces_businesses in psql shows they are fields on the table:
    • latitude | double precision
    • longitude | double precision
  • Both fields are included in the sql_query generated by rake ts:rebuild but they don't show up in the generated index.

Any help hugely appreciated!

Thanks

Community
  • 1
  • 1
stuartbates
  • 229
  • 1
  • 10

1 Answers1

2

I managed to sort this so including the answer for anyone else who runs into this error message.

The error: unexpected TOK_IDENT basically means that a field referenced in the query doesn't exist in the index.

The best way to verify what's in your index is to run the Sphinx CLI using:

mysql -P9306 --protocol=tcp --prompt='sphinxQL> '

And run a SELECT * query like:

SELECT * FROM your_index_core WHERE sphinx_deleted = 0 LIMIT 0, 20;

From here you can see the fields in the index across the top.

I can't figure out why they didn't exist in the index - I'd ran rake ts:rebuild multiple times to no avail. In the end I had to stop searchd, manually delete the configuration file and indexes and rebuild from scratch.

stuartbates
  • 229
  • 1
  • 10