ActiveRecord scoping by a custom method

Question

So let's say I have a class called Property, and I'm using the PostGIS adapter to keep track of spatial relations between physical properties (like a house, office, etc.) I'm also using the RGeo gem to keep track of individual properties' locations.

Thus, if I call:

p = Property.first
p.location # => #<RGeo::Geographic::ProjectedPointImpl:0x3f9303eecc3c "POINT (-111.67226 33.231371)">
p.location.latitude # => 33.231371

I also have a custom method in the Property model:

class Property < ActiveRecord::Base
  ...
  def lat
    self[:location].latitude
  end
end

p.lat # => 33.231371

I want to create a scope that is like "latitude less than x" or "longitude greater than x".

However, if I type into the console

irb> Property.where("lat > ?", 30)
# => ActiveRecord::StatementInvalid: PG::Error: ERROR:  column "lat" does not exist

And if I try a scope that looks like:

class Property < ActiveRecord::Base
  ...
  scope :lat_lt, lambda {|val| where(['properties.location.latitude <= ?', val]) }
end

Then I get

irb> Property.lat_lt "30"
# => Property Load (1.3ms)  SELECT "properties".* FROM "properties" WHERE (properties.location.latitude <= '30')
# => ActiveRecord::StatementInvalid: PG::Error: ERROR:  schema "properties" does not exist

So basically, there's no column for "lat", though I can call .lat on a Property instance and get the information I need. Now I just need to be able to create a way to scope by that return value, even though there isn't a column.

I'd love more information on how this works; is ActiveRecord querying operating at a level that is unaware of methods that've been defined on the model, ones that don't exist on their own in the database?

Any and all help would be much appreciated.

Answer 1

When you are using where method (or any other method from AR querying interface: http://guides.rubyonrails.org/active_record_querying.html) you are building an sql query which is executed when needed. Since your database has no access to your code, it has no idea about methods you have defined - it only has access to what is being stored in a db.

If you want to get results based on your custom method, you can 1. get a list of all properties and then filter them using select method (http://www.ruby-doc.org/core-2.1.0/Array.html#method-i-select) - this however might hit quite heavy performance and resource problems. 2. (recommended) figure out how to get what you want using pure sql.

If you could show how location is stored inside db, I'll try to help further.