Getting user data in a list

I have a table name posts and likes
likes table will have a references to users table and posts table
I want to get a list of posts (basically like twitter feed or some other social media) where it will show the if the user liked the post

Repo.all(Post) will get all the post list but without any association with the likes table. Is there any method that elixir provided that I did not know :confused:


Example for liked table columns

id |user_id | post_id


Example json output

{  
  "posts":[  
  {  
     "id":1,
     "title":"Some title here",
     "content":"Some message here",
     "liked":true     //this will based on liked table
  },
  {  
     "id":2,
     "title":"Some title here",
     "content":"Some message here",
     "liked":false     //this will based on liked table
  }
  ]
}

If there someone already ask something similar to this, please lead me to the topic
Any kind of help would be appreciated :slight_smile:

You should use Repo.preload.

If you want to preload only one relationship:
Repo.all(Post) |> Repo.preload(:like)

If you want to preload more than one relationship:
Repo.all(Post) |> Repo.preload([:like, :other_relationship])

If you want to preload cascade relationship ( assuming that likes belongs to user )
Repo.all(Post) |> Repo.preload([likes: [:user]])

Let’s assume I’m logged in as a user

Repo.all(Post)
|> Repo.preload(like only for a user that I logged in?)

How can I achieve this?

Are you willing to fetch a list of posts a specific user liked?

If that’s the case, instead of querying the posts table, you could do this:

defmodule Example do
  import Ecto.Query

  alias Example.Repo
  alias Example.Like

  def get_liked_posts(user) do
    Repo.all \
    from like in Like,
      where: like.user_id == ^user,
      join: post in assoc(like, :post),
      select: post
  end
end

You can add a virtual field to the post schema:

schema "posts" do
  ...
  field(:liked, :boolean, virtual: true)
end

Then you can do a query like this:

def with_liked_field(query, viewer_id) do
  from post in composed_query,
    left_join: like in Like,
    on: [post_id: post.id, user_id: ^viewer_id],
    select_merge: %{liked: not is_nil(like.user_id)}
end

https://hexdocs.pm/ecto/Ecto.Query.html#select_merge/3

Hope it helps!

2 Likes

Or use the following:

user_id = 1
query = from p in Post,
    join: l in assoc(p, :likes),
    join: u in assoc(l, :user),
    where: u.id == ^user_id

query
|> Repo.all
|> Repo.preload([likes: [:user]])

The problem with this is that it is filtering all the posts that are not liked by the user and they are never showed up.

Thanks alex. This works!