I’m trying to write a query that counts the number of products from a supplier group that are on a specific state or return 0 if it doesn’t have products.
This query will only return the count if the product exists.
x
SUPPLIER_GROUP_ATTRS = [
"id",
"count(products) as completed_products_count"
]
SupplierGroup.left_joins(:products).where("products.state=?","completed").select(SUPPLIER_GROUP_ATTRS).group(:id)
Advertisement
Answer
use sum()
instead of count()
, then select case
so we can ignore your where
clause.
SUPPLIER_GROUP_ATTRS = [
"id",
"sum(case when products.state='completed' then 1 else 0 end) as completed_products_count"
]
SupplierGroup.left_joins(:products).select(SUPPLIER_GROUP_ATTRS).group(:id)