How to select id with max date group by category in Ecto query with Phoenix?

Question

For an example, I would like to select id with max date group by category, the result is: 7, 2, 6

id  category  date
1   a         2013-01-01
2   b         2013-01-03
3   c         2013-01-02
4   a         2013-01-02
5   b         2013-01-02
6   c         2013-01-03
7   a         2013-01-03
8   b         2013-01-01
9   c         2013-01-01

This is the SQL I think can work:

SELECT * FROM Table1 t1
JOIN 
(
   SELECT category, MAX(date) AS MAXDATE
   FROM Table1
   GROUP BY category
) t2
ON T1.category = t2.category
AND t1.date = t2.MAXDATE

But how to translate that into a query on Ecto?

Answer 1

An issue with many frameworks is that they cannot capture all the complexities of a SQL SELECT statement. The easiest solution: wrap your complex query in a view:

CREATE VIEW my_complex_view AS
  SELECT * FROM Table1 t1
  JOIN (
    SELECT category, MAX(date) AS maxdate
    FROM Table1
    GROUP BY category) t2
  ON t1.category = t2.category AND t1.date = t2.maxdate;

Now you have a simple query (SELECT * FROM my_complex_view) which any decent framework can easily handle.

Answer 2

You can use subquery function

subquery = from t in "Table1"
      |> select([t], %{categoty: t.category, max_date: max(t.date)})
      |> group_by([t], t.category)

from t in "Table1"
      |> join(:inner, [u], t in subquery(subquery), t.category == u.category and t.max_date == u.date)
      |> Repo.all