Arel query through an association

Question

Here are my models:

class Player < ActiveRecord::Base
  has_many :registrations, :class_name => "PlayerRegistration"
end

class PlayerRegistration < ActiveRecord::Base
   belongs_to :player

   belongs_to :payment

   def self.paid
     where("payment_id IS NOT NULL")
   end

   def self.unpaid
     where(:payment_id => nil)
   end
end

class Payment < ActiveRecord::Base
  has_many :registrations, :class_name => "PlayerRegistration"
end

What I would like to do is create a scope on Player that returns all players who have an unpaid registration, so something like:

BAD PSUEDO CODE AHEAD

class Player < ActiveRecord::Base

  def self.paid
    registrations.unpaid  #But actually return the players
  end
end

Is this possible? It must be, considering that all the data I need is out there, I just have no idea how to write that sort of an AREL query. Can anybody help?

FWIW I've tried this:

class Player < ActiveRecord::Base
  def self.paid
    includes(:registrations).where("registrations.payment_id IS NOT NULL")
  end
end

but that provides me with the following error:

ActiveRecord::StatementInvalid:
   PGError: ERROR:  missing FROM-clause entry for table "registrations"
   LINE 1: ...registrations"."player_id" = "players"."id" WHERE "registrat...
                                                                ^
   : SELECT "players"."id" AS t0_r0, "players"."first_name" AS t0_r1, "players"."last_name" AS t0_r2, "players"."birthday" AS t0_r3, "players"."gender" AS t0_r4, "players"."created_at" AS t0_r5, "players"."updated_at" AS t0_r6, "players"."user_id" AS t0_r7, "player_registrations"."id" AS t1_r0, "player_registrations"."player_id" AS t1_r1, "player_registrations"."grade_id" AS t1_r2, "player_registrations"."shirt_size_id" AS t1_r3, "player_registrations"."division_id" AS t1_r4, "player_registrations"."coach_request" AS t1_r5, "player_registrations"."buddy_request" AS t1_r6, "player_registrations"."created_at" AS t1_r7, "player_registrations"."updated_at" AS t1_r8, "player_registrations"."league_id" AS t1_r9, "player_registrations"."season_id" AS t1_r10, "player_registrations"."school_id" AS t1_r11, "player_registrations"."payment_id" AS t1_r12 FROM "players" LEFT OUTER JOIN "player_registrations" ON "player_registrations"."player_id" = "players"."id" WHERE "registrations"."payment_id" IS NULL

Could anyone show me a better way? Isn't there a way I can use the paid scope that I already have in PlayerRegistrations?

Answer 1

You need the real table name in the where() filter:

class Player < ActiveRecord::Base
  def self.paid
    includes(:registrations).where("player_registrations.payment_id IS NOT NULL")
  end
end