Fetch out the 2nd Element using Ecto.Repo?

Currently, my database is:- Postgres. So, how can I fetch the 2nd element from it or you can say fetch out entry present at the 2nd index. Note- To do using- Ecto.Repo

probably the equivalent to sql limit 1, offset 1

Considering your previous topic I’m sensing a possible XY Problem.

It’s the nature of queries that the result records aren’t ordered unless you take explicit steps to impose an order on them based on the data in the record.

For example:

defmodule MusicDB.Track do
  use Ecto.Schema
  alias MusicDB.Album

  schema "tracks" do
    field(:title, :string)
    field(:duration, :integer)
    field(:duration_string, :string, virtual: true)
    field(:index, :integer)
    field(:number_of_plays, :integer)
    timestamps()

    belongs_to(:album, Album)
  end
end
  • all tracks from an album can be ordered by :index.
  • if you want a track with a specific index you query that index.

So the question is - what is it about your data that makes a particular item come at a particular position in the result set?

If there is a definitive answer for that then use exactly those properties to query it directly.

Unlikely to give predictable results when data is being inserted/deleted between queries.

2 Likes

Like my database is
id Name Marks
10 Yash 32
19 Ram 89

How can I get the first entry from the database? Suppose I don’t know what will be id.

The results don’t have a predictable order unless the query specifies an order by id, name, or marks.

1 Like

OH…Thanks

What do you mean by “first” entry? Records in the database don’t have an order by default.

To get a “first” element, you have to sort (order) the elements first. For example sort them by last name.

1 Like

Just one potential problem scenario with offsets:

  • track id=21 is queried as the “first” element
  • a new track is inserted which based on the specified ordering makes it “first”
  • now querying the “second” element track id=21 is returned again

Similarly deletion between queries could lead to skipped records:

  • track id=21 is queried as the “first” element
  • track id=21 is deleted - track id=22 becomes “first”
  • now querying the “second” element track id=23 is returned - skipping track id=22

offset/limit are often used for pagination but duplicating or missing an entry between pages is an accepted side effect of concurrent inserts and deletes.

# file: music_db/priv/repo/playground.exs
#
# http://www.pragmaticprogrammer.com/titles/wmecto
# https://pragprog.com/titles/wmecto/source_code
# http://media.pragprog.com/titles/wmecto/code/wmecto-code.zip
#
# pg_ctl -D /usr/local/var/postgres start
# mix format ./priv/repo/playground.exs
# mix run ./priv/repo/playground.exs
#

defmodule AppInfo do
  def string() do
    Application.loaded_applications()
    |> Enum.map(&to_app_keyword/1)
    |> Enum.sort_by(&map_app_name/1)
    |> Enum.map_join(", ", &app_keyword_to_string/1)
  end

  defp to_app_keyword({app, _, vsn}),
    do: {app, vsn}

  defp app_keyword_to_string({app, vsn}),
    do: "#{app}: #{vsn}"

  defp map_app_name({app, _}),
    do: app
end

defmodule Playground do
  import Ecto.Query
  alias MusicDB.Repo
  alias MusicDB.Track

  def changeset(track, params \\ %{}) do
    permitted = [:album_id, :index, :title, :duration]

    track
    |> Ecto.Changeset.cast(params, permitted)
  end

  def new_track() do
    changeset(
      %Track{},
      %{album_id: 4, index: 0, title: "Nonesense", duration: 99}
    )
  end

  def album_tracks(album_id) do
    from(t in Track,
      where: t.album_id == ^album_id,
      order_by: [asc: t.index]
    )
  end

  def track_by_index(album_id, index) do
    # no order_by needed
    from(t in Track,
      where: t.album_id == ^album_id,
      where: t.index == ^index
    )
  end

  def track_by_offset(album_id, offset) do
    # forcing order by index
    from(t in Track,
      where: t.album_id == ^album_id,
      order_by: [asc: t.index],
      offset: ^offset,
      limit: 1
    )
  end

  def play do
    album_id = 4
    index = 2
    offset = 1

    # --- All tracks
    IO.puts("Results ordered by index")

    results =
      album_tracks(album_id)
      |> Repo.all()

    IO.puts("#{inspect(results)}")

    # --- track with index "2"
    IO.puts("Result by index")

    results =
      track_by_index(album_id, index)
      |> Repo.all()

    IO.puts("#{inspect(results)}")

    # --- track offset 1 from the beginning
    IO.puts("Result by offset")

    results =
      track_by_offset(album_id, offset)
      |> Repo.all()

    IO.puts("#{inspect(results)}")

    # --- insert a track
    IO.puts("Adding new track")
    {:ok, track} = Repo.insert(new_track())

    IO.puts("Result by index AFTER INSERT")

    results =
      track_by_index(album_id, index)
      |> Repo.all()

    IO.puts("#{inspect(results)}")

    IO.puts("Result by offset AFTER INSERT")

    results =
      track_by_offset(album_id, offset)
      |> Repo.all()

    IO.puts("#{inspect(results)}")

    # --- clean up track
    IO.puts("Cleaning up track")
    {:ok, _} = Repo.delete(track)
  end
