Help needed translating this SQL query to Ecto Query (join and aggregation)

I have this SQL query which returns also rows for books who have no records at all:

select max(r.id) as record_id, b.id as book_id, r.due_for_return, r.returned_at
FROM books b
left join records r
on b.id = r.book_id
group by b.id, r.due_for_return, r.returned_at

I have tried this Ecto query but it returns only the books which have at least on associated record in the records table. How to make it return all books even those without any records?

My attempt:

Repo.all(from books in Mango.Books.Book,
join: records in assoc(books, :records),
group_by: [records.book_id, books.id, records.due_for_return, records.returned_at],
select: [max(records.id), books.id, records.due_for_return, records.returned_at])

Thank you.

Solution appears to be to use left_join instead of join in Ecto Query.

2 Likes

And just to document: the default Ecto join is an inner join in SQL.

2 Likes