acrolink

How to use PostgreSQL's DISTINCT ON in Ecto Query

I have this SQL query:

SELECT DISTINCT ON (b.id) b.id as book_id, r.id as record_id, r.due_for_return, r.returned_at
FROM books b
left join records r
on b.id = r.book_id
order by b.id, r.id DESC

Any idea how to convert it to an Ecto Query, especially the DISTINCT ON part ? Thank you.

11 comments

/phoenix #ecto #sql #postgresql

15 10677 11

2019-07-12 15:30:30 UTC

Marked As Solved

peerreynders

How to use PostgreSQL’s DISTINCT ON in Ecto Query

    create table(:books) do
      add :title, :string
    end

    create table(:records) do
      add :book_id, :id
      add :due_for_return, :date
    end

# lib/books.ex
defmodule Books do
  import Ecto.Query;
  alias Books.{Repo}

  def init do
    Repo.insert_all(
      "books", [
        [title: "One"],
        [title: "Two"],
        [title: "Three"],
        [title: "Four"]
      ]
    )
    Repo.insert_all(
      "records", [
        [book_id: 1, due_for_return: ~D[2017-11-01]],
        [book_id: 1, due_for_return: ~D[2017-12-01]],
        [book_id: 1, due_for_return: ~D[2018-01-01]],
        [book_id: 2, due_for_return: ~D[2018-01-02]],
        [book_id: 2, due_for_return: ~D[2018-02-02]],
        [book_id: 3, due_for_return: ~D[2018-03-03]],
      ]
    )
  end

  def query() do
    "books"
    |> join(:left, [b], r in "records", b.id == r.book_id)
    |> select([b,r], %{
         title: b.title,
         book_id: b.id,
         record_id: max(r.id)
       })
    |> group_by([b], [b.title, b.id])
    |> subquery()
    |> join(:left, [d,r],
         r in "records", d.book_id == r.book_id and d.record_id == r.id
       )
    |> select([d,r], %{
         book_id: d.book_id,
         title: d.title,
         due: r.due_for_return
       })
    |> Repo.all()
  end
end

iex(1)> Books.init()

07:15:35.715 [debug] QUERY OK db=7.9ms
INSERT INTO "books" ("title") VALUES ($1),($2),($3),($4) ["One", "Two", "Three", "Four"]
 
07:15:35.720 [debug] QUERY OK db=1.3ms
INSERT INTO "records" ("book_id","due_for_return") VALUES ($1,$2),($3,$4),($5,$6),($7,$8),($9,$10),($11,$12) [1, {2017, 11, 1}, 1, {2017, 12, 1}, 1, {2018, 1, 1}, 2, {2018, 1, 2}, 2, {2018, 2, 2}, 3, {2018, 3, 3}]
{6, nil}
iex(2)> Books.query()

07:15:43.693 [debug] QUERY OK db=3.1ms
SELECT s0."book_id", s0."title", r1."due_for_return" FROM (SELECT b0."title" AS "title", b0."id" AS "book_id", max(r1."id") AS "record_id" FROM "books" AS b0 LEFT OUTER JOIN "records" AS r1 ON b0."id" = r1."book_id" GROUP BY b0."title", b0."id") AS s0 LEFT OUTER JOIN "records" AS r1 ON (s0."book_id" = r1."book_id") AND (s0."record_id" = r1."id") []
[
  %{book_id: 1, due: {2018, 1, 1}, title: "One"},
  %{book_id: 2, due: {2018, 2, 2}, title: "Two"},
  %{book_id: 3, due: {2018, 3, 3}, title: "Three"},
  %{book_id: 4, due: nil, title: "Four"}
]
iex(3)>

PostgreSQL GROUP BY

Post #7

Also Liked

snnowwy

Hi acrolink et al,
I know I’m a bit late to this party but I was reading this for inspiration on a similar issue today and I ended up just using a fragment like so:

books_query =
  from(
    b in Mango.Books.Book,
    left_join: r in Mango.Records.Record,
    on: r.book_id == b.id,
    select: %{
      :book_id => fragment("distinct on (?) ?", b.id, b.id),
      :record_id => r.id,
      :due_for_return => r.due_for_return,
      :returned_at => r.returned_at
    },
    order_by: [asc: b.id, desc: r.due_for_return]
  )

My particular case didn’t allow for the methods outlined above so I gave that a go and it worked.

Also, I’ve sorted by due_for_return instead of id just in case something crazy happened and your record.id’s ended up out of sync with due_for_return.

I’d be interested to see how this pans out for you.

Post #12

peerreynders

You could also move the query into a view. That way the Ecto select is extremely simple and you can change the query inside the database if needed later.

PostgreSQL Views

Post #11

peerreynders

Is it slow now?

I don’t know anything about PostgreSQL’s performance characteristics. And ultimately you would have to benchmark the query on your configuration.

There may be cases where an active column can be the best solution but there is the trade off of the required additional update the needs to happen within the same transaction as the insert (both of which affect the index).

Another alternative is to formulate the query around due_for_return and put an index on that but I don’t think it’s going to perform any better than using the primary key index.

Post #9