How to enable sorting a associations join/includes table?

[dekanaxo]

For example there are two tables: books (id, title, date) and authors (id, name, country, date). When you search for the book, I show all the information about the author. Thus, we will see the columns:

books.id books.title books.created_at authors.name authors.country authors.created_at

Usually in a situation I can sort as you like, using for example orders authors.country desc. However, when working with thinking_sphinx I did not get to do so. Now code of book_index.rb looks like this:

ThinkingSphinx::Index.define :book, with: :real_time do
  indexes title, sortable: true
  indexes author.name, as: :author_name, sortable: true
  indexes author.country, as: :author_country, sortable: true

  has title, type: string
  has author.name, type: string
  has author.country, type: string
  has created_at, type: :timestamp
  has author.created_at, type: :timestamp
end

Now, to search, I write this:

Book.search params[:search], sql: {include: :author}, order: 'author_name asc'

And this answer:

index book_core: sort_by attribute 'author_name' not found - SELECT * FROM 'book_core' WHERE MATCH('blablabla') AND 'sphinx_deleted' = 0 ORDER BY 'author_name' asc LIMIT 0, 50; SHOW META

Question: what's wrong? I would like to be able to sort the data in the main table (books), but also the associated attributes (such as author.created_at).

Thanks!

[pat]

Generally everything you're doing looks right. Have you run ts:regenerate (or just ts:rebuild on v3.4+) since adding in the sortable option? Making a field sortable means it'll add a corresponding attribute under the hood, which means regeneration is required.

Also: I'd recommend giving your string attributes aliases so they don't conflict with the fields and other attributes. e.g. You've got title twice, and created_at (for both book and author) twice.

[dekanaxo]

Okay, I'll try later. Thank you! I spent on the solution of the problem about 6 hours and gave up. Want to relax a bit...

[pat]

This issue's been quiet for a month, so I'm going to close it. If you have further questions though, please do comment and we can re-open it :)

[frerotg]

I have the same issues.. Do you find a solution ?

[pat]

@frerotg if you'd like, you're welcome to open a new issue - feel free to go into detail about what you're trying to do, your index definitions, and the version of TS that you're using :)