Search code examples
sqlelixirectocorrelated-subquery

Correlated subqueries using Ecto


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.


Solution

  • 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}