Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 443
category Avatar asked Mar 07 '23 03:03

category


1 Answers

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}
like image 89
José Valim Avatar answered Mar 20 '23 08:03

José Valim