Query to select parent and child fields with preload? Filter out sensitive fields for JSON API

Questions / Help
1

With the following schema, is it possible to filter out version in %Post{} and password from %User{} with a preload query and select?

I’m able to filter out password from %User{}, but cannot remove version in %Post{}.

# Post
schema "posts" do
  field :id
  field :title
  field :version
  has_many(:user_ids, MyApp.UserPost, on_replace: :delete)
  has_many(:users, through: [:user_ids, :user], preload_order: [asc: :username])
end

# User
schema "users" do
  field :id
  field :username
  field :password
end

I would like to output a map like this.

[%{
  id: 80,
  title: "title",
  user_ids: [ %{ id: 1 }, %{ id: 2 }, %{ id: 3 } ]
}]

This gives me ** (Tds.Error) Line 1 (Error 207): Invalid column name 'user_ids'.

    user_ids_query = from u in UserPost, select: %{id: u.user_id}

    query =
      from p in Post,
        where: p.id == 80,
        preload: [user_ids: ^user_ids_query],
        select: map(p, [:id, :title, :user_ids]) # error here

Without the select in query, it gives me all the fields of %Post{}, but user_ids is correct.

[%Post{
  ...
  id: 80,
  title: "title",
  version: 3,
  user_ids: [ %{ id: 1 }, %{ id: 2 }, %{ id: 3 } ]
}]
2 
Repo.all from p in Post,
           join: user_ids in assoc(p, :user_ids),
           where: p.id == 80,
           select: %{id: p.id, title: p.title, user_ids: user_ids}

user_id struct I presume has it’s own ID, user’s ID and post’s ID. Why is it necessary to show only the user’s one and for it to be under id, not user_id?

3

The API is defined to return a list of user_ids in the format of an array of {"id": 123}.

I could not get Ecto to return queries in the format I wanted. I have a layer rendering Ecto results to the JSON format I needed. I think what I need is @benwilson512’s Absinthe.

4

map(p, [:id, :title, :user_ids])

This is the relevant piece to your error. This has a few effects.

  • First of all :user_ids is not a column you can select. You cannot execute SELECT user_ids from posts. Preloads doesn’t magically add columns you can “query”.
  • Even if you remove :user_ids and use map(p, [:id, :title]) you’ll return a map, which now doesn’t have a user_ids key. So there’s no place for ecto to put the preloaded information you’re after.
  • You could …, select: %{id: p.id, title: p.title, user_ids: []} to return maps with that additional key. But you still return a map, which likely means preloading :user_ids doesn’t work. A bunch of information needed to preload data is provided via the schema used for data. Returning a plain map means that information is not available to ecto.
  • You could go with struct(p, [:id, :title]), which should get you close to what you want to do, but it’ll return post structs not plain maps.

Generally I’d suggest taking a step back:

  • You could go with simpler querying, but filtering out data at runtime. More on that here: Phoenix Views for JSON APIs | Benjamin Milde
  • You can also build queries, which return plain data, but then you’ll need to drop depending on schema level information, in this case mostly automatic type casting and preloads.
2 Likes