CODEWITHSUNDEEP
sqlruby-on-railsruby-on-rails-3activerecord
Kyle Patterson
Kyle Patterson

Reputation: 185

RoR table join with "where" needed on both tables

I'm having trouble wrapping my head around joining tables. I have a one_to_many relationship between Locations and Listings. Locations have a longitude and latitude, as well as it's primary key: zipcode. Listings have a title and zipcode.

Listing.where("title LIKE ?", "%#{params[:search2]}%")
Location.where(:lat => @min_lat..@max_lat, :lon => @min_lon..@max_lon)

I'd basically Like to combine these two statements so that I can get all Listings within a given range of a zipcode, which is determnied by using a range of longitudes and latitudes. I can't figure out how to do this in Ruby on Rails in a fashion where I don't have to do separate SQL finds and loop through the data in my code to find the correct data.

Edit Updated code:

@results=Location.joins(:listings).where(:listings => ["title LIKE ?", "%#{params[:search2]}%"], :locations => {:zipcode => params[:searchZip]})

SQL output:SELECT "locations".* FROM "locations" INNER JOIN "listings" ON "listings"."location_id" = "locations"."zipcode" WHERE ("locations"."listings" IN ('title LIKE ?', '%fish%')) AND ("locations"."zipcode" = 44012)

I don't know why it is doing "locations"."listings" (which gives an error), or where the IN is coming from.

Upvotes: 2

Views: 151

Answers (3)

Brett Bender
Brett Bender

Reputation: 19738

I suggest using ARel as opposed to writing your own SQL:

Listing.joins(:location).
  where(:listings => {:lat => @min_lat..@max_lat, :lon => @min_lon..@max_lon},
        :location => ["title LIKE ?", "%#{params[:search2]}%"])

Upvotes: 2

Jeremy Weathers
Jeremy Weathers

Reputation: 2554

If I am understanding correctly that Location has_many :listings, then this query will return all of the listings in the location range with matching titles:

Listing.joins(:location).where(
  'listings.title LIKE ? AND 
   locations.lat BETWEEN ? AND ? AND 
   locations.lon BETWEEN ? AND ?', 
"%#{params[:search2]}%", @min_lat, @max_lat, @min_lon, @max_lon)

Upvotes: 1

eveevans
eveevans

Reputation: 4460

   Location.joins(:listings).where(:listings => {:lat => @min_lat..@max_lat, :lon => @min_lon..@max_lon} )

Upvotes: 0

Related Questions