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

One of the most important tips I learnt is that while it's nice that you can chain methods in Ruby, if you're chaining in your code it indicates that you're not following the [Law Of Demeter](http://en.wikipedia.org/wiki/Law_of_Demeter). Don't start by looking at your SQL query, you should be looking at your ZipCache.select.where.limit.first and seeing how you can reduce the number of methods by moving the logic down. It's a little weird that you're approaching the query starting at ZipCache and not the User model... Am I missing something? — Christos Hrousis, Jan 20 '15 at 22:23
I would assume the piece you are "missing" is that because it is ActiveRecord chaining is required. I dare you to write an ActiveRecord query that selects and populates only one model and only one property and tell me how to do it without method chaining. Something tells me your being pedantic about LoD. It has been said many times but bears repeating [The Law of Demeter Is Not A Dot Counting Exercise](http://haacked.com/archive/2009/07/14/law-of-demeter-dot-counting.aspx/). — Ryan, Jan 21 '15 at 13:53

score 23 · Accepted Answer · edited Jan 14 '19 at 12:50

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.

You need `subquery.to_sql`. Just `subquery` does not get interpolated correctly. — chadoh, Dec 06 '17 at 03:14

score 2 · Answer 2 · answered Jan 21 '15 at 01:58

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
)

answered Jan 21 '15 at 01:58

Dan Kohn

33,811
9
84
100

2

Neat, did not know about scuttle. This is the correct answer, but I'd recommend in this case just using SQL. If you're not doing algorithmic composition, you don't gain anything by writing it in ARel. It's pretty much impenetrable. `.star.count`!? `'ST_Intersects'`!? And why are some DSL methods but `NamedFunction` isn't? No one should actually write this code. – Xavier Shay Jan 21 '15 at 03:43
... and not that performance matters here, but ARel is at minimum tens of method calls and vulnerable to API changes. You don't need to deal with this with SQL. – Xavier Shay Jan 21 '15 at 03:44

ActiveRecord Subquery Inner Join

2 Answers2