CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-3activerecordjoinmodel
gifkins
gifkins

Reputation: 73

Rails 3 multiple model query

I have models below:

class Location < ActiveRecord::Base
    has_many :location_items
    has_many :items, :through=>:location_items
end

class Item < ActiveRecord::Base
    has_many :location_items
    has_many :locations, :through=>:location_items
end

class LocationItem < ActiveRecord::Base
    belongs_to :item
    belongs_to :location
end

Also I have a full text search (from gem) enabled for item model, I can do Item.search('keyword') -- 'search' is a scope provided by the gem to get all items with name or description matching keyword, the result item is added a 'rank' attribute for relevance of the match

I also have a Geo search (from gem) enabled for location model, I can do Location.near('Toronto. ON', 100) --- 'near' is a scope provided by the gem to get all locations within 100 km from Toronto, the result location is added a 'distance' attribute for distance from the given location - Toronto in this example

So now what I am trying to do is to get a list of location_item objects that have location match certain given location and item match given keyword. For example, search for location_item objects matching 'keyword' and within 100km of Toronto.

How can I achieve this with one query? and can also have access to distance and rank attributes through associated item and location inside location_item object.

I can't seem to chain the scope for they only work on Item and Location, not LocationItem,

For example, the expression below won't work

LocationItem.joins(:items, locations).search('keyword').near('Toronto, ON', 100)

Hope my description of what I am trying to do makes sense. Do you have any idea? Thank you very much!

Upvotes: 4

Views: 1463

Answers (1)

Tim Snowhite
Tim Snowhite

Reputation: 3776

Basically, you won't be able to do everything that you want to in one query AND retain the normal LocationItem#location and LocationItem#item interface that you're looking for. I'm going to assume that you're using Postgres's full-text search, because the question doesn't make sense* if you're using Solr or Sphinx**.

If you want one query, but don't mind giving up the belongs_to interface of your returned elements: ActiveRecord::Base automatically assigns the attributes from whatever the SELECT portion of the query was if it is provided, like this: Location.select('id+1 as more_id').first.more_id #=> 2 so you can use that to your advantage, and create attributes that have the appropriate parts of location and item and item_rank and location_dist.

class LocationItem < ActiveRecord::Base

  #This presumes that your geo and search gems actually return scopes that 
  # properly respond to to_sql (which they should if you're using rails 3).
  def self.local_matching_items(text, dist=100)
    LocationItem
      .joins("INNER JOIN #{Item.search(text).to_sql} as matching_items 
                                          on matching_items.id 
                                          = location_items.item_id")
      .joins("INNER JOIN #{Location.near(dist).to_sql} as nearby_locations
                                            on nearby_locations.id 
                                            = location_items.location_id")
      .select("location_items.id, nearby_locations.distance, matching_items.rank,
               nearby_locations.other_field_you_might_want as
                location_other_field_you_might_want,
               matching_items.name as item_name, etc")
      #returns LocationItems with #id, #distance, #rank,
              # location_other_field_you_might_want, #item_name, etc
              #It might be most helpful to distinguish between these
              # and normal location_item's by storing them in variables with a 
              # different naming convention like nearby_item_matches.
  end
end

*Because then you'd be doing two queries, one for matching the keywords via your search provider, one for getting your records from the db.

** If you're using ThinkingSphinx, that has support for searching by geographical distance already, but you have to define your index differently and call LocationItem#search differently.

Upvotes: 2

Related Questions