Incorrect sorted results

[Azzawie]

Hello @pat, I'm having this issue recently where

If I do this call in the rails console will give me incorrect results (not sorted result)

ScheduledService.search({order: "eec_name asc"})

while if I do the equivalent query in mysql --host 127.0.0.1 --port 9306 will give me the correct result (Alphabetic sorted)

SELECT * FROM `scheduled_service_core`, `scheduled_service_delta` WHERE `sphinx_deleted` = 0 ORDER BY `eec_name` asc LIMIT 0, 20 OPTION max_matches=10000;

this is the call in the index file

indexes location.eec.name, as: :eec_name, sortable: true

I tried to reindex, rebuild the index but no luck. Am I missing something?

[pat]

Hi Mustafa - what you're doing seems correct!

When you run the Ruby version of the query, in your app logs you should see the equivalent SphinxQL query. Is it the same as what you're running manually?

Also: which versions of Thinking Sphinx, Rails, and Sphinx are you using?

[Azzawie]

When you run the Ruby version of the query, in your app logs, you should see the equivalent SphinxQL query. Is it the same as what you're running manually?

Yes, it's the same one I'm running locally (mentioned above)

thinking-sphinx (4.0.0)
Sphinx 2.2.11-id64-release (95ae9a6)
Rails 4.2.8
ruby 2.3.4p301 (2017-03-30 revision 58214) [x86_64-darwin19]

[Azzawie]

@pat Unfortunately, I'm still facing an issue where the thinking-sphinx has incorrect data, I listed below the SQL query and the value for the eec_name column I get in the TS console vs what the value should be as you can see in the rails console result.

• TS console

  
  SELECT eec_name, sphinx_internal_id, sphinx_internal_class  FROM `scheduled_service_core`, `scheduled_service_delta` WHERE `sphinx_deleted` = 0 AND sphinx_internal_id= 2270067 ;

+----------+--------------------+-----------------------+ | eec_name | sphinx_internal_id | sphinx_internal_class | +----------+--------------------+-----------------------+ | | 2270067 | ScheduledService | +----------+--------------------+-----------------------+

 - Rails console.
 ```rb
ScheduledService.find(2270067).location.eec.name
=> "Shopper Events - East"

• In my scheduled_service_index.rb file.

  indexes location.eec.name, as: :eec_name, sortable: true

[pat]

Hmm… I'm still not sure why this is happening. It looks like you're using SQL-backed indices (:with => :active_record) - is that correct? And can you confirm that in the scheduled_service_core_0 source in the generated Sphinx configuration file (config/development.sphinx.conf locally) there is the line sql_field_string = eec_name?

[Azzawie]

True, we are using active_record

ThinkingSphinx::Index.define :scheduled_service, with: :active_record, delta: ThinkingSphinx::Deltas::SidekiqDelta do

we also have the line in the config/development.sphinx.conf file

sql_field_string = eec_name

[atomical]

Pat,

If one or both of the associations is null would that impact the sorting?

# location and location.eec could be nil
indexes location.eec.name, as: :eec_name, sortable: true

[pat]

@atomical it's a good suggestion - but in this case, going by the Rails console output @Azzawie has shared, it looks like neither of the associations are null.

@Azzawie in that source in the config file, there will be a SQL query - can you run that against your database (adapting the WHERE clause to just this specific record) and see if the eec_name value is returned correctly?

[pat]

… although, just thinking a bit more - that SQL query should return the right results, because you've noted that Sphinx is sorting correctly, but Thinking Sphinx is then muddling things up.

Just to be sure: when you're running the search through Thinking Sphinx, are you only passing through the :order option? Or are there other options being added - in particular, I'm wondering about :sql or :middleware that may adjust sorting behaviour?

[Azzawie]

@pat It turned out that it is an issue with the association, sorry about the false alarm. thanks for your help and support

[pat]

Ah, great to hear it's resolved one way or another :)