Jun 3, 2009

Sum and index a related column in Thinking Sphinx

It's fairly straight forward to get indexing working using the excellent Thinking Sphinx plugin. But sometimes you may want to do something a little more complicated. For example, if you have a Model foo with a has_many relationship to model Bar and you want to search on Bar how do you set it up? Or, what if you want to find out how many Bars Foo has? Enough! Let's work on an example.

Let's start and try to index a User model. That user has many bank accounts each of which has a balance:

User 1
--> Account 1 ($5)
--> Account 2 ($100)
--> Account 3 ($3)

User 2
--> Account 4 ($5)
--> Account 5 ($75)

In our example, we want to search for users by the amount of cash they have in their accounts. To do that we need to configure sphinx to first tell it that a User has many Accounts with a bank balance:

class Brigade < ActiveRecord::Base
has_many :accounts

#sphinx index
define index do
has accounts(:bank_balance), :as => :bank_balance
indexes "SUM(bank_balance)", :as => :total_bank_balance, :sortable => true

With that done, you can now do a search for users with a bank balance of $5 and it will return the 2 users with that balance. But, you can also do a search for users with a total bank balance of $80 and it will return only user #2.

No comments:

Post a Comment