How to map structs

[Vladislav Shcherbin]

I've got University and Faculties. University can have several Faculties (one to many)

I need to take University and all Faculties and send to client in this format:

{
  "fullTitle": "university title",
  "faculties": [
    {
      "shortTitle": "faculty 1 short title"
    },
    {
      "shortTitle": "faculty 2 short title"
    }
  ]
}

So, I need to use preload.

I use Ecto 2.0 and the query:

University
|> where(slug: "slug")
|> preload(:faculties)
|> select([:id, :fullTitle, faculties: [:shortTitle]])
|> Repo.first

The query works, but now I have two questions:

1) Why do I need to specify the university id in select (it is used in faculties column university_id). Can't it take it automatically?

2) This will return me unnecessary fields and metadata, how can I return only necessary data w/o metadata?

I tried this, but it gives an error.

University
|> where(slug: "slug")
|> preload(:faculties)
|> select([u, f], %{
  fullTitle: u.fullTitle,
  faculties: %{
    shortTitle: f.shortTitle
  }
})
|> Repo.first

I can't find any examples and all my experiments throw me an error. Maybe there is a function to normalize the struct and return map (it is very needed for json response) ?

[Wojtek Mach]

Hi!

I'm not sure if it's gonna be helpful (or accurate :)) but as far as I know when you're doing: `select: [:id, :fullTitle]` it's the same as doing: `select: take(University, [:id, :fullTitle])` which always returns the struct of your schema, however it only populates fields you specify. See Ecto.Query.API.take.

It's interesting problem; while it's kinda gross I suppose you can do a join and select fields with different syntax than with `take` and then manually build up your response (I swapped University/Faculy with Post/Comment as I had some similar code around):

from(p in Post,
     join: c in assoc(p, :comments),
     select: {p.title, c.body},
     where: p.title == "Post 1")
|> Repo.all
|> Enum.reduce(%{title: nil, comments: []}, fn ({title, comment}, %{comments: comments}) ->
  %{title: title, comments: comments ++ [%{text: comment}]}
end)

%{comments: [%{text: "Comment 1"}, %{text: "Comment 2"}], title: "Post 1"}

You'll probably be better off by doing what you were previously doing and manually massaging data after getting it from DB.

I'm really curious myself what's the cleanest solution to this problem.

Regards,
Wojtek        

[Vladislav Shcherbin]

Yes, it always returns a strict with populated fields, you are right. While we can do such dirty mapping, it would be awesome to have a clean solution, like we have a great solution with traverse_errors.

For me, this is one of the most needed things in Ecto as I always need JSON and I always spend a lot of time just trying to get the correct output.

In previous ORM I had a method:

$user = App\User::find(1);
return $user->toJson();

It worked flawlessly and is very clean and obvious. Maybe we can have a similar function here (like Repo.to_json). 

Maybe there is another clean solution or any pitfalls of such function?

[José Valim]

Why do you want to leave the metadata out? The examples you have posted should work as long you specify which fields you want to encode. In your schema:

defmodule University do

  @derive {Poison.Encoder, only: [:id, :name, :title]}

  schema "..." do

    ...

  end

end

Now you can: Repo.get!(University, 1) |> Poison.encode

If you prefer to generate the whole data from the query, you can:

from u in University,

  where: u.slug == "slug",

  join: f in assoc(u, :faculties),

  select: %{fullTitle: u.fullTitle, faculties: %{shortTitle: f.shortTitle}}

  

José Valim

www.plataformatec.com.br

Skype: jv.ptec

Founder and Director of R&D

--
You received this message because you are subscribed to the Google Groups "elixir-ecto" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elixir-ecto...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-ecto/2dada250-439f-4290-92e9-c3f331100d72%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[Vladislav Shcherbin]

With @derive we need to list all fields again (overkill, no?) and I guess the query below will still return me a struct with all db fields with the populated ones.

University
|> where(slug: "slug")
|> preload(:faculties)
|> select([:id, :fullTitle, faculties: [:shortTitle]])
|> Repo.first

We can use join, but it will return an array of faculties, so the output will be:

[

  {
    "fullTitle": "university title",
    "faculties": {

      "shortTitle": "faculty 1 title"
    }
  },

  {
    "fullTitle": "university title",
    "faculties": {

      "shortTitle": "faculty 2 title"
    }
  }
]

I tried it, but only preload gives me the university with faculties inside.

[José Valim]

You can also derive with except: [:__meta__] if you want to include everything but I would strongly advise against doing that. Exposing everything by default is a bad idea because:

1. You are coupling your database representation with the user facing API

2. You may accidentally expose sensitive fields, specially as they are added in the future. I have seen folks accidentally exposing authentication tokens due to the "include all" philosophy, private rejection criteria on user facing API and so on.

So at this point you have three different ways of tackling this: you can build them as part of the query, you can derive the protocol (with only or except) or you can use Phoenix generators that will give you a working structure out of the box. We are not going to make it easy to do the wrong thing though.

To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-ecto/5dc5bd6b-7d25-4534-bf73-b9636277a51d%40googlegroups.com.

