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?

Side note, use [scope](http://api.rubyonrails.org/classes/ActiveRecord/NamedScope/ClassMethods.html#method-i-scope) instead of those paid/unpaid functions: `scope :paid where("payment_id IS NOT NULL")` You can also refer to [rail 3 where condition using NOT NULL](http://stackoverflow.com/questions/4252349/rail-3-where-condition-using-not-null) — Christopher Manning, Apr 27 '11 at 19:43

score 4 · Accepted Answer · answered Apr 27 '11 at 18:23

4

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

answered Apr 27 '11 at 18:23

Chris Cherry

28,118
6
68
71

1

+1. I wish I could give more! Your answer was the final hint that helped me answer my own question. Thanks! http://stackoverflow.com/questions/15470782/how-can-i-create-a-scope-in-a-model-that-returns-only-those-objects-with-an-asso – djoll Mar 18 '13 at 13:16

score 1 · Answer 2 · answered Apr 27 '11 at 21:27

You could write:

class Player

  scope :unpaid, includes(:registrations).where("player_registrations.payment_id is null")
  scope :paid, includes(:registrations).where("player_registrations.payment_id is not null")

end

and now Player.unpaid will return all players with unpaid registrations.

If you write something like:

class Registration

  scope :paid, where("payment_id IS NOT NULL")
  scope :unpaid, where("payment_id is null")

end

then it is possible to write player1.registrations.unpaid, but that is not what you want. If I understand correctly, you want to find all players with unpaid registrations, and that is only possible using the scope on the Player class.

Hope this helps.

Arel query through an association

2 Answers2