How do I write a Rails finder method where none of the has_many items has a non-nil field?

First of all, this really depends on whether or not you want to use a pure Arel approach or if using SQL is fine. The former is IMO only advisable if you intend to build a library but unnecessary if you're building an app where, in reality, it's highly unlikely that you're changing your DBMS along the way (and if you do, changing a handful of manual queries will probably be the least of your troubles).

Assuming using SQL is fine, the simplest solution that should work across pretty much all databases is this:

Order.where("(SELECT COUNT(*) FROM line_items WHERE line_items.order_id = orders.id AND line_items.discount_applied IS NULL) = 0")

This should also work pretty much everywhere (and has a bit more Arel and less manual SQL):

Order.left_joins(:line_items).where(line_items: { discount_applied: nil }).group("orders.id").having("COUNT(line_items.id) = 0")

Depending on your specific DBMS (more specifically: its respective query optimizer), one or the other might be more performant.

Hope that helps.

A possible code

Order.includes(:line_items).where.not(line_items: {discount_applied: nil})

I advice to get familiar with AR documentation for Query Methods.

Update

This seems to be more interested than I initially though. And more complicated, so I will not be able to give you a working code. But I would look into a solution using LineItem.group(order_id).having(discount_applied: nil), which should give you a collection of line_items and then use it as sub-query to find related orders.

If I understood correctly, you want to get all orders for which none line item (if any) has a discount applied.

One way to get those orders using ActiveRecord would be the following:

Order.distinct.left_outer_joins(:line_items).where(line_items: { discount_applied: nil })

Here's a brief explanation of how that works:

• The solution uses left_outer_joins, assuming you won't be accessing the line items for each order. You can also use left_joins, which is an alias.
• If you need to instantiate the line items for each Order instance, add .eager_load(:line_items) to the chain which will prevent doing an additional query for every order (N+1), i.e., doing order.line_items.each in a view.
• Using distinct is essential to make sure that orders are only included once in the result.

Update

My previous solution was only checking that discount_applied IS NULL for at least one line item, not all of them. The following query should return the orders you need.

Order.left_joins(:line_items).group(:id).having("COUNT(line_items.discount_applied) = ?", 0)

This is what's going on:

• The solution still needs to use a left outer join (orders LEFT OUTER JOIN line_items) so that orders without any associated items are included.
• Groups the line items to get a single Order object regardless of how many items it has (GROUP BY recipes.id).
• It counts the number of line items that were given a discount for each order, only selecting the ones whose items have zero discounts applied (HAVING (COUNT(line_items.discount_applied) = 0)).

I hope that helps.

Not efficient but I thought it may solve your problem:

orders = Order.includes(:line_items).select do |order|
  order.line_items.all? { |line_item| line_item.discount_applied.nil? }
end

Update:

Instead of finding orders which all it's line items have no discount, we can exclude all the orders which have line items with a discount applied from the output result. This can be done with subquery inside where clause:

# Find all ids of orders which have line items with a discount applied:
excluded_ids = LineItem.select(:order_id)
                       .where.not(discount_applied: nil)
                       .distinct.map(&:order_id)

# exclude those ids from all orders:
Order.where.not(id: excluded_ids)

You can combine them in a single finder method:

Order.where.not(id: LineItem
                    .select(:order_id)
                    .where.not(discount_applied: nil))

Hope this helps

If you want all the records where discount_applied is nil then:

Order.includes(:line_items).where.not(line_items: {discount_applied: nil})

(use includes to avoid n+1 problem) or

Order.joins(:line_items).where.not(line_items: {discount_applied: nil})

Here is the solution to your problem

order_ids = Order.joins(:line_items).where.not(line_items: {discount_applied: nil}).pluck(:id)
orders = Order.where.not(id: order_ids)

First query will return ids of Orders with at least one line_item having discount_applied. The second query will return all orders where there are zero instances of a line_item having the discount_applied.

I would use the NOT EXISTS feature from SQL, which is at least available in both MySQL and PostgreSQL

it should look like this

class Order
  has_many :line_items
  scope :without_discounts, -> {
    where("NOT EXISTS (?)", line_items.where("discount_applied is not null")
  }
end

You cannot do this efficiently with a classic rails left_joins, but sql left join was build to handle thoses cases

Order.joins("LEFT JOIN line_items AS li ON li.order_id = orders.id 
                                       AND li.discount_applied IS NOT NULL")
     .where("li.id IS NULL")

A simple inner join will return all orders, joined with all line_items,
but if there are no line_items for this order, the order is ignored (like a false where)
With left join, if no line_items was found, sql will joins it to an empty entry in order to keep it

So we left joined the line_items we don't want, and find all orders joined with an empty line_items

And avoid all code with where(id: pluck(:id)) or having("COUNT(*) = 0"), on day this will kill your database