0

I have three tables: location, product, and stock.

In the stock table, the fields product_id, location_id, and quantity are present. However, when a location is created (or a product, for that matter), no stock entry is created automatically. This is because, by default, to represent 'no stock', a either a stock entry with quantity '0' is used, or no entry at all. This is to prevent having to add many entries to this table when creating a new location or product.

However, I am having trouble with querying the stock of products in each location.

What I want to do is this:

  • Get all locations
  • For each location, if there is an entry in the stock table with the specific product ID we're looking for, return the quantity.
  • If not, return 0.

This sounds simple, but I'd prefer to do this with as few queries as possible (ideally just the one).

My original idea was:

Repo.all(Location) |> Enum.map(fn loc ->
  if Repo.exists?(from l in Location, 
    left_join: s in Stock, 
    where: l.id == ^loc.id and s.product_id == ^product_id) then
    Repo.one(from l in Location, left_join: s in Stock, select: s.quantity, where: l.id == ^loc.id and s.product_id == ^product_id)
  else
    0
  end
end)

but as you can see, this is very cumbersome, and makes many queries, especially with many locations.

Thanks in advance!

Basil
  • 488
  • 1
  • 15
  • In plain SQL you would use `COALESCE(s.quantity, 0)`. I don't know the `ecto` equivalent. – Barmar Jan 12 '23 at 15:53
  • What would be the full query? I tried to use COALESCE but the issue was that doing 'WHERE product_id = ?' causes the ones without any entries to disappear ^^ – Basil Jan 12 '23 at 16:03
  • 1
    `s.product_i == ^product_id` needs to be in the `ON` clause, not `WHERE`. See https://stackoverflow.com/questions/47449631/return-default-result-for-in-value-regardless/47449788#47449788 – Barmar Jan 12 '23 at 16:11
  • Ohhhh, I didn't realise you could have multiple conditions on an `ON` - thanks! – Basil Jan 12 '23 at 16:11

0 Answers0