SQL Query does not translate purely

Questions / Help
1

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. :slight_smile: :grinning:

2

However when I translate said query line by line to Ecto, the results are completely different to what I get with pure SQL.

It’d be helpful to know in what way they are different. Different ordering, grouping, etc.

3

What SQL is Ecto generating for this query? You can inspect it using Ecto.Adapters.SQL.to_sql/3

2 Likes
4

Thank you both, strangely, both SQL and Ecto are now returning the same results… I will confirm and report back in a few minutes. THANKS A BUNCH

5

My original question was incorrect. Ecto did translate my query without issues.

2 Likes