bglusman

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 Responses

brightball

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. :slight_smile:

OvermindDL1

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. :slight_smile:

outlog

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..

Where Next?

Popular in Questions Top

Harrisonl
We have an ECS cluster with 4 services, where each task joins a single cluster, via discovery ECS discovery service. Currently when I de...
New
greenz1
I have a phoenix application from which a user can download multiple(5-6) files of size 1MB. I couldn’t find anything related to sending ...
New
Patoshizzle
After calling mix ecto.create I get this error: 17:00:32.162 [error] GenServer #PID<0.412.0> terminating ** (Postgrex.Error) FATAL...
New
fireproofsocks
I’m working on defining a simple Ecto schema for a table (in PostGres), but I don’t see where I can define a column as NOT NULL. Conside...
New
Fl4m3Ph03n1x
About me? ( if you have nothing better to do than reading about some random guy in the internet :stuck_out_tongue: ) Hello all, this is ...
New
jaysoifer
Is there a way to rollback a specific migration and only that one (“skipping” all the other ones)? Would mix ecto.rollback -v 200809061...
New
rms.mrcs
Hi, I need to transform a list of numbers into a map where the keys are the indexes and the values are the original values of the list. ...
New
shijith.k
I am trying to start a new phoenix project with elixir 1.9, but mix phx.new does not work. It says that ** (Mix) The task "phx.new" could...
New
dotdotdotPaul
Okay, I’m having a heck of a time trying to figure out how to best handle the validation of belongs_to associations in Ecto. I’m sure I’...
New
hariharasudhan94
I would like to know what is the best IDE for elixir development?
New

Other popular topics Top

sen
Hi All, I set a environment variables in dev.exs , like below code. when i start server, how can i set the ${enable} value? thanks. d...
New
ovidiubadita
Hey all, I discovered Elixir and I love it. I always wanted to learn a functional programming and I intended to go for Haskell, but afte...
New
johnnyicon
Hi all, I’ve just started learning Elixir and Phoenix Framework, so please pardon my n00bness at this stage. I’m trying to use Postgres...
New
Fl4m3Ph03n1x
About me? ( if you have nothing better to do than reading about some random guy in the internet :stuck_out_tongue: ) Hello all, this is ...
New
hariharasudhan94
lets say i have a sample like a = 20; b = 10; if (a > b) do {:ok, "a"} end if (a < b) do {:ok, b} end if (a == b) do {:ok, "equa...
New
joeerl
Hello again - after a longish gap I’ve decided I really must dig into Elixir and see what’s been happening here - so I have a few questio...
New
KronicDeth
Elixir plugin for JetBrain’s IntelliJ Platform (including Rubymine) This is a plugin that adds support for Elixir to JetBrains IntelliJ...
289 36128 110
New
Brian
What is the proper way to load a module from a file in to IEX? In the python world, doing something like this pretty standard: from ....
New
hariharasudhan94
I would like to know what is the best IDE for elixir development?
New
dogweather
I wrote this comment on r/haskell, and it’s not popular there. :wink: But I think I’m on to something… Haskell reminds me of Java, and e...
New

We're in Beta

About us Mission Statement