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.