Help translating from SQL to Ecto

Questions / Help
1

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, jobs.name,
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 jobs.id = equipment_hr_km_entries.job_id
RIGHT JOIN (
			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 our_equipments.id = equipment_hr_km_entries.our_equipment_id
ORDER BY our_equipments.unit_number
1 Like
2

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: j.id == 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: oe.id == 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: j.name,
    hours: e.hours, kms: e.kms, inserted_at: e.inserted_at}

    Repo.all(query)
1 Like
3

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

4

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

5

That’s pretty cool, thanks!