I have the following SQL Query which returns the results I want and expect:
select unit_number, type, make, model, serial_number, jobs.name as job_name, kms, MAX(equipment_hr_km_entries.inserted_at) as max_date from equipment_hr_km_entries right join jobs on jobs.id = equipment_hr_km_entries.job_id right join our_equipments on our_equipments.id = equipment_hr_km_entries.our_equipment_id group by 1, 2, 3, 4, 5, 6, 7 order by unit_number ASC
However when I translate said query line by line to Ecto, the results are completely different to what I get with pure SQL. I have no idea why this is happening. My Ecto query is the following:
query = from e in "equipment_hr_km_entries", right_join: j in "jobs", on: j.id == e.job_id, right_join: oe in "our_equipments", on: oe.id == e.our_equipment_id, group_by: [oe.unit_number, oe.type, oe.make, oe.model, oe.serial_number, j.name, e.kms], order_by: oe.unit_number, select: [oe.unit_number, oe.type, oe.make, oe.model, oe.serial_number, j.name, e.kms, max(e.inserted_at)] Repo.all(query)
I will greatly appreciate any guidance as to what I am doing wrong.