Selecting preload/virtual field

I have the following association.

Post belongs to User. User has many posts.

I have the following in the user schema

has_many :posts, MyApp.Posts.Post, foreign_key: :user_id

User with id 1 has POST_A, POST_B, POST_C

User with id 2 has POST_D, POST_E

I want to get a tuple in the following format

{
1: [
    %Post{
    id: 1,
    name: postA,
    },
    %Post{
    id: 2,
    name: postB,
    },
    %Post{
    id: 3,
    name: postC,
    }
   ],
2: [
    %Post{
    id: 4,
    name: postD,
    },
    %Post{
    id: 5,
    name: postE
    }
   ]
}

I tried writing the following query

posts_query =
    Post
    |> put_query_prefix(schema)
    |> order_by([p], desc: p.inserted_at)

    res =
    User
    |> put_query_prefix(schema)
    |> preload([posts: ^posts_query])
    |> select([u, p], {u.id, u.posts})

But this returns the following error:

field posts in select is a virtual field in schema.

Any suggestion how to resolve this?

  1. I tried using select_merge which didn’t work for me. Maybe I was wrong in framing the query.
  2. I do not want to enumerate the list since I have a large set of data and enumerating over such a long list wouldn’t be efficient.

field posts in select is a virtual field in schema.

When :has_many is declared in schema field, Ecto generates a virtual field for convenience. There is no column :posts in user schema - so we can’t “select” them from the DB, at least Ecto doesn’t do that magic for us.

What you want to do though, as far as I understand, is select all posts and group by post.user_id

Post |> Repo.all() |> Enum.group_by(fn post -> post.user_id end)

However, because we don’t want to enumerate and group the list after, we could do that in DB using aggregation functions like jsonb_agg for Postgres:

from(p in Post, group_by: p.user_id, select: {p.user_id, fragment("jsonb_agg(?)", p)})
|> Repo.all()

(adding to my answer from How to use array_agg in ecto to return structs)

If you want to fetch, say, 50 users along with all of their posts, you could write that:

users_query =
  from(u in User, limit: 50) # etc

result =
  users_query
  |> Repo.all()
  |> Repo.preload(:posts)
  |> Map.new(fn u -> {u.id, u.posts} end)

What you don’t want to do is waste the database’s CPU encoding things into JSON to try to squeeze this into one query.