CODEWITHSUNDEEP
sqlruby-on-rails-4activerecordleft-join
Maxime ARNSTAMM
Maxime ARNSTAMM

Reputation: 5314

Left outer join and where clause with rails activerecord

I have two models : User and Rsvp. User has many Rsvps.

I want to get all Users that has no Rsvp created at a specified date.

So i wrote this :

scope :free, -> (date) { joins(:rsvps).where.not(rsvps: {created_at: date.beginning_of_day..date.end_of_day}) }

But it only works if User has at least one Rsvp but not for 'date'. It doesn't work when a User has no Rsvp at all.

I tried with a left outer join, but it doesn't work. I must miss something :

scope :free, -> (date) { joins("LEFT OUTER JOIN rsvps ON rsvps.user_id = users.id").where.not(rsvps: {created_at: date.beginning_of_day..date.end_of_day}) }

Here's my unit test :

it 'should fetch user if he has no rsvp at all' do
  # Given
  user = User.create!(email: '[email protected]', password: '12345678', password_confirmation: '12345678')

  # Then
  expect(User.free(Date.new(2014, 1, 1)).count).to eq 1
end

Upvotes: 1

Views: 1699

Answers (3)

Layne McNish
Layne McNish

Reputation: 46

This should work: scope :free, -> (date) { includes(:rsvps).where("rsvps.id IS NULL OR rsvps.created_at NOT BETWEEN ? AND ?", date.beginning_of_day, date.end_of_day).references(:rsvps) }

Hope it helps!

Upvotes: 3

Mario Pérez Alarcón
Mario Pérez Alarcón

Reputation: 3638

You almost got it right. You need to use a GROUP_BY statement so you don't get repeated rows. 

scope :free, -> (date) do 
  joins("LEFT OUTER JOIN rsvps ON rsvps.user_id = users.id")
    .where.not(rsvps: {created_at: date.beginning_of_day..date.end_of_day})
    .group("users.id") # or just .group(:id)
end

Upvotes: 1

Brozorec
Brozorec

Reputation: 1183

It's normally that joins returns Users having at least one Rsvp. That's why I suggest using includes. Try this one (probably you have to modify the condition because I'm not sure it's what you want exactly):

scope :free, -> (date) { includes(:rsvps).where('rsvps.created_at != ?', date ).references(:rsvps) }

Upvotes: 1

Related Questions