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 Sign in or create a free account to add a new ticket.
With your very own profile, you can contribute to projects, track your activity, watch tickets, receive and update tickets through your email and much more.
Create new ticket
Create your profile
Help contribute to this project by taking a few moments to create your personal profile. Create your profile ยป
Chris Wanstrath