How to write an SQL NOT EXISTS query/scope in the Rails way?-CodePudding

I have a database scope to filter only latest ProxyConfig version for particular Proxy and environment.

This is the raw SQL that works very well with MySQL, PostgreSQL and Oracle:

class ProxyConfig < ApplicationRecord
  ...
  scope :current_versions, -> do
    where %(NOT EXISTS (
      SELECT 1 FROM proxy_configs pc
      WHERE proxy_configs.environment = environment
        AND proxy_configs.proxy_id = proxy_id
        AND proxy_configs.version < version
      ))
  end
  ...
end

You can find a simple test case in my baby_squeel issue.

But I find it nicer not to use SQL directly. I have spent a lot of time trying out different approaches to write it in the Rails way to no avail. I found generic Rails and baby_squeel examples but they always involved different tables.

PS The previous version used joins but it was super slow and it messed up some queries. For example #count produced an SQL syntax error. So I'm not very open on using other approaches. Rather I prefer to know how to implement this query exactly. Although I'm at least curious to see other simple solutions.

PPS About the question that direct SQL is fine. In this case, mostly yes. Maybe all RDBMS can understand this quoting. If one needs to compare text fields though that requires special functions on Oracle. On Postgres the case-insensitive LIKE is ILIKE. It can be handled automatically by Arel. In raw SQL it would require different string for the different RDBMS.

CodePudding user response：

This isn't actually a query that you can build with the ActiveRecord Query Interface alone. It can be done with a light sprinkling of Arel though:

class ProxyConfig < ApplicationRecord
  def self.current_versions
    pc = arel_table.alias("pc")
    where(
      unscoped.select(1)
         .where(pc[:environment].eq(arel_table[:environment]))
         .where(pc[:proxy_id].eq(arel_table[:proxy_id]))
         .where(pc[:version].gt(arel_table[:version]))
         .from(pc)
         .arel.exists.not
    )
  end
end

The generated SQL isn't identical but I think it should be functionally equivilent.

SELECT "proxy_configs".* FROM "proxy_configs" 
WHERE NOT (
  EXISTS (
    SELECT 1 FROM "proxy_configs" "pc" 
    WHERE "pc"."environment" = "proxy_configs"."environment" 
    AND "pc"."proxy_id" = "proxy_configs"."proxy_id" 
    AND "pc"."version" > "proxy_configs"."version"
  )
)