How would you go about writing a correlated subquery in Ecto
?
For example, what would be the equivalent of the SQL SELECT
query:
--Heaviest Lemmings per Group
SELECT ld.group_name, ld.id, ld.name, ld.weight
FROM lemming_data ld
WHERE weight = (
SELECT max(weight)
FROM lemming_data
WHERE group = ld.group_name
);
in Ecto
syntax? I'm not sure how to refer to the outer query's group_name
.
Co-related subqueries are not possible in Ecto without using fragments. So it would be something like:
from ld in "lemming_data",
where: ld.weight in fragment("(SELECT max(weight) FROM lemming_data WHERE group = ?)", ld.group_name),
select: ld # or {ld.group_name, ld.id, ld.name, ld.weight}
or:
from ld in "lemming_data",
where: fragment("? = (SELECT max(weight) FROM lemming_data WHERE group = ?)", ld.weight, ld.group_name),
select: ld # or {ld.group_name, ld.id, ld.name, ld.weight}