Ecto Subquery for getting N item of Lowest amount grouped by category

Questions / Help
1

Hi all, i was trying to implement these PostgreSQL query for getting N item with lowest price for each category

the reference SQL is like

SELECT tmp.category_id, tmp.product.*
FROM (
SELECT row_number() over (partition by product.category_id order by product.price ASC), product.* as sort_number
FROM product) tmp
WHERE tmp.sort_number = 1

i was thinking that Ecto Subquery could help with this, but i got stuck, getting error

    ** (Ecto.Query.CompileError) `from` in query expression specified 3 binds but query contains 1 binds

with following code

query =
from(b in Product,
select: [b, fragment(“row_number() over (partition by ? order by ? ASC)”, b.category_id, b.price)]
)
query =
from([b, sort_number] in subquery(query),
where: sort_number == 1,
select: {b.category_id, b}
)
Repo.all(query)

How to resolve it? is this correct way of using subquery?

Thanks for helping !

2

Here is a solution for a very similar topic

1 Like
3

@fuelen Thanks, it point to working solution, though i am not sure if adding virtual field in ecto schema for temporary value used in SQL calculation is good habit, is there a better way?

4

I don’t think so. You have to use plain structure. A simple map can be used instead of a struct, so you don’t put a virtual field to the schema, but actually it is the same approach.

5

Here’s another solution that i found, turn out there is restriction on what can be selected as part of the subquery, and nested map is part of them. So what it can do is split query to two part, first part only get identifier of the object and second part to get fully fleshed object from identifier

query =
from(b in Product,
select: %{
  id: b.id, 
  sort_number: fragment(“row_number() over (partition by ? order by ? ASC)”, b.category_id, b.price)
})

query =
from(result in subquery(query),
where: result.sort_number == 1,
select: result.id
)

ids = Repo.all(query)

query = from(b in Product, where: b.id in ^ids, select: {b.category_id, b})

Repo.all(query)

but @fuelen solution is much simpler, so that;s what i do