Reference parent columns from subquery

[daliborfilus]

Issue

Is there a way to reference tables from outer scopes inside subquery?

Reproduction

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

gemfile true do
  source 'https://rubygems.org'
  gem 'activerecord', '~> 5.0.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 :accounts, force: true do |t|
    t.string :name
  end
  create_table :songs, force: true do |t|
    t.string :name
  end
  create_table :account_songs, force: true do |t|
    t.integer :account_id
    t.integer :song_id
  end
  create_table :playlists, force: true do |t|
    t.integer :account_id
    t.integer :song_id
  end
end

class Song < ActiveRecord::Base
  has_many :account_songs, :dependent => :destroy, :inverse_of => :song
  has_many :accounts, :through => :account_songs
  has_many :playlists, :dependent => :nullify
end

class Account < ActiveRecord::Base
  has_many :account_songs, :dependent => :destroy, :inverse_of => :account
  has_many :songs, :through => :account_songs
  has_many :playlists, :dependent => :nullify
end

class AccountSong < ActiveRecord::Base
  belongs_to :account, :inverse_of => :account_songs
  belongs_to :song, :inverse_of => :account_songs
end

class Playlist < ActiveRecord::Base
  belongs_to :account
  belongs_to :song
end

class BabySqueelTest < Minitest::Spec
  it 'works' do
    # global scope
    scope = AccountSong.all
    # filters defined down the road, in filtering classes, in this case: was this song already played:
    # `account_songs` here is coming from the global scope
    subquery = Playlist.where.has { |t| (t.account_id = t.account_songs.account_id) & (t.song_id == t.account_songs.song_id) }
    scope = scope.where.has { exists(subquery) }

    # result after applying all filters
    scope.to_sql.must_equal %{
      SELECT "account_songs".* FROM "account_songs"
      WHERE EXISTS (
        SELECT "playlists".* FROM "playlists"
        WHERE "playlists"."account_id" = "account_songs"."account_id"
          AND "playlists"."song_id" = "account_songs"."song_id"
      )
    }.squish
  end
end

Here, account_songs is undefined when creating subquery.

EDIT: Fixed singular table names to be plural again.

[rzane]

I think this is what you want:

AccountSong.where.has { |s|
  s.exists Playlist.where.has { |p|
    (s.account_id == p.account_id) & (s.song_id == p.song_id)
  }
}

Which, generates the following SQL:

SELECT "account_songs".*
FROM "account_songs"
WHERE (EXISTS (
  SELECT "playlists".*
  FROM "playlists"
  WHERE ("account_songs"."account_id" = "playlists"."account_id"
  AND "account_songs"."song_id" = "playlists"."song_id"))
)

[daliborfilus]

Well, that looks even better and is more readable. It didn't even cross my mind that you can do it like this. Thanks a lot!

[rzane]

You're welcome! Thanks for taking the time to detail the problem so clearly.