Fancy groupings with Thinking Sphinx

Question

I have tables boards, users, joins, and roles. The joins table defines the association a user has to a board, as well as the role that user has on that board. I'm trying to either get all of a user's boards at once with a way to differentiate what role a user has on each board, or make three separate calls to get boards that a user is a certain role.

In the board model I have:

  define_index do
    indexes :name
    indexes description

    has created_at
    has users(:id), :as => :user
    has joins.role_id, :as => :role

    set_property :enable_star => true
    set_property :min_infix_len => 1
  end

and I've been trying queries that are similar to this:

Board.search(:with => {:user => some_user.id, :role => some_role.id})
Board.search(:with => {:user => some_user.id}, :group_function => :attr, :group_by => 'role')

The problem with the first query is that each board has users that have each possible role, so I get every board the user is associated with, rather than ones for a specific role. As for the second, I'm not sure how I'm getting only the same board returned back to me three times, but that's what's happening.

Thanks in advance for any help/tips.

Answer 1

Hi Kurt

When you run a grouped query, one matching result per group is returned. It sounds like a specific board is being returned for each group, because that board has three roles.

I don't think it's actually possible to do what you want to do in a single query (ie: limit role values to a specific user) - because Sphinx doesn't have any idea of the relationships.

Sorry I can't be of more help.