So I have a gnarly query in my open source Phoenix app I was trying to make dramatically more efficient.. in its current form it brings in a lot of unnecessary data over the wire, so I managed to get a raw SQL form that only gets the data I need, and tried translating it into Ecto, and got stuck with some errors related to group_by vs select I think… the purpose is to get the data we need, which is stocks that have a “path” to any of the credit_types in the database, grouped by which credit type(s) they have a path to (most only have one path, but some have 2, and in that case we want the stock duplicated and to appear in a column for both credit types)… in it’s current form (in Ecto, before attempted refactor) it works but it pulls every food in the database also, because they all relate to one or more credit types, but we only care about “stocked” foods. Current form goes from credit_type “toward” stocks, the refactor works by going from stocks “toward” credit type, and grouping stocks by credit type…

Here is the raw SQL version:

    select stock.id, food.long_desc, food.manufacturer_name, food_group.foodgroup_desc,
      credit_type.name
      from facilities facility
      inner join stocks stock on stock.facility_id = facility.id
      inner join foods food on food.id = stock.food_id
      inner join credit_type_memberships ctm on ctm.food_group_id = food.food_group_id
      inner join credit_types credit_type on credit_type.id = ctm.credit_type_id
    where facility.id = 1
    group by stock.id, food.long_desc, food.manufacturer_name, food_group.foodgroup_desc,
      credit_type.name

And rather than paste the non-working ecto-translation of it here, I’ll link in context to my best/closest attempt I think I got to, in the branch I was playing with it on… the play function here was accidentally committed ages ago while trying to solve the same problem, before I had a working SQL version of it, just to make it easy to test the query in iex… the stock_by_type function above it is the actual query from the app as currently used. You can probably answer any question from the code in the link there, (and original form of that query before the WIP/play commit above is actually changed here for reference) but also happy to provide context if anything is unclear/harder to find an answer to than you’d like, but you’re otherwise interested in helping with the refactor… Thanks all!