Using a table alias/query expression in an Ecto fragment?

Question

Starting with this query as the basis for an Ecto version:

 select folder_id, json_agg(p.*) 
 from folder_memberships inner join profiles p 
 on p.id=folder_memberships.profile_id 
 where folder_id in (1234) group by folder_id;

I've got this code:

 # ids=[1234] 
 from(p in Profile,
   join: link in FolderMembership, on: link.profile_id == p.id,
   select: [link.folder_id, fragment("json_agg(?) as members", p)],
   group_by: link.folder_id,
   where: link.folder_id in ^ids
 ) 
 |> Repo.all

And that gets me the following error:

== Compilation error on file lib/profile.ex ==
** (Ecto.Query.CompileError) variable `p` is not a valid query expression. 
Variables need to be explicitly interpolated in queries with ^
(ecto) expanding macro: Ecto.Query.select/3

I'm sure I'm missing elementary but I'm bonkered if I know what it is. I've tried a number of options, but all the examples I've been able to see do something like fragment("json_agg(?)", p.some_field), not p itself.

I thought `fragment("json_agg(?)", p.*)` would work but that escapes the `*` and ends up sending the value of the column named "*" to `json_agg`. I looked at all the tests in Ecto that include a `fragment` and didn't find any that pass a table name or `table.*`... I don't think this is possible with fragments right now. — Dogbert, Jun 28 '17 at 06:02
Yep, tried that with the same result. Ok, good to know its not just me. :) Will also give credit for answer to an alternate means of achieving the same result if you have any ideas! — Keith Gaddis, Jun 28 '17 at 15:53

score 2 · Accepted Answer · answered Jun 28 '17 at 20:09

The solution is not perfect because it requires listing all fields explicitly and also doesn't let you exclude fields from the resulting JSON.

 # ids=[1234] 
 from(p in Profile,
   join: link in FolderMembership, on: link.profile_id == p.id,
   select: [link.folder_id, fragment("json_agg((?, ?, ?)::profiles) as members", p.id, p.name, p.created_at)],
   group_by: link.folder_id,
   where: link.folder_id in ^ids
 ) 
 |> Repo.all

The number of question marks in json_agg should be exactly the same as the number of columns in the profiles table and also the order of columns in the table should correspond to the order of fragment arguments. I don't know your schema, so I "made up" 3 columns - I hope you get the idea.

I tried this approach myself on a simplified example (without a join). The source code of the app I used as a playground is there.

defmodule Magic do
  import Ecto.Query
  alias Badging.{Badge, Repo}

  @fields Badge.__schema__(:fields)
  @source Badge.__schema__(:source)
  @questions Enum.map_join(@fields, ", ", fn _ -> "?" end)
  @json_agg "json_agg((#{@questions})::#{@source})"

  def run do
    fields = Badge.__schema__(:fields)
    source = Badge.__schema__(:source)
    questions = Enum.map_join(fields, ", ", fn _ -> "?" end)
    json_agg = "json_agg((#{questions})::#{source})"

    from(
      b in Badge,
      select: [
        b.id,
        fragment(
          "json_agg((?, ?, ?, ?, ?, ?, ?, ?, ?)::badges)",
          b.id,
          b.identifier,
          b.subject,
          b.status,
          b.color,
          b.svg,
          b.svg_downloaded_at,
          b.inserted_at,
          b.updated_at
        )
      ],
      group_by: b.id
    ) |> Repo.all
  end
end

I also made an attempt to make it more flexible by using Badge.__schema__(:fields) and Badge.__schema__(:source), but stumbled upon the inability of fragment to accept variable number of arguments.

This is what I got so far:

defmodule Magic do
  import Ecto.Query
  alias Badging.{Badge, Repo}

  fields = Badge.__schema__(:fields)
  source = Badge.__schema__(:source)
  questions = Enum.map_join(fields, ", ", fn _ -> "?" end)
  @json_agg "json_agg((#{questions})::#{@source})"

  def run do
    from(
      b in Badge,
      select: [
        b.id,
        fragment(
          @json_agg,
          field(b, :id), # or just b.id
          b.identifier,
          b.subject,
          b.status,
          b.color,
          b.svg,
          b.svg_downloaded_at,
          b.inserted_at,
          b.updated_at
        )
      ],
      group_by: b.id
    ) |> Repo.all
  end
end

I think technically it is possible to rely on __schema__(:fields) instead of listing all fields explicitly. The list of fields is known at compile time. I'm just not as good at macros in Elixir/Ecto to do it (yet).

Giving you credit for the answer even though its not quite the answer I was looking for—word from the ecto mailing list is that it should be supported, though it doesn't work at present. i actually spent about 5 hours yesterday trying to come up with a PR to support it but my metaprogramming skills in elixir aren't quite up to the task yet. — Keith Gaddis, Jun 29 '17 at 21:25

Using a table alias/query expression in an Ecto fragment?

1 Answers1