Ecto selecting virtual field on preload child record

     schema "parent" do
       field :name, :string
       has_many :children, Child
     end

    schema "child" do
      field :name, :string
      field :uppername, :string, virtual: true
      belongs_to :parent, Parent
    end

Want to run equivalent SQL to

    select p.*, c.*, upper(c.name) as uppername
    from parent p
    inner join child c on c.parent_id = p.id;

And have it populate a struct with the child records nested

%Parent{
  id: 1,
  name: "p1",
  children: [
    %Child{
      id: 1,
      name: "name one",
      uppername: "NAME ONE",
      parent_id: 1
    },
    %Child{
      id: 2,
      name: "name two",
      uppername: "NAME TWO",
      parent_id: 1
    }
  ]
}

This gives me a flat structure

 q = from p in Parent,
   join: c in assoc(c, :children),
   select:  %{id: p.id, name: p.name, children: %{c | uppername: fragment("upper", c.name)}}
 Repo.all(q)

And adding in preload

 q = from p in Parent,
   join: c in assoc(c, :children),
   preload: [children: c],
   select:  %{id: p.id, name: p.name, children: %{c | uppername: fragment("upper", c.name)}}
 Repo.all(q)

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

And I’m a bit clueless from this point on.

I guess I can just rollup the flattened structure myself, but that seems to be defeating the point.

Note the ‘upper’ is just an example for demonstration of question. I assume I could do something trivial in a simpler way (something like @property in python).

You should be able to use a query with preload to customize how the child is selected.

https://hexdocs.pm/ecto/Ecto.Query.html#preload/3-preload-queries

children =
  from c in Child,
    select: %{c | uppername: fragment("upper(?)", c.name)}

query =
  from p in Parent,
    preload: [children: ^children]

Repo.all(query)

[
  %Example.Parent{
    __meta__: #Ecto.Schema.Metadata<:loaded, "parent">,
    id: 21,
    name: "p1",
    children: [
      %Example.Child{
        __meta__: #Ecto.Schema.Metadata<:loaded, "child">,
        id: 42,
        name: "p1 name one",
        uppername: "P1 NAME ONE",
        parent_id: 21,
        parent: #Ecto.Association.NotLoaded<association :parent is not loaded>
      },
      %Example.Child{
        __meta__: #Ecto.Schema.Metadata<:loaded, "child">,
        id: 43,
        name: "p1 name two",
        uppername: "P1 NAME TWO",
        parent_id: 21,
        parent: #Ecto.Association.NotLoaded<association :parent is not loaded>
      }
    ]
  }
]

1 Like

I think your issue is this should be join: c in assoc(**p**, :children)

1 Like

thanks @benstepp the query in the preload worked
It does end up with two queries being executed, but that works for now.

@joey_the_snake thanks… yes that was an issue too