Format nested data in a query

Hi!

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:

%Post {
  title: "a title"
  interactions: %{
    vote: "UPVOTE",
    shared: true,
  }
}

Let’s say I only care about the vote, is there any way I could query it so it would return something like

%Post {
  title: "a title",
  vote: "UPVOTE"
}

Thank you :slight_smile:

Virtual fields, check them in Ecto Schema documentation.

I guess you mean something like:

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"]})

For more information please take a look at Ecto.Query.API.json_extract_path/2.

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.

2 Likes

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()

Not exactly sure

2 Likes

Can you use virtual fields to retrieve data?

Thanks for pointing that out!

That’s does exactly what I had in mind! (It took me a while to understand what it’s doing, heh :roll_eyes:)
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…

I think it would be hard to add it in a preload. I can’t think of a good way right now.

1 Like

Well, you can populate them in your query if you need.