Ordering results by field in a related table (has_many relationship)

[mikej]

Client
  has_many :client_categories
  has_many :categories, :through => :client_categories

ClientCategory
  belongs_to :category
  belongs_to :client

Category
  has_many :client_categories
  has_many :clients, :through => :client_categories

Each client can order their categories individually, so client_categories includes a position column. Each ClientCategory is further subdivided and may be in a group (group_id).

I am wanting to search categories for a given client and return Categories - ordered first by client_categories.group, then client_categories.position then at the end of the search include categories not assigned to that client.

I can do this by indexing client_categories and searching the ClientCategory model. This is very slow though.

Can I do this through the Category Model?

e.g.

define_index do
    indexes name, :sortable => true
    has client_categories.client_id, :as => :category_client_id, :sortable => true
    has client_categories.group_id, :as => :category_group_id, :sortable => true
end

Ignoring trying to include clients not assigned to categories at the moment:

@categories = Category.search nil,:with=>{:category_client_id=>1},:order => :category_group_id

This does not work.

Also not sure how to include categories not assigned to the client.

Any thoughts much appreciated, big thanks,

Mike

[Pat Allan]

Hi Mike

I think the first thing to be aware of is that you're trying to sort by a set of values - each category can have multiple client categories - so handling that from either a Sphinx or SQL perspective isn't a simple matter. What do you expect to happen if a category has three different client category positions? Which one should be considered the value to sort by?

--
Pat

> --
> You received this message because you are subscribed to the Google Groups "Thinking Sphinx" group.
> To view this discussion on the web visit https://groups.google.com/d/msg/thinking-sphinx/-/S44OFcKXDIAJ.
> To post to this group, send email to thinkin...@googlegroups.com.
> To unsubscribe from this group, send email to thinking-sphi...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/thinking-sphinx?hl=en.

[mikej]

Pat,

thanks for the response.  In practice, each category will only be assigned to a client once. This is tightly validated.  Hope that helps,

Mike

[Pat Allan]

Hi Mike

Can a category be assigned to different clients?

--
Pat

> --
> You received this message because you are subscribed to the Google Groups "Thinking Sphinx" group.

> To view this discussion on the web visit https://groups.google.com/d/msg/thinking-sphinx/-/3vcyf4_IzZwJ.

[mikej]

Yes, but only once to each client and I am only interested in listing categories for one client at once.  So, for each client I would like a list of categories ordered as above, leaving unassigned categories to the end (although I can live without this if need be).

[Pat Allan]

Right - Sphinx has no concept of key/value pairs, so it doesn't know which client category values are relevant for a given search query, it just has all the values associated to the category document.

I think running the search from the ClientCategory model will be much better, as that gives you single values of group and position, you can pull in the category name easily enough, and you can add an attribute for the client_id and filter by that. If it's running particularly slow, do you have database indices set up on your foreign keys? And how slow is slow, and how many records?

Cheers

--
Pat

On 19/06/2012, at 12:43 PM, mikej wrote:

> Yes, but only once to each client and I am only interested in listing categories for one client at once. So, for each client I would like a list of categories ordered as above, leaving unassigned categories to the end (although I can live without this if need be).
>

> --
> You received this message because you are subscribed to the Google Groups "Thinking Sphinx" group.

> To view this discussion on the web visit https://groups.google.com/d/msg/thinking-sphinx/-/syCtsNsVk6EJ.

[mikej]

Thanks very much Pat.  I will work on this approach.  The problem comes in that clients frequently reorder categories, which means looping through all the client_categories and updating them with other info.  This was too slow using delta indexing.  I could do this as a background job or perhaps disable thinking sphinx and reindex after all the changes have been made.  I guess updating in the background is safest.  There are around 200 client_categories per client.  25000 client_categories altogether at the minute.

Sincere thanks, greetings from London town, hope to run into you at the next railscamp.

Mike

[Pat Allan]

Good to hear Mike. You may want to investigate doing a bulk update in pure SQL, and then invoking delta indexing on the model once (as opposed to many times on each relevant index):

ClientCategory.define_indexes
ClientCategory.index_delta

Not sure I'll make the next Rails Camp UK (unless it's really soon), but am hoping to swing through London for the August LRUG, should all go to plan. Perhaps I'll catch you there :)

Cheers

--
Pat

> --
> You received this message because you are subscribed to the Google Groups "Thinking Sphinx" group.

> To view this discussion on the web visit https://groups.google.com/d/msg/thinking-sphinx/-/HcP2jfXAqOAJ.