I’m writing a query to get shopping carts with duplicate products in them. I have a Cart that has_many Items. I’m trying to query carts with duplicated items in the cart, but I only want to look in specific carts that have been abandoned so I’ve written a query like this:
from(itm in Item,
join: cart in Cart,
on: cart.id == itm.cart_id,
where: cart.status in ["abandoned"],
join: itm2 in Item,
on: itm.id != itm2.id and itm.cart_id == itm2.cart_id and c.product_id == itm2.product_id,
select: itm.id
)
Perhaps this is overly verbose, but I’m looking for items, where the associated cart is ‘abandoned’, then I want to see if those items are duplicated (same product in the same cart) and return the item ids.
The above query returns >='. However; if I change the last line to select: %{id: itm.id}I'll get a list of maps with the correct item IDs. This confirms to me that the query works but for some reason, if I just want the IDs, not as a map something goes wrong. I've tried writing the query a few other ways and sometimes I get a=>` returned as well but it’s the same thing, if I wrap it in a map it returns the right IDs.
