Cannot assign to column using Ecto

Questions / Help
1

Hi all. I am trying to do the following query:

from(p in Post, select: %Post{title: p.title, row_number: fragment("dense_rank() over (partition by p0.visible order by p0.id desc)")}, where: p.id in [1,2]) |> Repo.all()

which results in the following SQL query:

SELECT p0."title", dense_rank() over (partition by p0.visible order by p0.id desc) FROM "posts" AS p0 WHERE (p0."id" IN (1,2)) []

row_number is a column in the Post table but I was wondering why SQL isn’t assigning the fragment to the row_number column.

For context, I am trying to do the following query:
from(p in Post, select: %Post{title: p.title, row_number: fragment("dense_rank() over (partition by p0.visible order by p0.id desc)")}, where: p.id in [1,2] and p.row_number > 1) |> Repo.all()
which runs the following SQL command:
SELECT p0."title", dense_rank() over (partition by p0.visible order by p0.id desc) FROM "posts" AS p0 WHERE (p0."id" IN (1,2) AND (p0."row_number" > 1)) []
but I cannot filter by row_number because the fragment doesn’t get assigned. Thanks.

2

:wave:

Sorry for being off-topic, but I’d suggest against hard coding table aliases into fragments (p0.visible, p0.id). What if the SQL translation in ecto/postgrex changes?

Try this instead:

from p in Post,
  select: %Post{
    title: p.title,
    row_number: fragment("dense_rank() OVER (PARTITION BY ? ORDER BY ? DESC)", p.visible, p.id)
  },
  where: p.id in [1, 2]

As a quick work-around you can filter on your fragment. And for the ease of use, you can put that fragment in a private macro.

It might look something like the following (not sure it would work, see my note below)

defmacrop windowing(agg, partition_by, order_by) do
  quote do
    fragment("? OVER (PARTITION BY ? ?)",
      unquote(agg),
      unquote(partition_by),
      unquote(order_by))
  end
end

defmacrop dense_rank do
  quote do 
    fragment("dense_rank()")
  end
end

def your_function(...) do
  query = 
    from p in Post,
      select: %Post{
        title: p.title,
        # can put windowing(dense_rank(), p.visible, ^order_by(Post, [p], desc: p.id)) into it's own macro if it's used often
        row_number: windowing(dense_rank(), p.visible, ^order_by(Post, [p], desc: p.id))
      },
      where: p.id in [1, 2] and p.row_number > windowing(dense_rank(), p.visible, ^order_by(Post, [p], desc: p.id))
  # ...
end

Or maybe use a subquery.

SELECT title, row_number
FROM 
  (
    SELECT title,
           dense_rank() OVER (PARTITION BY visible ORDER BY id DESC) as row_number
    FROM posts
  )
WHERE id IN (1, 2) AND (row_number > 1)

In ecto:

defmacrop windowing(agg, partition_by, order_by) do
  quote do
    fragment("? OVER (PARTITION BY ? ?)",
      unquote(agg),
      unquote(partition_by),
      unquote(order_by))
  end
end

defmacrop dense_rank do
  quote do 
    fragment("dense_rank()")
  end
end

def your_function(...) do
  subselect = # can put where p.id in [1, 2] clause here, actually, might inprove performance (not sure, try benchmarking / `EXPLAIN ANALYZE`ing)
    select(Post, [p], %Post{
      title: p.title,
      row_number: windowing(dense_rank(), p.visible, ^order_by(Post, [p], desc: p.id))
    })

  query =
    from(
      ss in subquery(subselect),
      where: ss.id in [1, 2],
      where: ss.row_number > 1,
      select: %Post{
        title: ss.title,
        row_number: ss.row_number
      }
    )
end

BTW, have you tried running the SQL you want? It might work not quite as you expect it to. Sub-selecting is a safer choice in this case, I think, since windowing functions are not allowed in WHERE clauses (they are run after the filtering is done).

1 Like
3

Thanks, yep hard coding that is definitely not a good idea.

I tried your workaround but I got the error (windowing_error): window functions are not allowed in WHERE.

Subquery works:

 q = from(p in Post, select: %Post{title: p.title, id: p.id, row_number: fragment("dense_rank() over (order by ? desc)", p.id)})
from(p in subquery(q), where: p.row_number < 3, preload: [:tags]) |> Repo.all()

I was originally trying to preload the tags using a join so that I can run this using just one SQL query. But I think I’ll just put that off for now. Thanks for your help!

1 Like