Ecto DISTINCT ON and NULLS LAST

Questions / Help
1

I need to use DISTINCT ON on my query as such:

from(
  q in query,
  distinct: [desc: q.some_count, asc: q.id],
  order_by: q.name
)

And it kinda works with 1 caveat where records with some_count = NULL will be at the top, naturally I wanted to add NULLS LAST to my query but I tried many different ways that don’t work (at least on Ecto 2.x). For instance, I tried the following:

from(
  q in query,
  distinct: [q.some_count, q.id],
  order_by: [fragment("? DESC NULLS LAST", q.some_count)]
)

This doesn’t work as Ecto generate this SQL query ORDER BY q.some_count, q.id, q.some_count DESC NULLS LAST where it is first sorted by q.some_count in ASC order. I also tried

from(
  q in query,
  distinct: [desc: fragment("? NULLS LAST", q.some_count), asc: q.id],
  order_by: q.name
)

But it also doesn’t work, AFAIK and according to this ticket here https://github.com/elixir-ecto/ecto/issues/1475 it’ll just throw a SQL error.

Any ideas?

P/S: Basically I want to achieve something like distinct: [desc_nulls_last: q.some_count] on Ecto 2

1 Like
2

I have a temporary workaround as such:

from(
  q in query,
  distinct: [desc: fragment("coalesce(?, 0)", q.some_count), asc: q.id],
  order_by: q.name
)

forcing the null count to become 0 and it’ll achieve the “NULLS LAST” effect. But I would appreciate any comments as well.

3

If you’re using the new new Ecto 3 release there is now support for NULLS LAST. Checkout the “Other Changes” section of this blog post http://blog.plataformatec.com.br/2018/10/a-sneak-peek-at-ecto-3-0-query-improvements-part-2/ for the details.

3 Likes
4

Yeah I saw the change hence my question is actually targeted at Ecto 2.x . When I upgrade sometime in the future I will definitely try out desc_nulls_last on distinct :smiley: