Sorting by association count in Thinking Sphinx

Question

I have a channel model with 2 associations, "contents" and "subscriptions".

In the channel index the user has the possibility of ordering the channels by number of subscriptions or number of approved contents.

While in development everything seems to work properly (by observation of the results, can be malfunctioning and be a question of not enough data to see it properly), in staging the results are random, sometimes showing them properly, sometimes don't.

At first I wasn't using delta indexes and thought the problem could be there so every time I approve a content I call:

Delayed::Job.enqueue(DelayedRake.new("ts:index"), queue: "sphinx")

Since the subscriptions don't have indexes, I don't reindex every time I create one ( should I do it? )

Then I started using delta indexes in the channel and I still get the same problems:

ThinkingSphinx::Index.define :channel, with: :active_record, delta: true do
  # fields
  indexes :name, sortable: true
  indexes description

  # attributes
  has created_at, sortable: true
  has approved, type: :boolean
  has public, type: :boolean

  join subscriptions
  has "COUNT(subscriptions.id)", as: :subscription_count, type: :integer, sortable: true

  join contents.approved
  has "COUNT(contents.id)", as: :content_count, type: :integer, sortable: true
end

And here is the search call in the controller:

  def index
    if params[:order_by].present?
      @channels = Channel.search params[:search],
                                 order: "#{params[:order_by]} DESC",
                                 page: params[:page], per_page: 6
    else
      @channels = Channel.search params[:search],
                                 order: :name,
                                 page: params[:page], per_page: 6
    end
  end

Summarising, my questions would be: 1. Are my channel indexes well formed? 2. Should subscriptions by indexed as well or is it enough to join them in my channel index? 3. Should I run reindex after I create a subscription / approve a content or the delta index in the channel deals with that since I have those two controllers joined in the channel index?

Answer 1

Your index looks fine, but if you're using deltas (and I think that's the wisest approach here, to have the data up-to-date), then you want to fire deltas for the related channels when a subscription or content is created/edited/deleted. This is covered in the documentation (see the "Deltas and Associations" section), but you'd be looking at something like this in both Subscription and Content:

after_save :set_channel_delta_flag
after_destroy :set_channel_delta_flag

# ...

private

def set_channel_delta_flag
  channel.update_attributes :delta => true
end

Given you're using Delayed Job, I'd recommend investigating ts-delayed-delta to ensure delta updates are happening out of your normal HTTP request flow. And I highly recommend not running a full index after every change - that has the potential of getting quite slow quite quickly (and adding to the server load unnecessarily).