I'm writing a long query on my rails application, part of it (last .where.not is that the inventory column's quantity property is not equal 0 on its entries) is not working. My current query looks like this:
@products = @products.offset(offset).limit(@limit)
.where(status: "visible")
.where("add_to_inventory =? OR (add_to_inventory =? AND inventory !=? )", false, true, '{}')
.where.not("ARRAY(select (jsonb_each(inventory)).value->>'quantity' as integer)='{0}'")
However, in my resultant products array, Product 14 manages to pass through this filtering, product 14 looks like so.
2.4.6 :001 > Product.find(14)
Product Load (0.8ms) SELECT "products".* FROM "products" WHERE "products"."deleted" = $1 AND "products"."id" = $2 LIMIT 1 [["deleted", "f"], ["id", 14]]
=> #<Product id: 14, deleted: false, category_id: 11, created_at: "2020-10-26 10:27:22", updated_at: "2020-11-10 16:26:11", final_price: 2342.0, status: 0, inventory: {"fsaf"=>{"sku"=>"", "barcode"=>"", "quantity"=>"0"}, "werwe"=>{"sku"=>"", "barcode"=>"", "quantity"=>"0"}}, add_to_inventory: true>
You can see that the inventory quantities are 0, how is this passing the filter?
It is passing the filter because there are two items in inventory. Current filter only filters out cases when
When there are two or more items in inventory, it's not going to filter it out.
If you need to filter out cases when all quantities are 0, you need to change the last line of the filter to
.where.not("ARRAY(select distinct (jsonb_each(inventory)).value->>'quantity' as integer)='{0}'")