subqueries comparing with same table impossible

[akostadinov]

Issue

When doing a sub-query with current table, then it is not possible to compare fields from inner and outer query. This is very useful when trying to create a scope for maximum of group type of queries.

Reproduction

require 'bundler/inline'
require 'minitest/spec'
require 'minitest/autorun'

gemfile true do
  source 'https://rubygems.org'
  gem 'activerecord', '~> 5.2.0' # which Active Record version?
  gem 'sqlite3'
  gem 'baby_squeel', github: 'rzane/baby_squeel'
end

ActiveRecord::Base.establish_connection(adapter: 'sqlite3', database: ':memory:')

ActiveRecord::Schema.define do
  create_table :proxy_configs, force: true do |t|
    t.string :environment
    t.bigint :proxy_id
    t.bigint :version
    t.string :configuration
  end
end

class ProxyConfig < ActiveRecord::Base
  scope :only_max_version, -> do
    where.has do |out|
      out.not_exists(
        unscoped.where.has { |inner| (out.environment == inner.environment) & (out.proxy_id == inner.proxy_id) & (out.version < inner.version) }
      )
    end
  end
end

class BabySqueelTest < Minitest::Spec
  it 'works' do
    ProxyConfig.create!(environment: "production", proxy_id: 5, version: 0, configuration: "some configuration")
    ProxyConfig.create!(environment: "production", proxy_id: 5, version: 1, configuration: "some other configuration")

    ProxyConfig.only_max_version.count.must_equal 1
  end
end

[akostadinov]

I also played with table = BabySqueel[:proxy_configs].alias(:versions), create_table_alias and from to no avail. So I'm curious about non baby_squeel solutions too. The only thing that I made working was direct SQL

    where('NOT EXISTS (SELECT 1 FROM proxy_configs AS pc where proxy_configs.environment = environment AND proxy_configs.proxy_id = proxy_id AND proxy_configs.version < version)')