ActiveRecord Subquery Inner Join

Question

I am trying to convert a "raw" PostGIS SQL query into a Rails ActiveRecord query. My goal is to convert two sequential ActiveRecord queries (each taking ~1ms) into a single ActiveRecord query taking (~1ms). Using the SQL below with ActiveRecord::Base.connection.execute I was able to validate the reduction in time.

Thus, my direct request is to help me to convert this query into an ActiveRecord query (and the best way to execute it).

SELECT COUNT(*)
FROM "users"
INNER JOIN (
  SELECT "centroid"
  FROM "zip_caches"
  WHERE "zip_caches"."postalcode" = '<postalcode>'
) AS "sub" ON ST_Intersects("users"."vendor_coverage", "sub"."centroid")
WHERE "users"."active" = 1;

NOTE that the value <postalcode> is the only variable data in this query. Obviously, there are two models here User and ZipCache. User has no direct relation to ZipCache.

The current two step ActiveRecord query looks like this.

zip = ZipCache.select(:centroid).where(postalcode: '<postalcode>').limit(1).first
User.where{st_intersects(vendor_coverage, zip.centroid)}.count

Answer 1

Disclamer: I've never used PostGIS

First in your final request, it seems like you've missed the WHERE "users"."active" = 1; part.

Here is what I'd do:

First add a active scope on user (for reusability)

scope :active, -> { User.where(active: 1) }

Then for the actual query, You can have the sub query without executing it and use it in a joins on the User model, such as:

subquery = ZipCache.select(:centroid).where(postalcode: '<postalcode>')
User.active
    .joins("INNER JOIN (#{subquery.to_sql}) sub ON ST_Intersects(users.vendor_coverage, sub.centroid)")
    .count

This allow minimal raw SQL, while keeping only one query.

In any case, check the actual sql request in your console/log by setting the logger level to debug.

Answer 2

The amazing tool scuttle.io is perfect for converting these sorts of queries:

User.select(Arel.star.count).where(User.arel_table[:active].eq(1)).joins(
  User.arel_table.join(ZipCach.arel_table).on(
    Arel::Nodes::NamedFunction.new(
      'ST_Intersects', [
        User.arel_table[:vendor_coverage], Sub.arel_table[:centroid]
      ]
    )
  ).join_sources
)