Rails 5 where or active record query

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.

SUPPLIER_GROUP_ATTRS = [
  "id",
  "count(products) as completed_products_count"
]
SupplierGroup.left_joins(:products).where("products.state=?","completed").select(SUPPLIER_GROUP_ATTRS).group(:id)

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)