Search/filter by attribute of associated objects

Roger Kind Kristiansen's Avatar

Roger Kind Kristiansen

18 Apr, 2018 07:26 PM

Hi,

Not the clearest of subjects, I guess, so I'll try to explain better with a simple example. Let's say I've got the following models:

class Person
  has_many :organizations, through: :person_organizations
end

class PersonOrganization
  belongs_to :person
  belongs_to :organization
end

class Organization
  has_many :people, through: :person_organizations
end

And I have defined an index like the following:

ThinkingSphinx::Index.define :person, with: :active_record do
  indexes [firstname, lastname], as: :name, sortable: true
  indexes organizations.name, as: :organization_name, sortable: true
end

The issue at hand is that I, for some of my people searches, want to only fetch the people belonging to organizations that have some particular attribute(s). Let's, for the sake of this example say that I only want the organizations that have the attribute special = true. I haven't figures out a way to do this with the index above, but think that I in stead need to define a second index, equal to the first, but with a where clause, like this:

ThinkingSphinx::Index.define :person, name: :person_with_special_org, with: :active_record do
  where 'organizations.special = TRUE'

  indexes [firstname, lastname], as: :name, sortable: true
  indexes organizations.name, as: :organization_name, sortable: true
end

My question is then: Is this the best way of going about this, or is there some clever way of achieving this using a single index definition? If so, would you happen to have any recommendation on how to avoid declaring all the same fields and attributes twice, one for each index?

  1. Support Staff 1 Posted by Pat Allan on 19 Apr, 2018 01:49 AM

    Pat Allan's Avatar

    Hi Roger

    As you've currently described things (if my understanding is correct), I think it's possible to have everything in a single index:

    ThinkingSphinx::Index.define :person, with: :active_record do
      indexes [firstname, lastname], as: :name, sortable: true
      indexes organizations.name, as: :organization_name, sortable: true
    
      # for PostgreSQL:
      has "bool_or(organizations.special)", :as => :special, :type => :boolean
      # or MySQL:
      has "MAX(organizations.special)", :as => :special, :type => :boolean
    end
    

    And then to search on people who belong to at least one special organisation:

    Person.search "foo", :with => {:special => true}
    

    Does this fit what you need?

  2. 2 Posted by Roger Kind Kris... on 19 Apr, 2018 07:13 AM

    Roger Kind Kristiansen's Avatar

    Thanks Pat,

    If one person is connected to two organizations, one with `special=true`
    and one with `special=false`, and my search string actually only matches
    the name of the latter organization, I don't want the search to return this
    person.

    Won't your example return the person as long as one or more of that persons
    organizations have `special=true` set?

    (I did actually try something like your example before I asked the original
    question, but just couldn't get my expected result. Perhaps I'm just doing
    it wrong or there is something with my actual index setup makes it behave
    differently. :/)

  3. 3 Posted by Roger Kind Kris... on 19 Apr, 2018 07:16 AM

    Roger Kind Kristiansen's Avatar

    To try to be clear – I _also_ need to do searches where it matches any of
    the persons organizations. The one I'm trying to figure our above is just
    for an additional special case.

  4. Support Staff 4 Posted by Pat Allan on 19 Apr, 2018 03:01 PM

    Pat Allan's Avatar

    Ah, yes you're right, what I've suggested will still match names from non-special organisations. Needing that behaviour does change things…

    I feel there are two options here. The first is, as you initially suggested, have two indices, and your where clause was spot on. Sharing the rest of the index details across definitions is tricky… you could have a helper class that applies fields and attributes?

    class IndexDefiner < BasicObject
      def self.call(source)
        new(source).call
      end
    
      def initialize(source)
        @source = source
      end
    
      def call
        # Here goes the common index contents
        indexes [firstname, lastname], as: :name, sortable: true
        indexes organizations.name, as: :organization_name, sortable: true
      end
    
      private
    
      def method_missing(name, *arguments, &block)
        @source.__send__ name, *arguments, &block
      end
    end
    
    # and in the index definitions
    
    ThinkingSphinx::Index.define :person, with: :active_record do
      IndexDefiner.call self
    end
    
    ThinkingSphinx::Index.define :person, name: :person_with_special_org, with: :active_record do
      where 'organizations.special = TRUE'
    
      IndexDefiner.call self
    end
    

    And then use the :indices => ["person_with_special_org_core"] option in your searches.

    A different way is to instead define an index on the joining class, which will only have one organisation rather than many:

    ThinkingSphinx::Index.define :person_organization, :with => :active_record do
      indexes [person.firstname, person.lastname], as: :name, sortable: true
      indexes organization.name, as: :organization_name, sortable: true
    
      has organization.special, :as => :special
    end
    

    And then you can search on that model, loading each person through that:

    PersonOrganization.search "foo", :with => {:special => true}, :sql => {:include => :person}
    

    As for which of these to use? I guess it depends on whether you want the duplicate indices (even with the slightly cleaner approach), or a single model to search on - and whether the full situation you're dealing with works in either setup?

Reply to this discussion

Internal reply

Formatting help / Preview (switch to plain text) No formatting (switch to Markdown)

Attaching KB article:

»

Attached Files

You can attach files up to 10MB

If you don't have an account yet, we need to confirm you're human and not a machine trying to post spam.

Keyboard shortcuts

Generic

? Show this help
ESC Blurs the current field

Comment Form

r Focus the comment reply box
^ + ↩ Submit the comment

You can use Command ⌘ instead of Control ^ on Mac