end

IO.puts(AppInfo.string())
Playground.play()
$ mix run ./priv/repo/playground.exs
Compiling 16 files (.ex)
Generated music_db app
asn1: 5.0.8, compiler: 7.3.2, connection: 1.0.4, crypto: 4.4.2, db_connection: 2.0.5, decimal: 1.6.0, ecto: 3.0.6, ecto_sql: 3.0.5, elixir: 1.8.1, hex: 0.19.0, inets: 7.0.7, jason: 1.1.2, kernel: 6.3.1, logger: 1.8.1, mariaex: 0.9.1, mix: 1.8.1, music_db: 0.1.0, postgrex: 0.14.1, public_key: 1.6.5, ssl: 9.2.2, stdlib: 3.8.1, telemetry: 0.3.0

Results ordered by index
11:33:54.388 [debug] QUERY OK source="tracks" db=5.0ms decode=0.6ms queue=1.1ms
SELECT t0."id", t0."title", t0."duration", t0."index", t0."number_of_plays", t0."inserted_at", t0."updated_at", t0."album_id"
FROM "tracks" AS t0
WHERE (t0."album_id" = $1) ORDER BY t0."index" [4]
[
%MusicDB.Track{__meta__: #Ecto.Schema.Metadata<:loaded, "tracks">, album: #Ecto.Association.NotLoaded<association :album is not loaded>, album_id: 4, duration: 204, duration_string: nil, id: 21, index: 1, inserted_at: ~N[2019-05-03 00:16:32], number_of_plays: 0, title: "Come Rain Or Come Shine", updated_at: ~N[2019-05-03 00:16:32]},
%MusicDB.Track{__meta__: #Ecto.Schema.Metadata<:loaded, "tracks">, album: #Ecto.Association.NotLoaded<association :album is not loaded>, album_id: 4, duration: 360, duration_string: nil, id: 22, index: 2, inserted_at: ~N[2019-05-03 00:16:32], number_of_plays: 0, title: "Autumn Leaves", updated_at: ~N[2019-05-03 00:16:32]},
%MusicDB.Track{__meta__: #Ecto.Schema.Metadata<:loaded, "tracks">, album: #Ecto.Association.NotLoaded<association :album is not loaded>, album_id: 4, duration: 277, duration_string: nil, id: 23, index: 3, inserted_at: ~N[2019-05-03 00:16:32], number_of_plays: 0, title: "Witchcraft", updated_at: ~N[2019-05-03 00:16:32]},
%MusicDB.Track{__meta__: #Ecto.Schema.Metadata<:loaded, "tracks">, album: #Ecto.Association.NotLoaded<association :album is not loaded>, album_id: 4, duration: 297, duration_string: nil, id: 24, index: 4, inserted_at: ~N[2019-05-03 00:16:32], number_of_plays: 0, title: "When I Fall In Love", updated_at: ~N[2019-05-03 00:16:32]},
%MusicDB.Track{__meta__: #Ecto.Schema.Metadata<:loaded, "tracks">, album: #Ecto.Association.NotLoaded<association :album is not loaded>, album_id: 4, duration: 195, duration_string: nil, id: 25, index: 5, inserted_at: ~N[2019-05-03 00:16:32], number_of_plays: 0, title: "Peri's Scope", updated_at: ~N[2019-05-03 00:16:32]},
%MusicDB.Track{__meta__: #Ecto.Schema.Metadata<:loaded, "tracks">, album: #Ecto.Association.NotLoaded<association :album is not loaded>, album_id: 4, duration: 276, duration_string: nil, id: 26, index: 6, inserted_at: ~N[2019-05-03 00:16:32], number_of_plays: 0, title: "What Is This Thing Called Love?", updated_at: ~N[2019-05-03 00:16:32]},
%MusicDB.Track{__meta__: #Ecto.Schema.Metadata<:loaded, "tracks">, album: #Ecto.Association.NotLoaded<association :album is not loaded>, album_id: 4, duration: 309, duration_string: nil, id: 27, index: 7, inserted_at: ~N[2019-05-03 00:16:32], number_of_plays: 0, title: "Spring Is Here", updated_at: ~N[2019-05-03 00:16:32]},
%MusicDB.Track{__meta__: #Ecto.Schema.Metadata<:loaded, "tracks">, album: #Ecto.Association.NotLoaded<association :album is not loaded>, album_id: 4, duration: 297, duration_string: nil, id: 28, index: 8, inserted_at: ~N[2019-05-03 00:16:32], number_of_plays: 0, title: "Someday My Prince Will Come", updated_at: ~N[2019-05-03 00:16:32]},
%MusicDB.Track{__meta__: #Ecto.Schema.Metadata<:loaded, "tracks">, album: #Ecto.Association.NotLoaded<association :album is not loaded>, album_id: 4, duration: 325, duration_string: nil, id: 29, index: 9, inserted_at: ~N[2019-05-03 00:16:32], number_of_plays: 0, title: "Blue In Green", updated_at: ~N[2019-05-03 00:16:32]}
]

Result by index
11:33:54.392 [debug] QUERY OK source="tracks" db=0.7ms queue=0.9ms
SELECT t0."id", t0."title", t0."duration", t0."index", t0."number_of_plays", t0."inserted_at", t0."updated_at", t0."album_id"
FROM "tracks" AS t0
WHERE (t0."album_id" = $1) AND (t0."index" = $2) [4, 2]
[
%MusicDB.Track{__meta__: #Ecto.Schema.Metadata<:loaded, "tracks">, album: #Ecto.Association.NotLoaded<association :album is not loaded>, album_id: 4, duration: 360, duration_string: nil, id: 22, index: 2, inserted_at: ~N[2019-05-03 00:16:32], number_of_plays: 0, title: "Autumn Leaves", updated_at: ~N[2019-05-03 00:16:32]}
]

Result by offset
11:33:54.394 [debug] QUERY OK source="tracks" db=0.7ms queue=1.0ms
SELECT t0."id", t0."title", t0."duration", t0."index", t0."number_of_plays", t0."inserted_at", t0."updated_at", t0."album_id"
FROM "tracks" AS t0
WHERE (t0."album_id" = $1) ORDER BY t0."index" LIMIT 1 OFFSET $2 [4, 1]
[
%MusicDB.Track{__meta__: #Ecto.Schema.Metadata<:loaded, "tracks">, album: #Ecto.Association.NotLoaded<association :album is not loaded>, album_id: 4, duration: 360, duration_string: nil, id: 22, index: 2, inserted_at: ~N[2019-05-03 00:16:32], number_of_plays: 0, title: "Autumn Leaves", updated_at: ~N[2019-05-03 00:16:32]}
]

Adding new track
11:33:54.406 [debug] QUERY OK db=7.3ms queue=0.8ms
INSERT INTO "tracks" ("album_id","duration","index","title","inserted_at","updated_at")
VALUES ($1,$2,$3,$4,$5,$6)
RETURNING "id" [4, 99, 0, "Nonesense", ~N[2019-05-17 15:33:54], ~N[2019-05-17 15:33:54]]

Result by index AFTER INSERT
11:33:54.409 [debug] QUERY OK source="tracks" db=2.8ms
SELECT t0."id", t0."title", t0."duration", t0."index", t0."number_of_plays", t0."inserted_at", t0."updated_at", t0."album_id"
FROM "tracks" AS t0
WHERE (t0."album_id" = $1) AND (t0."index" = $2) [4, 2]
[
%MusicDB.Track{__meta__: #Ecto.Schema.Metadata<:loaded, "tracks">, album: #Ecto.Association.NotLoaded<association :album is not loaded>, album_id: 4, duration: 360, duration_string: nil, id: 22, index: 2, inserted_at: ~N[2019-05-03 00:16:32], number_of_plays: 0, title: "Autumn Leaves", updated_at: ~N[2019-05-03 00:16:32]}
]

Result by offset AFTER INSERT
11:33:54.411 [debug] QUERY OK source="tracks" db=1.2ms
SELECT t0."id", t0."title", t0."duration", t0."index", t0."number_of_plays", t0."inserted_at", t0."updated_at", t0."album_id"
FROM "tracks" AS t0
WHERE (t0."album_id" = $1)ORDER BY t0."index" LIMIT 1 OFFSET $2 [4, 1]
[
%MusicDB.Track{__meta__: #Ecto.Schema.Metadata<:loaded, "tracks">, album: #Ecto.Association.NotLoaded<association :album is not loaded>, album_id: 4, duration: 204, duration_string: nil, id: 21, index: 1, inserted_at: ~N[2019-05-03 00:16:32], number_of_plays: 0, title: "Come Rain Or Come Shine", updated_at: ~N[2019-05-03 00:16:32]}
]

Cleaning up track
11:33:54.416 [debug] QUERY OK db=2.8ms queue=1.0ms
DELETE FROM "tracks" WHERE "id" = $1 [37]
$
1 Like