I have a situation where I am searching for products but the conditions include the following:
In order for a product to be returned in the results, there must be stock available when you total all the stock remaining at all the stock locations but first subtracting the minimum allowed stock levels from each location (or 5 if that value is absent).
For example, “Store A” has three stock locations: Location 1, Location 2 and Location 3. All these locations stock “Product A” which has an alert_quantity field with a value of 5. The have these quantities left:
Location 1: 15
Location 2: 3
Location 3: 6
The product should be returned with an available quantity of 11 (15-5 plus 6-5). Location 2 should be ignored in totaling the available stock because it is below the threshold of 5.
On the other hand in the following scenario, the product should not be part of the results:
Location 1: 1
Location 2: 3
Location 3: 5
The relationships between tables are as follows:
‘InventoryProducts hasMany Variations’
‘Variations hasMany VariationLocationDetails’
I am already doing the standard product query but this portion of the conditions is missing in order to bring back just the results we require.