PostgreSQL does not support selecting all fields from without a schema

Phoenix Forum Questions / Help
, , ,
1

TL;DR The problem I’m trying to solve is analogous to building an Ecto.Query, say, for posts that includes n number of latest associated comments for each.

I realize this has been discussed several times already, but I still have not found a clear solution:

As an example, given the following:

defmodule Comment do
  schema "comments" do
    belongs_to(:post, Post)
  end
end

defmodule Post do
  schema "posts" do
    has_many(:comments, Comment)    
  end

  defmacrop latest_comments(post_id, limit \\ 5) do
    quote do
      fragment(
        "select * from comments
        where comments.post_id = ?
        order by comments.inserted_at desc nulls last
        limit ?",
        unquote(post_id),
        unquote(limit)
      )
    end
  end

  def with_latest_comments(queryable) do
    queryable
    |> join(:left_lateral, [p], c in latest_comments(p.id))
    |> select_merge([p, c], %{comments: c})
  end
end

I think this is so close to a solution, but it raises this error:

PostgreSQL does not support selecting all fields from without a schema. Please specify a schema or specify exactly which fields you want to select in query

If I wrap c with map or struct e.g.:

|> select_merge([p, c], %{comments: struct(c, [:id, :post_id])})

The following error is raised:

it is not possible to return a map/struct subset of a fragment, you must explicitly return the desired individual fields in query

I see this was discussed here #1592 Ecto.QueryError) PostgreSQL requires a schema module when using selector “u0” but none was given.

The following suggestions were mentioned, either of which I think would work great!

We could also support struct(u, [:foo, :bar, :baz], MyModule)
…
or even struct(u, MyModule) if there are all the columns since the module contains the column names

But alas, that issue is from 2016 and has long been closed. :confused:

Any other ideas? Am I missing something obvious?

Thanks!

1 Like
2

Edit: Full example of a working lateral join.

iex(1)> alias MusicDB.{Repo,Album,Track}
[MusicDB.Repo, MusicDB.Album, MusicDB.Track]
iex(2)> import Ecto.Query
Ecto.Query
iex(3)> compact_rest = fn
...(3)>   {nil,nil,nil,[]} ->
...(3)>     []
...(3)>   {_, album, tracks, rest} = init ->
...(3)>     [Map.put(album, :tracks, :lists.reverse(tracks)) | rest]
...(3)> end
#Function<6.127694169/1 in :erl_eval.expr/5>
iex(4)> next_acc = fn
...(4)>   (%{id: id} = a, t, {id, album, tracks, rest}) ->
...(4)>     {id, album, [t|tracks], rest}
...(4)>   (%{id: id} = a, t, acc) ->
...(4)>     {id, a, [t], compact_rest.(acc)}
...(4)> end
#Function<18.127694169/3 in :erl_eval.expr/5>
iex(5)> row_to_acc = fn (%{album: album, track: track}, acc) -> next_acc.(album, track, acc) end
#Function<12.127694169/2 in :erl_eval.expr/5>
iex(6)> make_result = fn rows ->
...(6)>   rows
...(6)>   |> List.foldl({nil,nil,nil,[]}, row_to_acc)
...(6)>   |> compact_rest.()
...(6)>   |> :lists.reverse()
...(6)> end
#Function<6.127694169/1 in :erl_eval.expr/5>
iex(7)> album_ids = [1,2]
[1, 2]
iex(8)> album_query = from(a in Album, [where: a.id in ^album_ids])
#Ecto.Query<from a in MusicDB.Album, where: a.id in ^[1, 2]>
iex(9)> query = from(q in subquery(album_query), [
...(9)>   left_lateral_join: t in fragment("SELECT * FROM tracks AS t WHERE t.album_id = ? ORDER BY t.inserted_at DESC NULLS LAST LIMIT ?", q.id,3), on: true,
...(9)>   select: %{album: %{id: q.id, artist_id: q.artist_id, title: q.title}},
...(9)>   select_merge: %{track: %{id: t.id, index: t.index, title: t.title, duration: t.duration}}
...(9)> ])
#Ecto.Query<from a in subquery(from a in MusicDB.Album,
  where: a.id in ^[1, 2]),
 left_join_lateral: f in fragment("SELECT * FROM tracks AS t WHERE t.album_id = ? ORDER BY t.inserted_at DESC NULLS LAST LIMIT ?", a.id, 3),
 on: true,
 select: %{album: %{id: a.id, artist_id: a.artist_id, title: a.title}, track: %{id: f.id, index: f.index, title: f.title, duration: f.duration}}>
iex(10)> results = Repo.all(query)

