using scopes with parameters and has_and_belongs_to_many associations with ActiveRecord (Ruby)

Question

In my Sinatra app my model defines a HABTM relationship between Users and Notifications. I am trying to define a couple of scopes, one for all Notifications associated with no Users called unread and one that returns all Notifications that are 'unread' by a particular User.

class Notification < ActiveRecord::Base
  has_and_belongs_to_many :users

  scope :unread, ->{
    Notification.joins("LEFT JOIN notifications_users ON notifications.id = notifications_users.notification_id").
                  where("notifications_users.user_id IS NULL").uniq
  }

  scope :unread_by, ->(u){
    Notification.joins("LEFT JOIN notifications_users ON notifications.id = notifications_users.notification_id").
                  where("notifications_users.user_id <> ?", u.id).uniq
  }

The unread scope works fine but the unread_by scope is not giving me the results I expect.

it "should know which notifications have not yet been read by anyone, or by a particular user" do
  n1 = Notification.create!(title: 'test 1', text: 'this is some longer text about the notification')
  n2 = Notification.create!(title: 'test 2', text: 'this is also some longer text about the notification')
  Notification.unread.must_include(n1)
  Notification.unread.must_include(n2)
  @user1.read(n1)
  Notification.unread.wont_include(n1)
  Notification.unread.must_include(n2)
  Notification.unread_by(@user1).wont_include(n1)
  Notification.unread_by(@user1).must_include(n2) # => fails
  Notification.unread_by(@user2).must_include(n1)
  Notification.unread_by(@user2).must_include(n2) # => fails
end

I suspect my query logic is flawed but I've been staring at this for too long and I'm just not seeing it. What am I missing?

What version on ActiveRecord are you using? as you've used the new style `scope` method with the old style hash conditions — maniacalrobot, Sep 05 '13 at 07:11

score 0 · Answer 1 · answered Sep 05 '13 at 07:38

0

For ActiveRecord 4, you should be using the new style query interface inside the scope blocks

http://guides.rubyonrails.org/active_record_querying.html

Completely untested, but the following should be a guide a to how to structure the query: (note that each method is chained to the one above it)

scope :unread, {
    joins( "LEFT JOIN notifications_users ON notifications.id = notifications_users.notification_id").
    where("notifications_users.user_id IS NULL").
    select("DISTINCT notifications.*")
}

scope :unread_by, ->(u){
    joins("LEFT JOIN notifications_users ON notifications.id = notifications_users.notification_id").
    where(["notifications_users.user_id IS ?", u.id]).
    select("DISTINCT notifications.*")
}

answered Sep 05 '13 at 07:38

maniacalrobot

2,413
2
18
20

Thanks but when I try that (just trying your syntax on the `unread` scope for now, as it's already working the way I had it) I get `syntax error, unexpected (` – Dave Sag Sep 05 '13 at 22:27
I've rewritten my first scope thusly. `scope :unread, ->{ Notification.joins("LEFT JOIN notifications_users ON notifications.id = notifications_users.notification_id").where("notifications_users.user_id IS NULL").uniq}` and it works fine. Still having issues with the second one. – Dave Sag Sep 05 '13 at 23:04
I'm trying `scope :unread_by, ->(u){Notification.joins("LEFT JOIN notifications_users ON notifications.id = notifications_users.notification_id").where("notifications_users.user_id <> ?", u.id).uniq}` but it's not giving me the results I want. My query logic is a bit confused I think. – Dave Sag Sep 05 '13 at 23:06

score 0 · Accepted Answer · answered Sep 05 '13 at 23:21

Okay this fixed it, but it doesn't seem super-elegant to me.

scope :unread_by, ->(u){
  Notification.joins("LEFT JOIN notifications_users ON notifications.id = notifications_users.notification_id").
                where("notifications_users.user_id <> ? OR notifications_users.user_id IS NULL", u.id).uniq
}

It works though. Yay - Thanks all for your help, it pointed me in the right direction.

using scopes with parameters and has_and_belongs_to_many associations with ActiveRecord (Ruby)

2 Answers2