Help translating from SQL to Ecto

I am having trouble translation the following SQL query into Ecto. Any assistance will be immensely appreciated.

SELECT our_equipments.unit_number, our_equipments.type, our_equipments.make, our_equipments.model, our_equipments.serial_number,,
equipment_hr_km_entries.hours, equipment_hr_km_entries.kms, equipment_hr_km_entries.inserted_at
FROM equipment_hr_km_entries
LEFT JOIN jobs on = equipment_hr_km_entries.job_id
			SELECT our_equipment_id, MAX(inserted_at) AS max_inserted_at
			FROM equipment_hr_km_entries
			GROUP BY our_equipment_id
			) grouped
ON equipment_hr_km_entries.our_equipment_id = grouped.our_equipment_id
AND equipment_hr_km_entries.inserted_at = grouped.max_inserted_at
RIGHT JOIN our_equipments ON = equipment_hr_km_entries.our_equipment_id
ORDER BY our_equipments.unit_number
1 Like

I think I reached my solution with:

max_dates = from e in "equipment_hr_km_entries",
    select: %{our_equipment_id: e.our_equipment_id, max_inserted_at: max(e.inserted_at)},
    group_by: e.our_equipment_id

    query = from e in "equipment_hr_km_entries",
    left_join: j in "jobs", on: == e.job_id,
    right_join: md in subquery(max_dates),
    on: e.our_equipment_id == md.our_equipment_id and e.inserted_at == md.max_inserted_at,
    right_join: oe in "our_equipments", on: == e.our_equipment_id,
    order_by: oe.unit_number,
    select: %{unit_number: oe.unit_number, type: oe.type, make: oe.make, model: oe.model, serial_number: oe.serial_number, job_name:,
    hours: e.hours, kms: e.kms, inserted_at: e.inserted_at}

1 Like

Thanks for sharing your solution! Good job! :heart:

A bit of hack, but i have written a way to transform arbitrary SQL to Ecto.Query at Hacking Ecto: Constructing Ecto Query from Raw SQL - DEV Community

That’s pretty cool, thanks!