PostgreSQL does not support selecting all fields from without a schema

Phoenix Forum Questions / Help
, , ,
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