SQL vs Ecto optimization/translation help

So I see a bunch of people have looked at this but no responses… anyone confused over what I’m asking? Possibly the answer is to just use one or more fragments to translate the pieces, which is a valid answer I’ve already considered, but wouldn’t mind advice on the best pattern/seams to tackle that, vs just making it one gigantic fragment, but welcome any thoughts or questions at all… not urgent, just been bugging me and want to find a way to resolve the question so I can stop scratching at the itch of what to do about it

More rather it is a non-small SQL statement and it is not formatted in proper markdown code tags so it is very hard to read. ^.^

I’ll fix your post to use proper markdown so it looks better.

EDIT: And edited, I do not have time to mentally parse and convert the size of that SQL just yet, but if no one else does soonish then poke me with @OvermindDL1 and I’ll take a look when I can.

Thank you! I thought it didn’t look right, but it looked ok and was a code block in the preview box, but then looked rather… flat and un-codeish on the page, appreciate the pointer though, suspected there might be a better way!

Its interesting that you’re using group_by when you aren’t aggregating anything. Which means the query is basically the same as:

    SELECT DISTINCT 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;

Which might simplify your Ecto work. Personally, for SQL that has this much complexity, I would probably create a view in the database. That way I can separate DB level optimisations from application processing.

Might not be the solution that you are looking for, but I find that complex queries are sometimes best represented as a function. Assuming that’s Postgres, here is an example of how I wrapped a search results function in Phoenix a while back.

It’s not updated for more recent versions, but same idea might be a good fit for the “don’t overthink it” approach. I think something similar would translate well with the schema/context approach.

this presentation especially from ~14 minutes mark, will empower your ecto skills a lot (schemaless queries - whole presentation is great!) - also gives you a way to compose these big queries from smaller ones..

I am not super experienced at SQL so you may be right, I got some help crafting this SQL query, but the reason for the group_by is that I want to display all the stocks that connect to a particular credit_type together… the current query starts from credit type and credit_type has_many stocks through foods, which is joined through food_groups and food_group_memberships… I guess though I wouldn’t think in terms of aggregations per se, I am kind of aggragating which stocks are reachable from which credit types, and duplicate stocks in each credit type are possible and need to be treated as seperate instances for purposes of this query, if that explains anything… the surrounding context of the Elixir code may make this clearer, but the idea is this is a pure refactor of the function as it exists on master or in the second git sha link in the before version, so that no other code needs to change… Does that help explain/understand?

Will keep that in mind @brightball, though I have some reluctance/resistance to splitting actual application logic across code and database stored functions… I know it can be efficient, and I like Ecto’s philosophy of trusting the database for constraints, which arguably verges into application logic in some cases, but constraints are pretty simple compared to stored procedures.. not ruling it out, but I don’t think the increase in complexity is justified at this point.

I understand conceptually (but the domain isn’t immediately familiar to me). My intention was to say that the SQL you wrote and the SQL I wrote should produce identical results. When there are no aggregations on a select with a group by then the behaviour is to select distinct rows. Since you mentioned you think it was the group by that was giving you challenges in Ecto, this alternative form might help you get over the bump…

Sounds more like you want to ORDER BY instead of grouping. Grouping is primarily for aggregates only and it does not sound like it is what you want.