Fetch fields from an association?

Phoenix Forum Questions / Help
1

Hi

I have Thread which has many Likes. A User can Like a thread with a specific amount.

So, we have a threads table, thread_likes (join table with like_amount, user_id and thread_id) and users.

How can I, in Ecto, create a query which returns a map with the fields from the thread and the like_level for the threads (for a specific user)?

What id like to achieve:

[
  %{id: 1, title: "Something", body: "More", ..., user_like_level: 3},
%{id: 2, title: "Something else", body: "More", ..., user_like_level: 2},
%{id: 3, title: "Something", body: "More", ..., user_like_level: 0}
]
2

I believe you are looking for preload:

When you have a struct and want to load another relation:
https://hexdocs.pm/ecto/Ecto.Repo.html#c:preload/3
Or if you want to do it all at the same time:
https://hexdocs.pm/ecto/Ecto.Query.html#preload/3

3

You could also use a join and select…
I dont know what your schema looks like, but heres a stab at some code that should get you close to what you need:

WARNING: UNTESTED

# Assuming user has_many :likes, through: Thread
query = from u in User,
        join: l in assoc(u, :likes),
        select: {u, l}
results = Repo.all(query)
4

Thank you, but preloads is not the entire solution here I believe. Using preloads I can do something like this:

def threads_with_like_level_for_user(user) do
  thread_like_query =
    from tl in MyApp.ThreadLike,
      where: tl.user_id == ^user.id,
      select: map(ti, [:like_level])

  from t in MyApp.Thread,
    left_join: tl in MyApp.ThreadLike,
    on: t.id == tl.thread_id and tl.user_id == ^user.id,
    preload: [thread_likes: ^thread_like_query]
end

This returns something like this:

[
%MyApp.Thread{id: 1, title: "Something", body: "Foo", ..., thread_likes: [%{like_level: 2}]},
%MyApp.Thread{id: 2, title: "Something else", body: "Foo", ...,, thread_likes: [%{like_level: 1}]},
%MyApp.Thread{id: 2, title: "Something else", body: "Foo", ..., , thread_likes: []}
]

Which is the closest I’ve come to my goal above, but not exactly as you can see.

5

It would really help to see your Schemas, but assuming ThreadLike.like_level is singular then what about something like:

def thread_with_like_level_for_user(user) do
  from t in MyApp.Thread,
    left_join: tl in MyApp.ThreadLike, on: ti.id == tl.thread_id and tl.user_id == ^user.id,
    select: %{id: t.id, title: t.title, body: t.body, user_like_level: tl.thread_likes}
end
6

My tables look like this (the important fields at least :slight_smile:) :

threads
id, title, ...

thread_likes
thread_id, user_id, like_level

users
id, name, ...

So, a Thread has_many :thread_likes and a User has_many :thread_likes. However, a user can only like each thread once.

7

Yes, I think this would work actually. But it would be user_like_level: tl.like_level and the end :slight_smile:

Thanks.

Is there a way to fetch all the fields from the thread (and just add user_like_level) or do I have to explicitly list them all?

8

If you just use tl in your select it will give you the full row data.

9

Yep, I tend to be explicit in what I select for clarity though. :slight_smile:

Er, yes that, corrected. ^.^

10

Yes, but I want all fields in t and only one in tl. :slight_smile:

11

Thank you

12

This doesn’t really work actually…

If I try to include a has_many association here, like this:

def thread_with_like_level_for_user(user) do
  from t in MyApp.Thread,
    left_join: tl in MyApp.ThreadLike, on: ti.id == tl.thread_id and tl.user_id == ^user.id,
    join: p in assoc(t, :posts),
    select: %{id: t.id, title: t.title, body: t.body, user_like_level: tl.like_level, posts: p}
end

It doesn’t return a list of posts, but instead just a map. Can I get the association in there as well?