18:31:36.947 [debug] QUERY OK db=2.5ms
SELECT s0."id", s0."artist_id", s0."title", f1."id", f1."index", f1."title", f1."duration" FROM (SELECT a0."id" AS "id", a0."title" AS "title", a0."inserted_at" AS "inserted_at", a0."updated_at" AS "updated_at", a0."artist_id" AS "artist_id" FROM "albums" AS a0 WHERE (a0."id" = ANY($1))) AS s0 LEFT OUTER JOIN LATERAL (SELECT * FROM tracks AS t WHERE t.album_id = s0."id" ORDER BY t.inserted_at DESC NULLS LAST LIMIT 3) AS f1 ON TRUE [[1, 2]]
[
  %{
    album: %{artist_id: 1, id: 1, title: "Kind Of Blue"},
    track: %{duration: 481, id: 5, index: 5, title: "Flamenco Sketches"}
  },
  %{
    album: %{artist_id: 1, id: 1, title: "Kind Of Blue"},
    track: %{duration: 693, id: 4, index: 4, title: "All Blues"}
  },
  %{
    album: %{artist_id: 1, id: 1, title: "Kind Of Blue"},
    track: %{duration: 327, id: 3, index: 3, title: "Blue In Green"}
  },
  %{
    album: %{artist_id: 1, id: 2, title: "Cookin' At The Plugged Nickel"},
    track: %{duration: 1061, id: 10, index: 5, title: "No Blues"}
  },
  %{
    album: %{artist_id: 1, id: 2, title: "Cookin' At The Plugged Nickel"},
    track: %{duration: 754, id: 9, index: 4, title: "Miles"}
  },
  %{
    album: %{artist_id: 1, id: 2, title: "Cookin' At The Plugged Nickel"},
    track: %{duration: 896, id: 8, index: 3, title: "Walkin'"}
  }
]
iex(11)> result = make_result.(results)
[
  %{
    artist_id: 1,
    id: 1,
    title: "Kind Of Blue",
    tracks: [
      %{duration: 481, id: 5, index: 5, title: "Flamenco Sketches"},
      %{duration: 693, id: 4, index: 4, title: "All Blues"},
      %{duration: 327, id: 3, index: 3, title: "Blue In Green"}
    ]
  },
  %{ 
    artist_id: 1,
    id: 2,
    title: "Cookin' At The Plugged Nickel",
    tracks: [
      %{duration: 1061, id: 10, index: 5, title: "No Blues"},
      %{duration: 754, id: 9, index: 4, title: "Miles"},
      %{duration: 896, id: 8, index: 3, title: "Walkin'"}
    ]
  }
]
iex(12)>

Raw SQL that served as the template:

SELECT a1.id AS album_id, a1.artist_id, a1.title AS album_title, t1.id AS track_id, t1.index, t1.title, t1.duration FROM
  (SELECT id, artist_id, title FROM albums WHERE id in (1,2)) a1
  LEFT JOIN LATERAL
  (SELECT id, index, title, duration FROM tracks WHERE album_id = a1.id ORDER BY inserted_at DESC NULLS LAST LIMIT 3) t1
  ON true;
3

Correction: Caveat

The issue is that the conditions apply to all the records returned by that single preload query - not just each single association.

preload query.

Example:

iex(1)> alias MusicDB.{Repo,Album,Track}
[MusicDB.Repo, MusicDB.Album, MusicDB.Track]
iex(2)> import Ecto.Query
Ecto.Query
iex(3)> album_id = 2
2
iex(4)> tracks_query = from(t in Track, [
...(4)>    order_by: fragment("? DESC NULLS LAST", t.inserted_at),
...(4)>    limit: 3
...(4)> ])
#Ecto.Query<from t in MusicDB.Track,
 order_by: [asc: fragment("? DESC NULLS LAST", t.inserted_at)], limit: 3>
iex(5)> query = from(a in Album, [
...(5)>   preload: [tracks: ^tracks_query],
...(5)>   where: a.id == ^album_id
...(5)> ])
#Ecto.Query<from a in MusicDB.Album, where: a.id == ^2,
 preload: [tracks: #Ecto.Query<from t in MusicDB.Track, order_by: [asc: fragment("? DESC NULLS LAST", t.inserted_at)], limit: 3>]>
iex(6)> Repo.all(query)

12:43:52.217 [debug] QUERY OK source="albums" db=2.2ms decode=1.7ms
SELECT a0."id", a0."title", a0."inserted_at", a0."updated_at", a0."artist_id" FROM "albums" AS a0 WHERE (a0."id" = $1) [2]
 
12:43:52.223 [debug] QUERY OK source="tracks" db=2.5ms
SELECT t0."id", t0."title", t0."duration", t0."index", t0."number_of_plays", t0."inserted_at", t0."updated_at", t0."album_id", t0."album_id" FROM "tracks" AS t0 WHERE (t0."album_id" = $1) ORDER BY t0."album_id", t0."inserted_at" DESC NULLS LAST LIMIT 3 [2]
[
  %MusicDB.Album{
    __meta__: #Ecto.Schema.Metadata<:loaded, "albums">,
    artist: #Ecto.Association.NotLoaded<association :artist is not loaded>,
    artist_id: 1,
    genres: #Ecto.Association.NotLoaded<association :genres is not loaded>,
    id: 2,
    inserted_at: ~N[2018-06-16 20:29:41.476625],
    title: "Cookin' At The Plugged Nickel",
    tracks: [
      %MusicDB.Track{
        __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
        album: #Ecto.Association.NotLoaded<association :album is not loaded>,
        album_id: 2,
        duration: 1061,
        duration_string: nil,
        id: 10,
        index: 5,
        inserted_at: ~N[2018-06-16 20:29:41.480612],
        number_of_plays: 0,
        title: "No Blues",
        updated_at: ~N[2018-06-16 20:29:41.480618]
      },
      %MusicDB.Track{
        __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
        album: #Ecto.Association.NotLoaded<association :album is not loaded>,
        album_id: 2,
        duration: 754,
        duration_string: nil,
        id: 9,
        index: 4,
        inserted_at: ~N[2018-06-16 20:29:41.480045],
        number_of_plays: 0,
        title: "Miles",
        updated_at: ~N[2018-06-16 20:29:41.480051]
      },
      %MusicDB.Track{
        __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
        album: #Ecto.Association.NotLoaded<association :album is not loaded>,
        album_id: 2,
        duration: 896,
        duration_string: nil,
        id: 8,
        index: 3,
        inserted_at: ~N[2018-06-16 20:29:41.479460],
        number_of_plays: 0,
        title: "Walkin'",
        updated_at: ~N[2018-06-16 20:29:41.479465]
      }
    ],
    updated_at: ~N[2018-06-16 20:29:41.476631]
  }
]
iex(7)>
1 Like