[Vladislav Shcherbin]

You misunderstood me.

I don't want to expose all from database, I only need to take selected columns and return as json.

The issue is with preload and select.

1. University |> where(slug: "slug") |> preload(:faculties) |> Repo.first

Gives me all fields from two tables, plus metadata. I need to select only needed fields, so:

2. University |> where(slug: "slug") |> preload(:faculties) |> select([u], %{title: u.fullTitle}) |> Repo.first

Error: 

** (Ecto.QueryError) the binding used in `from` must be selected in `select` when using `preload` in query

So, I can't select only needed columns.

3. University |> where(slug: "slug") |> preload(:faculties) |> select([:id, :slug, faculties: [:university_id, :shortTitle]]) |> Repo.first

Selects the needed fields, but gives me a struct with all fields, with populated ones that I listed in select function. Also has metadata.

I simply need a map:

%{

  fullTitle: "university title",

  faculties: [

    %{

      shortTitle: "faculty 1 title"

    }, 

    %{

      shortTitle: "faculty 1 title"

    }

]

But there is no way of creating a map with preload function that I am aware of. How this can be solved if I can't use a map with preload and select?

To unsubscribe from this group and stop receiving emails from it, send an email to elixir-ecto+unsubscribe@googlegroups.com.

[José Valim]

I don't want to expose all from database, I only need to take selected columns and return as json.

Why don't you decouple the code that takes the data from the database from the code that renders it?

def from_db_to_json do

  University

  |> where(slug: "slug")

  |> preload(:faculties)

  |> Repo.all

  |> Enum.map(&uni_to_json/1)

end

Unless you have really large columns (binary data), you are prematurely optimizing something that likely won't ever be a performance issue. And even if you have large columns, you can handle it in Ecto by defining different schemas that map to the same table.

If you really want to select only those fields then write a function that allows you to pass the same data to both select and the json renderer:

def from_db_to_json do

  select = [:id, :slug, faculties: [:id, :name]]

  University

  |> where(slug: "slug")

  |> preload(:faculties)

  |> select(^select)

  |> Repo.all

  |> to_json(select)

end

defp to_json(data, select) when is_list(data) do

  Enum.map(data, &to_json(&1, select))

end

defp to_json(data, select) do

  for field <- select, into: %{} do

    case field do

      {k, v} -> {k, to_json(Map.fetch!(data, k), select)} # association

      k -> {k, Map.fetch!(data, k)} # regular field

    end

  end

end

Maybe we could add a function that works similarly to take but returns only a map instead of a filtered struct which would make this a bit easier but I would never expect preload to work on such cases as we do need the schema metadata.

[Vladislav Shcherbin]

This is exactly what I was asking for in the 3 message of the topic.

A function, that will return a map with selected fields (I feel it natural to select only needed fields), so Poison can easily convert it to json.

Is there a possibility to have such a clean syntax somehow?

  University

  |> where(slug: "slug")

  |> preload(:faculties)

  |> select([:id, :slug, faculties: [:id, :name]])

  |> Repo.all

  |> Repo.to_json

end

[José Valim]

There is. :) Just do what I mentioned in the previous e-mail. In your repo, do something like this:

defmodule MyApp.Repo do

  def to_map(query, select) do

    query

    |> Ecto.Query.select(^select)

    |> Repo.all

    |> to_map(select)

  end

  defp to_map(data, select) when is_list(data) do

    Enum.map(data, &to_json(&1, select))

  end

  defp to_map(data, select) do

    for field <- select, into: %{} do

      case field do

        {k, v} -> {k, to_map(Map.fetch!(data, k), select)} # association

        k -> {k, Map.fetch!(data, k)} # regular field

      end

    end

  end

end

Now you can call it as:

  University

  |> where(slug: "slug")

  |> preload(:faculties)

  |> Repo.to_map([:id, :slug, faculties: [:university_id, :shortTitle]])

José Valim

www.plataformatec.com.br

Skype: jv.ptec

Founder and Director of R&D

[Vladislav Shcherbin]

Okay, here are another problems:

1) We hardcoded Repo.all here

def to_map(query, select) do

    query

    |> Ecto.Query.select(^select)

    |> Repo.all

    |> to_map(select)

  end

What about Repo.first, etc

2) When using preload, we need to specify the id.

For example, we have University and Faculties, we need to take university title and faculties titles.

So, with the proposed syntax it will be something like:

    University |> preload(:faculties) |> where(slug: "slug") |> Repo.to_map([:id, :title, faculties: [:title]])

Now we selected the id, but we don't need to send it to the client.

How can this be solved?

[Vladislav Shcherbin]

I don't know, if this can be done, but I wish it was as simple as

    University |> preload(:faculties) |> where(slug: "slug") |> select([:title, faculties: [:title]]) |> Repo.first |> Repo.to_map

    and

    universityDb = University |> preload(:faculties) |> where(slug: "slug") |> select([:title, faculties: [:title]]) |> Repo.first

    universityMap = universityDb |> Repo.map

This is a very needed function.

Any thoughts, if it is possible, Jose?