CODEWITHSUNDEEP
ruby-on-railspostgresqlactiverecordgeolocationrails-geocoder
Sebastian
Sebastian

Reputation: 1912

Ruby On Rails - Geocoder - Near with condition

I'm using GeoCoder in my application. Now I need to search for objects in my database which are close to a position OR have specific attribute set. I would like to perform this action in one database query, because the database is realy huge.

I would like to have something like 

  Spot.near([lat,long],distance).where("visited = ?",true).

The distance and the visited attribute should be combined with an OR, not with an AND.

Does anyone have an idea how to do this?

Thank you!

Upvotes: 3

Views: 1474

Answers (2)

jearlu
jearlu

Reputation: 550

I'm in the process of upgrading a Rails application from Rails 4 to Rails 7 and ran into this problem. While I have no doubt Luke's suggestion worked in earlier versions, it doesn't work in Rails 7 (I'm currently running activerecord-7.0.3.1.

In my particular case, I am using the geocoder near() method to return results that are within a 20 mile radius of the query, but I also wanted to use OR conditions to return results where the query was similar to the text values in either the name or location columns from the items table in an attempt to return relevant items that haven't been assigned latitude and longitude values.

In Rails 4, my solution was:

select("items.*").near(q, 20, select: :geo_only).tap do |near_query|
  near_query.where_values.last << sanitize_sql([" OR items.location LIKE ? OR items.name LIKE ?", "%#{q}%", "%#{q}%"])
end

In Rails/ActiveRecord 7, the where_values() method no longer exists. Searching for an alternate solution led me to this post. I wound up spending a fair amount of time perusing the latest ActiveRecord and Arel code for a solution. Here's what I came up with,

Rails 7 solution:

t = Item.arel_table
arel_geo_conditions = Item.near(q, 20).where_clause.ast # ast: Abstract Syntax Tree
Item.where(arel_geo_conditions.or(t[:location].matches("%#{q}%").or(t[:name].matches("%#{q}%"))))

Upvotes: 0

Luke
Luke

Reputation: 4925

Based off of this answer, you should be able to do something like:

near = Spot.near([lat, long], distance)
visited = Spot.where(visited: true)

near = near.where_values.reduce(:and)
visited = visited.where_values.reduce(:and)

Spot.where(near.or(visited))

Upvotes: 4

Related Questions