bglusman
SQL vs Ecto optimization/translation help
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!
Most Liked
brightball
Not going to disagree too much. Mainly just wanted to toss it out as an option.
In terms of the app logic separation though, just keep in mind that there are some types of app logic that have been trumpeted by frameworks with the goal of database portability that actually negatively impact your application. The database is the only thing that can actually guarantee database integrity since otherwise you’re prone to race conditions.
Functions are really simple and powerful in Postgres though and the type of structure outlined in that post is a couple of lines of code around a query…that roughly translates to the equivalent of a database view with arguments.
The only thing I’d provide as some parting wisdom is this; at the point that you’re performance tuning your data storage or retrieval, all options should be on the table. I’ve seen cases where people were so committed to the idea of avoiding leveraging their database that they were more willing to add 3rd party tools as a workaround (like Redis/Elasticsearch) than using the tooling already in the database.
I don’t think it’s necessary in your case right now, just always be aware that your database (especially PG) is a really powerful thing. Take advantage. Don’t keep a Tesla Roadster in the garage to get groceries. 
OvermindDL1
Yeah, I’ve not had a project yet where I did not have to break out of Ecto some-how… ^.^;
I especially like it as a Static Typing and Translation layer on top of SQL. It catches stupid little typing bugs that I make, which consequently is also the main reason why I love statically typed languages. 
outlog
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…
kip
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…
Popular in Questions
Other popular topics
Latest on Elixir Forum
Sponsor Spotlight
Develop your skills with books, videos, and courses.
Categories:
Sub Categories:
Forums
Our Sponsors
Build Elixir applications with speed and confidence.
Supporting innovation across the BEAM ecosystem.
We build reliable cloud platforms for business-critical systems.
Catch errors, track performance, monitor hosts and more.
Practical resources that improve the lives of professional developers.
Enabling companies to succeed by building software people love.
The deployment platform built for Elixir: PaaS ease, VPS control.
Courses that'll move you from confusion to "Aha, now I get it!"