ActiveRecord::UnknownAttributeReference: Query method called with non-attribute argument(s)

I'm trying to use spatial distance in my Rails app, but I keep running into the "ActiveRecord::UnknownAttributeReference: Query method called with non-attribute argument(s)" error when I try to use the order method.

Here's my code:

def nearby_locations
  third_party_location_query = ThirdPartyLocation.where(id: id).select('geom').to_sql

  third_party.organisation.locations.active.
    select("locations.*, ROUND(ST_DistanceSphere(locations.geom, (#{third_party_location_query}))::numeric, 0)::integer distance").
    order("locations.geom <-> (#{third_party_location_query})").
    limit(10).as_json(methods: [:distance])
end

I understand that the error is caused by passing a non-attribute value to the order method, but I'm not sure how to avoid it in this case. How can I use spatial distance in my query without running into this error?

Solution

As of Rails 6.0 you cannot use a non-column reference in an order statement without passing an Arel object.

In your case options include:

 order(Arel.sql("locations.geom <-> (#{third_party_location_query})"))
# Or 
 third_party_location_query = ThirdPartyLocation.select(:geom)
    .arel.where(ThirdPartyLocation.arel_table[:id].eq(id))
 order(
  Arel::Nodes::InfixOperation.new("<->", # operator
    Locations.arel_table[:geom], # left side
    third_party_location_query)  # right side (will add parens as a subquery)  
  ))

We can even convert this part to arel but it wouldn't be pretty

# ROUND(ST_DistanceSphere(locations.geom, (#{third_party_location_query}))::numeric, 0)::integer distance
function = Arel::Nodes::NamedFunction
operation = Arel::Nodes::InfixOperation
operation.new('::',  
  function.new('ROUND',
    [operation.new('::',
      function.new('ST_DistanceSphere',[
        Location.arel_table[:geom],
        ThirdPartyLocation.select(:geom).arel.where(ThirdPartyLocation.arel_table[:id].eq(id))
      ]),
      Arel.sql('numeric')),
    0]),
  Arel.sql('integer')).as('distance')

For others that stumble across this post:

Please know the Arel#sql will not perform escaping.

If third_party_location_query needs to be escaped, because it comes from a third party and could be dangerous, using other techniques can and should be used to sanitize this data:

For instance if the parens are not needed then:

 Arel::Nodes::InfixOperation.new("<->",
    Locations.arel_table[:geom],
    Arel::Nodes.build_quoted(third_party_location_query))

should work.

If the parens are needed and the argument is singular or arguments are comma separated. Then

third_party_location_query = "hello" 
Arel::Nodes::Grouping.new([Arel::Nodes.build_quoted(third_party_location_query)]).to_sql
#=> (N'hello')
# Or 
third_party_location_query = [1,2,3]
Arel::Nodes::Grouping.new(third_party_location_query ).to_sql
#=> (1,2,3)

There are many other ways to handle the needed escaping depending on implementation.