SQL vs Ecto optimization/translation help

I put this to the side after we came full circle back to probably using group_by, but I opened a github issue for it, and a contributor appears to have solved the problem differently than I was trying to, but it seems to be OK, and certainly better than it was. PR here:
https://github.com/openpantry/open_pantry/pull/152

Agree @cmkarlsson, not complex at all - bad characterisation on my part.

Hah, full circle and yes, they do, not a huge number, but enough…

This might be a good use for group_by then. It is likely happening because multiple stocks join to the same/multiple credits.

Oh, cool, I was trying to select on that before but was getting an error about the binding not matching, but now I have that working, though now it seems to give me duplicate credit types somehow instead of duplicate stocks within a single credit type… this is the current query, which is maybe promising but it seems slower than it was before:

  def play() do # way more data than needed, but one query! :-/
    id = 1
    today = DateTime.utc_now
            |> DateTime.to_date
            |> Ecto.Date.cast!

    from(stocks in Stock,
      join:  food_credit_types in assoc(stocks, :credit_types),
      select: food_credit_types,
      where: ^id == stocks.facility_id,
      where: stocks.arrival < ^today,
      where: stocks.expiration > ^today,
      order_by: food_credit_types.id,
      preload: [stocks: [food: :food_group]])
    |> Repo.all
  end

Not totally sure at a glance if it’s pulling all foods form DB now or not… my guess is that it is, but, harder to tell than it was in the shape it was in before

As far as duplicates thing, if I pipe above query to map name:
|> Enum.map(&(&1.name))
then with only 3 credit types I get 6 names,
["Veggienoms", "Carbnoms", "Proteinoms", "Proteinoms", "Proteinoms", "Proteinoms"] which must be, like, the 3 or 4 foods in food groups that are both proteins and veggies or something I think, some of them repeating? Hard to reason about at a glance, but sort of seems like progress… merging those together into one struct per name/id wouldn’t be so bad though if it is avoiding pulling all the food, but like I said, at a glance I think the query is now slower by like 100% or so than the old version

At the very least to start with if you want to return a list of CreditTypes then you should add a select: statement doing so from your food_credit_types join.

Also this isn’t urgent @OvermindDL1 so please, appreciate all your help but feel free to check back in in a day or 3 if busy now but interested enough to help later! I appreciate the input and thoughts!

There won’t be a TON of overlap, but where there is we want it… mostly its about things like beans/legumes which are both a veggie and a protein, in our specific use case, and a few other foods that cross over some boundaries so we allow both credit types to be used for them… I got the Ecto query working as I expected, and like I said, now it’s a list of stocks

Here’s a workign query I’d like to turn “inside out”:

  def play() do
    id = 1
    today = DateTime.utc_now
            |> DateTime.to_date
            |> Ecto.Date.cast!

    from(stocks in Stock,
      join:  food_credit_types in assoc(stocks, :credit_types),
      where: ^id == stocks.facility_id,
      where: stocks.arrival < ^today,
      where: stocks.expiration > ^today,
      order_by: food_credit_types.id,
      preload: [food: [food_group: :credit_types]])
    |> Repo.all
  end

Once we talk in a thread then by default we get notified on updates on it.

I’m still a bit short on time, but quick question, since you want the CreditType structs, do you expect a lot of the same stocks and foods structs too, or will they all be pretty much distinct? Which answer determines which is the best way to do it.

that wasn’t done as a reply to any of you so you may not see it.. @OvermindDL1 / @cmkarlsson / @kip