Rails 3 multiple model query

Question

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!

Tim Snowhite · Accepted Answer · 2011-06-01 20:58:00Z

2

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.

edited Jun 1, 2011 at 20:58

answered May 30, 2011 at 17:04

Tim Snowhite

3,7862 gold badges25 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

gifkins Over a year ago

Thanks Tim for the reply, sample code and your time. That's exactly what I am looking for. After I tried, just found out that seems I need to add after joins 'INNER JOIN' to make the query work. like: LocationItem.joins("INNER JOIN (#{Item.search(keyword).to_sql}) as matching_items on matching_items.id = location_items.item_id")

Tim Snowhite Over a year ago

Oh, thank you, I had forgotten that part. The answer's now updated. Glad to help.

Collectives™ on Stack Overflow

Rails 3 multiple model query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related