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}
                        If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With