Arel subselects with ActiveRecord?

I'm using a slightly-modified version of the geocoded gem which returns this query when I call near on my model (calling Deal.near(southwest), where southwest is an array of geo coordinates):

SELECT
  deals.*,
  3958.755864232 * 2 * ASIN(SQRT(POWER(SIN((37.772476604436974 - addresses.lat) * PI() / 180 / 2), 2) + COS(37.772476604436974 * PI() / 180) * COS(addresses.lat * PI() / 180) * POWER(SIN((-122.42336332798004 - addresses.lng) * PI() / 180 / 2), 2) )) AS distance,
  CAST(DEGREES(ATAN2( RADIANS(addresses.lng - -122.42336332798004), RADIANS(addresses.lat - 37.772476604436974))) + 360 AS decimal) % 360 AS bearing
  FROM "deals"
  INNER JOIN "companies" ON "companies"."id" = "deals"."company_id"
  INNER JOIN "addresses" ON "addresses"."addressable_id" = "companies"."id" AND "addresses"."addressable_type" = 'Company'
  WHERE (
    addresses.lat BETWEEN 37.483013038215276 AND 38.06194017065867
    AND addresses.lng BETWEEN -122.78956461309022 AND -122.05716204286986
  )
  GROUP BY
    deals.id,
    deals.created_at,
    deals.updated_at,
    deals.active,
    deals.company_id,
    deals.title,
    deals.limitations,
    deals.redemption_count,
    addresses.lat,
    addresses.lng
  HAVING 3958.755864232 * 2 * ASIN(SQRT(POWER(SIN((37.772476604436974 - addresses.lat) * PI() / 180 / 2), 2) + COS(37.772476604436974 * PI() / 180) * COS(addresses.lat * PI() / 180) * POWER(SIN((-122.42336332798004 - addresses.lng) * PI() / 180 / 2), 2) )) <= 20
  ORDER BY 3958.755864232 * 2 * ASIN(SQRT(POWER(SIN((37.772476604436974 - addresses.lat) * PI() / 180 / 2), 2) + COS(37.772476604436974 * PI() / 180) * COS(addresses.lat * PI() / 180) * POWER(SIN((-122.42336332798004 - addresses.lng) * PI() / 180 / 2), 2) )) ASC

My issue is that this will return multiple Deal records if that Deal's company has multiple Addresses, which I don't want.

In MySQL, I could just omit address.lat, address.lng in the GROUP_BY clause and it will properly group the records, but I can't do this in PostgreSQL.

I know I could wrap the whole query above in another SELECT and GROUP_BY, like this:

SELECT
  id, created_at, updated_at, active, title, punches_to_complete, company_id, description, lat, lng, MIN(distance), bearing
  FROM ( ... ) AS t
  GROUP BY company_id

... where the ellipsis is the query from above. That (I believe) should get me the desired result in both MySQL and PostgreSQL.

The only problem is that I have no idea how to write this in ARel!

I had tried the following, a la this tip from the ARel guru, but I couldn't really make it work quite right (calling to_sql as the OP had said fixed his issue escapes the quotes, which freaks PostgreSQL out).

Can anyone help me with this???

---

UPDATE:

I've managed to get this done with an additional scope, like so:

  scope :nearest, lambda { |coords|
    subquery = "(#{Deal.near(coords).to_sql}) AS t1"
    columns = Deal.columns.map{ |c| c.name }.join(',')
    Deal.select(columns)
      .select('MIN(distance) AS distance')
      .from(subquery)
      .group(columns)
      .order('distance ASC')
  }

However, this totally breaks chainability, as now I cannot call something like current_user.deals.nearest(coords), since that tags on an additional WHERE deals.user_id = 1 to the query outside of the subselect. I tried compensating for this by moving this logic into a class method and blanking the wheres clause on the SelectManager manually, like this:

  def self.nearest(coords)
    subquery = "(#{Deal.near(coords).to_sql}) AS t1"
    columns = Deal.columns.map{ |c| c.name }.join(',')
    query = Deal.select(columns)
      .select('MIN(distance) AS distance')
      .from(subquery)
      .group(columns)
      .order('distance ASC')
    query.arel.ast.cores[0].wheres = []
    query
  end

... but that doesn't seem to work either: the additional WHERE clause is still appended:

Failure/Error: @user.deals.nearest(southwest).first.distance.to_f.round(2).should == ActiveRecord::StatementInvalid: Mysql2::Error: Unknown column 'deals.user_id' in 'where clause': SELECT id,created_at,updated_at,user_id,company_id, MIN(distance) AS distance FROM (SELECT deals.*, 3958.755864232 * 2 * ASIN(SQRT(POWER(SIN((37.772476604436974 - addresses.lat) * PI() / 180 / 2), 2) + COS(37.772476604436974 * PI() / 180) * COS(addresses.lat * PI() / 180) * POWER(SIN((-122.42336332798004 - addresses.lng) * PI() / 180 / 2), 2) )) AS distance, CAST(DEGREES(ATAN2( RADIANS(addresses.lng - -122.42336332798004), RADIANS(addresses.lat - 37.772476604436974))) + 360 AS decimal) % 360 AS bearing FROM deals INNER JOIN companies ON companies.id = deals.company_id INNER JOIN addresses ON addresses.addressable_id = companies.id AND addresses.addressable_type = 'Company' WHERE deals.user_id = 26 AND (addresses.lat BETWEEN 37.483013038215276 AND 38.06194017065867 AND addresses.lng BETWEEN -122.78956461309022 AND -122.05716204286986) GROUP BY deals.id,deals.created_at,deals.updated_at,deals.user_id,deals.company_id, addresses.lat, addresses.lng HAVING 3958.755864232 * 2 * ASIN(SQRT(POWER(SIN((37.772476604436974 - addresses.lat) * PI() / 180 / 2), 2) + COS(37.772476604436974 * PI() / 180) * COS(addresses.lat * PI() / 180) * POWER(SIN((-122.42336332798004 - addresses.lng) * PI() / 180 / 2), 2) )) <= 20 ORDER BY 3958.755864232 * 2 * ASIN(SQRT(POWER(SIN((37.772476604436974 - addresses.lat) * PI() / 180 / 2), 2) + COS(37.772476604436974 * PI() / 180) * COS(addresses.lat * PI() / 180) * POWER(SIN((-122.42336332798004 - addresses.lng) * PI() / 180 / 2), 2) )) ASC) AS t1 WHERE deals.user_id = 26 GROUP BY开发者_运维问答 id,created_at,updated_at,user_id,company_id ORDER BY distance ASC LIMIT 1

Is what I'm trying to do even possible with ARel? The additional scopes above feel really dirty to me (parsing the subquery to raw SQL? I thought ARel was supposed to make it so I never did that!)

Related question: Can ARel formulate cross-db queries for CTEs (Common Table Expressions)?