elixirectopostgrex

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

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.

Solution

  • 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).