empty? on associations
Reported by Chris Wanstrath | September 18th, 2007 @ 12:41 PM
Nicolas wants:
class Provider < AR
has_many :orders
end
class Order < AR
belongs_to :provider
end
Provider.select { |p| p.name =~ 'A%' && p.orders.empty? }
(to retrieve all the providers which name begins by A and have no orders)
Mislav says:
SELECT ... FROM providers
LEFT JOIN orders ON provider.id = orders.provider_id
WHERE providers.name LIKE 'A%'
GROUP BY providers.id
HAVING COUNT(orders.id ) = 0
Comments and changes to this ticket
-
ronin-13570 (at lighthouseapp) February 15th, 2008 @ 03:19 AM
Unfortunately this won't work as suggested as the GROUP BY clause MUST include every non aggregating column listed in the SELECT clause.
Typically the same thing can be achieved using a correlated sub-query (and NOT EXIST) which most databases can optimise to essentially the same thing anyway.
-
Chris Wanstrath February 16th, 2008 @ 03:30 AM
- → State changed from “open” to “hold”
Please Login or create a free account to add a new comment.
You can update this ticket by sending an email to from your email client. (help)
Create your profile
Help contribute to this project by taking a few moments to create your personal profile. Create your profile »
