On Tuesday, August 21, 2018 at 3:57:29 PM UTC-7, Jeff Dyck wrote: > > Hello, I'm a bit a newbie to Sequel but loving what I've been able to > accomplish with it so far. > > Anyway, I'm trying to do a query that returns results on the count of > associated records. > > Simplified, my models look like: > > class User < Sequel::Model > one_to_many :devices > many_to_one :site > end > > class Device < Sequel::Model > many_to_one :user > many_to_one :site > end > > class Site < Sequel::Model > one_to_many :users > one_to_many :devices > end > > I'm trying to pull a list of a specified number of users (10 in this > example) from specific site, in a specific grade, that don't already have > an assigned device. > I can easily get a list of the users by site & grade, but I haven't had > any success trying to filter it only to users without devices. > > I'm trying something like the following: > > users = User.where(:site=>siteid).where(:grade=>grade).where(:devices.count > == 0).first(10) > users = User.where(:site=>siteid).where(:grade=>grade).having(:devices.count > == 0).first(10) > > Everything works up to the last where/having clause where I try to limit > based on the device count, and that's where I'm struggling. > I *think* I may need to use eager loading for this, but I haven't been > able to wrap my brain around that, so hope someone can point me in the > right direction. >
There are various ways to handle this in SQL, but a NOT IN subquery is probably simplest: users = User.where(:site_id=>siteid, :grade=>grade).exclude(:id=>Device.select(:user_id).where(:site_id=>siteid)) # SELECT * FROM users WHERE ((site_id = ?) AND (grade = ?) AND (id NOT IN (SELECT user_id FROM devices WHERE (site_id = ?)))) NOT EXISTS can also be used, and may perform better in some cases. LEFT JOIN as mentioned in an earlier reply can also be used, but that has the negative effect of returning multiple rows for the same user if the user has multiple devices. In general I recommend avoiding joining for the purpose of filtering unless the code is performance sensitive and the joining approach has benchmarked faster. Thanks, Jeremy -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sequel-talk. For more options, visit https://groups.google.com/d/optout.
