Search code examples
rubypostgresqlruby-on-rails-4postgisrgeo

RGeo PostGIS multi-table query

The following query runs fine as raw sql, but because it hits 2 models, I'm not sure how construct it in active record...

sql =  "SELECT spots.*
              FROM spots, areas 
              WHERE areas.area = '#{@area.area}'
              AND shape && lonlat
              AND ST_Contains(shape,lonlat);"

How could I go about constructing this? It's not a join so...

Thanks!

Solution

  • You can generate this same SQL this way:

    Spot.from("spots, areas")
    .where("areas.area = ?",@area.area)
    .where("shape && lonlat")
    .where("ST_Contains(shape,lonlat)")

    But this way you aren't using what ActiveRecord have best like eager loads and relationships. To get the same result you could do this:

    In models:

    Spot < ActiveRecord::Base
    belongs_to :area
end

Area < ActiveRecord::Base
end

    And the query:

    Spot.joins(:area).where(areas: {area: @area.area})