0

I have built a Rails app that has data about the US Congress. I have a Legislator model and a Bill model:

class Legislator < ActiveRecord::Base
  has_many :bills
end

class Bill < ActiveRecord::Base
  belongs_to :legislator
end

So bills that a legislator has sponsored are tied to that legislator. The legislator also has a "party" attribute that is either "R", "D", or "I"

I want to get a list of all bills that were sponsored by all legislators from a particular party, e.g., all bills that were sponsored by Democrat. What would this query look like?

user3809888
  • 377
  • 1
  • 6
  • 23

2 Answers2

2
Bill.joins(:legislator).where(legislators: {party: "I"})

But I would advise you to write scopes on Legislator and Bill

# class Legislator
scope :by_party, ->(party) do
  where(party: party)
end

# class Bill
scope :sponsored_by_party, ->(party) do
  joins(:legislator).merge(Legislator.by_party(party))
end

Then you can write

Bill.sponsored_by_party("I")
messanjah
  • 8,977
  • 4
  • 27
  • 40
  • what's the query generated when you do that joins + merge ? – Mohammad AbuShady Feb 26 '15 at 06:27
  • Since I'm not at a console, I have to fake it. It should be `SELECT bills.* FROM bills INNER JOIN legislators ON legislators.id = bills.legislator_id WHERE legislators.party = 'I';` – messanjah Feb 26 '15 at 06:34
  • so active record supports a `.merge` method on the relation level correct? cause i thought it would be some array merging, i'll need to read about this, thanks +1 – Mohammad AbuShady Feb 26 '15 at 06:40
  • 1
    http://stackoverflow.com/q/9540801/597358 and http://api.rubyonrails.org/classes/ActiveRecord/SpawnMethods.html#method-i-merge – messanjah Feb 26 '15 at 06:45
1

Join and put the query as a hash

Bill.joins(:legislator).where(legislators: {party: 'D'})
Mohammad AbuShady
  • 40,884
  • 11
  • 78
  • 89
  • This is giving me an error ` from bin/rails:12:in `
    'irb(main):025:0> Bill.joins(:legislator).where(legislator: {party: 'D'}) PG::UndefinedTable: ERROR: missing FROM-clause entry for table "legislator" LINE 1: ...lators"."id" = "bills"."legislator_id" WHERE "legislato...` ^
     – user3809888 Feb 26 '15 at 06:23
  • ok I think because i need to say `legislators: {party: 'D'}` instead of `legislator: {party: 'D'}` ( the missing `s` ) – Mohammad AbuShady Feb 26 '15 at 06:25