I was wondering if there is a any idiomatic way in Ecto to format the data you get as result of a query, in particular if you would want to “skip” intermediate data in between joins.
Say I have a list of posts in a page and I for each post I have a join that returns all the information relative to my interaction as a user to these post, so the posts looks like so:
SELECT interactions -> 'vote' AS vote, title FROM posts;
If so then you can write a code like:
# alias for Post schema for example: alias MyApp.Blog.Post
import Ecto.Query
from(post in Post, select: %{title: post.title, vote: post.interactions["vote"]})
Edit: Sorry, I somehow missed join part, but basically with or without join you are using select in the same way - the only difference is that post.interactions["vote"] would need to be changed to interaction.vote.
First, you need to make sure that your join only returns one result.
Then you could (maybe) make the query like:
from(p in Post,
join: i in fragment("SELECT * FROM interactions WHERE vote = 'UPVOTE' LIMIT 1"), on: [post_id: p.id],
select: %{title: p.title, vote: i.vote},
)
|> Repo.all()
That’s does exactly what I had in mind! (It took me a while to understand what it’s doing, heh )
I wonder, is there any way I could apply that (or something of the same effect) to a preload?
I spent this morning experimenting with it but no success…