I’m having a bit of trouble trying to get a query to work in Ecto that uses window functions.
I have a BlogPost model that has a user_id column and a published_at column.
I need to make a query that returns the newest published blog posts, but only the single newest one for each user, so that one user cannot just post a bunch of blog posts and have them all shown in the list of recent blog posts.
If I were to build the query in raw sql, I would do something like this:
SELECT * FROM ( SELECT row_number() over (partition by blog_posts.user_id order by blog_posts.published_at desc) row_num, blog_posts.* FROM blog_posts WHERE blog_posts.published = TRUE ) AS sq WHERE row_num = 1 ORDER BY published_at DESC
I do however not want to do a raw query, as I would need to use this query in combination with preload and maybe even combine it with other queries.
The closest I’ve gotten to work is this:
from bp in (Helheim.BlogPost |> published), join: rn in fragment("SELECT id, row_number() OVER (PARTITION BY blog_posts.user_id ORDER BY blog_posts.published_at DESC) as row_number FROM blog_posts WHERE blog_posts.published = TRUE"), where: rn.id == bp.id and rn.row_number == 1, order_by: [desc: [bp.published_at, bp.inserted_at]], limit: ^limit
This generates a query like this:
SELECT b0."id", b0."title", b0."body", b0."visitor_count", b0."comment_count", b0."published", b0."published_at", b0."inserted_at", b0."updated_at", b0."user_id" FROM "blog_posts" AS b0 INNER JOIN (SELECT id, row_number() OVER (PARTITION BY blog_posts.user_id ORDER BY blog_posts.published_at DESC) as row_number FROM blog_posts WHERE blog_posts.published = TRUE) AS f1 ON TRUE WHERE (b0."published" = TRUE) AND ((f1."id" = b0."id") AND (f1."row_number" = 1)) ORDER BY ARRAY[b0."published_at",b0."inserted_at"] DESC LIMIT 8
This query runs just fine if I run it directly in the database, but when trying to run it through Phoenix and Ecto, I get this error:
ERROR 42601 (syntax_error): syntax error at or near "."
So, is this even the right way to make a query like this in Ecto? And if it is, then what am I doing wrong to have the query fail?
:ecto, "2.1.6" :phoenix, "1.3.0"