Rails Query Joins and Where Not-CodePudding

I have two models, Customer and Driver. There is a join table (as a full model) which is used to blacklist drivers and customers from each other.

class Driver
  has_many :customer_blacklists
  has_many :blacklisted_customers, through: :customer_blacklists, source: :customer
end


class Customer
  has_many :driver_blacklists, class_name: 'CustomerBlacklist', foreign_key: 'customer_id'
  has_many :blacklisted_drivers, through: :driver_blacklists, source: :driver
end

class CustomerBlacklist
  belongs_to :customer
  belongs_to :driver
end

I want to create an efficient query to find all drivers who are not blacklisted by the current customer.

Currently I have either:

Driver.where.not(id: CustomerBlacklist.where(customer_id: customer_id).pluck(:driver_id))

Driver.where.not(id: [customer.blacklisted_drivers.pluck(:id)] ) }

Both of these work fine (they're basically the same thing, the second is more readable. But there are two seperate queries happening here.

I've tried without success to make this a single query, my last try looked like:

Driver.left_joins(:customer_blacklists).where.not(customer_blacklists: {driver_id: driver_id, customer_id: customer_id} )

But this returns only Drivers who do have an entry in the customers_blacklists table, but not for the specified customer. It does not include any drivers who have never been blacklisted at all.

The query should return

All Drivers who are not blacklisted by anyone
All Drivers who are not blacklisted by the current customer

CodePudding user response：

All you actually need is:

Driver.where.not(id: customer.blacklisted_drivers)

ActiveRecord is smart enough to build a subquery when you pass an assocation to .where. .pluck(:id) is largely an anti-pattern as you're preventing the ORM from actually making a better query.

Another alternative to a subquery that can be more performant is a lateral join - however there is no polyglot solution for that so it would be database specific. I would revisit this later if the performance actually becomes a problem.