spree 3.0 only show products that have available stock

Question

So i see this question has been asked before but the answer wasn't posted even though the question got answered, and since i dont have enough points to comment on the answer i need to ask it again. here is the original post

Is there a scope to allow me to show only products with variants with stock?

This is for spree 3.0. when i added the shown code

joins(
  variants_including_master: :stock_items
).group('spree_products.id').having("SUM(count_on_hand) > 0")

to my product decorator like such

def self.available(available_on = nil, currency = nil)
    # joins(:master => :prices).where("#{Product.quoted_table_name}.available_on <= ?", available_on || Time.now)      
    joins(:master => :prices).where("#{Product.quoted_table_name}.available_on <= ?", available_on || Time.now).joins(variants_including_master: :stock_items).group('spree_products.id').having("SUM(count_on_hand) > 0")  
end
search_scopes << :available

this blows up the view since the result has some strange properties due to the GROUP BY as far as i can tell. this errors out in the products_helper.rb file on the line

  max_updated_at = (@products.maximum(:updated_at) || Date.today).to_s(:number)

because @products.maximum(:updated_at) returns a hash

{[6, 6]=>2015-07-04 19:08:36 UTC, [3, 3]=>2015-07-04 19:07:37 UTC, [10, 10]=>2015-07-07 19:01:12 UTC}

instead of returning a date

2015-07-07 19:01:12 UTC

which it does normally.

this code also blows up on the taxon page with the following error:

PG::GroupingError: ERROR: column "spree_products_taxons.position" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT DISTINCT "spree_products"."id", spree_products_taxon... ^

: SELECT DISTINCT "spree_products"."id", spree_products_taxons.position AS alias_0 FROM "spree_products" INNER JOIN "spree_variants" ON "spree_variants"."product_id" = "spree_products"."id" AND "spree_variants"."is_master" = 't' AND "spree_variants"."deleted_at" IS NULL INNER JOIN "spree_prices" ON "spree_prices"."variant_id" = "spree_variants"."id" AND "spree_prices"."deleted_at" IS NULL INNER JOIN "spree_variants" "variants_including_masters_spree_products" ON "variants_including_masters_spree_products"."product_id" = "spree_products"."id" AND "variants_including_masters_spree_products"."deleted_at" IS NULL INNER JOIN "spree_stock_items" ON "spree_stock_items"."variant_id" = "variants_including_masters_spree_products"."id" AND "spree_stock_items"."deleted_at" IS NULL LEFT OUTER JOIN "spree_products_taxons" ON "spree_products_taxons"."product_id" = "spree_products"."id" WHERE "spree_products"."deleted_at" IS NULL AND ("spree_products".deleted_at IS NULL or "spree_products".deleted_at >= '2015-07-09 14:30:53.668422') AND ("spree_products".available_on <= '2015-07-09 14:30:53.669089') AND "spree_products_taxons"."taxon_id" IN (2, 7, 14) AND (spree_prices.amount IS NOT NULL) AND "spree_prices"."currency" = 'USD' GROUP BY spree_products.id HAVING SUM(count_on_hand) > 0 ORDER BY spree_products_taxons.position ASC LIMIT 12 OFFSET 0

SO there must be a better way to do this so that the GROUP by clause doesn't throw everything off.

zrl3dx zrl3dx · Accepted Answer · 2015-07-09T20:08:04

Try something like this:

  joins(:master => :prices, variants: :stock_items).where("#{StockItem.quoted_table_name}.count_on_hand > 0 AND #{Product.quoted_table_name}.available_on <= ?", available_on || Time.now).uniq

You might want to include backorderable and track_inventory too - I don't know your setup, maybe you don't have such variants at all.

Edit: Added uniq. If you want master variants too then just change variants: :stock_itemspart to variants_including_master: :stock_items.

spree 3.0 only show products that have available stock

1 Answers