Let’s say I have two contexts: todo_summary and a todo_item.
todo_summary
schema “todo_summary” do
field :user_id, :string
field :name, :string
field :total_points, :integer
todo_item
schema “todo_items” do
field :user_id, :string
field :name, :string
field :points, :integer
Here is where I am stuck: I am not sure how to structure, or even accomplish something like tallying up every single *todo_item who’s user_id is X, and making that the value of todo_summary so that when the user adds another todo_item, it will automatically update the value of the summary’s total_points with the new sum.
I would greatly appreciate any help regarding this, and also maybe some pointers on what I should learn or practice in order to become more comfortable with this kind of logic. Is it an elixir thing? A phoenix thing? A simple SQL query I should be doing?
When I look at this I see that the total_points field can be accurately queried by adding all the points field in the todo_items table. This would be my initial approach as it will keep the database consistent and you don’t have to keep track that the fields must be in sync. The drawback with this approach is that it may become expensive if you have a large amount of todo_items per user but I see that as a later optimization.
In normal SQL I’d do something like this:
SELECT user_id, name, SUM(i.points) AS total_points
FROM todo_summary s LEFT JOIN todo_item i
ON s.user_id = i.user_id
WHERE s.user_id = X
Then you don’t need to keep changing the total_points field all the time.
I’m not sure if ecto supports a “dynamic field” built from a query. I know python’s sqlalchemy support it but don’t know how efficient it is
The other alternative would be to constantly update the total_points field when updating the todo_items. I feel this is more error prone but it is certainly doable and sometimes needed depending on the size of the tables. It can be done in application logic within a transaction or perhaps even with database triggers (although that opens up another can of worms).
I don’t actually know how to solve this the idiomatic way in ecto. Hopefully someone can fill in.
I think I know what you mean, I am having a really difficult time trying to implement something like that though. The first way you mentioned, with the SQL in a sort of “dynamic field” sounds ideal, but I’ve searched the docs and can’t find a way to do something like this
I’d personally just stick with the SUM() until it becomes a problem. Relational databases are fast at that sort of thing, especially when you’ve got the right indexes in place.
Maybe https://hexdocs.pm/ecto/Ecto.Repo.html#c:aggregate/4 is the answer?
Another option would be to create a view in your database and query that, but the SQL query would look very similar.
1 Like
Do you mind explaining it in a little more detail if possible? Where would I put the SUM(), in the schema? Or in a controller action perhaps?
Sure, I’d put it as part of my app’s Repo. You have a couple choices. One is to just have a one-off function that gives you the total score for a given user id.
The Ecto query API DSL has a sum() function:
defmodule YourApp.Repo do
def points_for_user(user_id) do
from q in TodoItem, where: q.user_id = ^user_id select: sum(q.total_points)
end
end
You could also have a function that takes an existing query so you can chain it:
defmodule YourApp.Repo do
def points_for_user(query, user_id) do
from q in query, where: q.user_id = ^user_id select: sum(q.total_points)
end
end
Or If you just want a one-off query with aggregate/4, you could do something like:
defmodule YourApp.Repo do
def points_for_user(user_id) do
aggregate(from q in TodoItem, where: q.user_id = ^user_id, :sum, :total_points)
end
end
Just sorta guessing here, so there are probably syntax errors or some other detail that might make it not work… but this should hopefully get you close!
1 Like
