DISTINCT with multiple columns is not supported by MsSQL

I have written an ecto query to get only entries with different uuid but am getting this error

** (Ecto.QueryError) DISTINCT with multiple columns is not supported by MsSQL. Please use distinct(true) if you need distinct resultset in query:

how can i work around this

def interchange_batch() do

    Interchange

    |> where([a], a.status == ^"PENDING")

    |> join(:left, [a], b in Zrl.SystemUtilities.Wagon, on: a.wagon_id == b.id)

    |> join(:left, [a, b], c in Zrl.SystemUtilities.WagonType, on: b.wagon_type_id == c.id)

    |> join(:left, [a, b, _c], d in Zrl.Accounts.RailwayAdministrator, on: b.owner_id == d.id)

    |> join(:left, [a, b, _c, _d], e in Zrl.Accounts.RailwayAdministrator, on: a.adminstrator_id == e.id)

    |> join(:left, [a, _b, _c, _d, _e], f in Zrl.SystemUtilities.Station, on: a.interchange_point == f.id)

    |> join(:left, [a, _b, _c, _d, _e, _f], g in Zrl.Locomotives.Locomotive, on: a.locomotive_id == g.id)

    |> join(:left, [a, _b, _c, _d, _e, _f, _g], h in Zrl.SystemUtilities.Status, on: a.wagon_status_id == h.id)

    |> join(:left, [a, _b, _c, _d, _e, _f, _g, _h], i in Zrl.SystemUtilities.InterchangeFee, on: a.interchange_fee_id == i.id)

    |> join(:left, [a, _b, _c, _d, _e, _f, _g, _h, _i], j in  Zrl.SystemUtilities.Commodity, on: a.commodity_id == j.id)

    |> order_by([a, _b, _c, _d, _e, _f, _g, _h, _i, _j], desc: a.adminstrator_id, desc: a.entry_date, desc: a.exit_date, desc: a.direction, desc: a.commodity_id, desc: a.interchange_point)

    |> distinct([a, _b, _c, _d, _e, _f, _g, _h, _i, _j], a.uuid)

    |> select([a, b, c, d, e, f, g, h, i, j], %{

        id: a.id,

        uuid: a.uuid,

        wagon_id: a.wagon_id,

        commodity_id: a.commodity_id,

        wagon_owner: d.description,

        wagon_code: d.description,

        wagon_type: c.description,

        maker_id: a.maker_id,

        checker_id: a.checker_id,

        inserted_at: a.inserted_at,

        updated_at: a.updated_at,

        commodity: j.code,

        comment: a.comment,

        direction: a.direction,

        status: a.status,

        entry_date: a.entry_date,

        exit_date: a.exit_date,

        accumulative_days: a.accumulative_days,

        accumulative_amount: a.accumulative_amount,

        interchange_fee: a.interchange_fee,

        wagon_id: a.wagon_id,

        wagon: b.description,

        wagon_status_id: a.wagon_status_id,

        wagon_status: h.description,

        commodity_id: a.commodity_id,

        adminstrator_id: a.adminstrator_id,

        administrator: e.description,

        interchange_point: a.interchange_point,

        interchange_pt: f.description,

        interchange_fee_id: a.interchange_fee_id,

        locomotive_id: a.locomotive_id,

        locomotive: g.description

    })

    |> Repo.all()

  end
  1. Do not use LEFT JOINS if you want to make them unique anyway.
  2. For gods sake, use named joins or even from syntax.
from interchange in Interchange,
  where: interchange.status == "PENDING",
  # Look ma', no `left_join`s
  join: wagon in assoc(a, :wagon), # I assume that you have specified the relations in `Interchange` schema
  join: type in assoc(wagon, :wagon_type),
  join: owner in assoc(wagon, :owner),
  join: administrator in assoc(interchange, :administrator),
  join: interchange_point in assoc(interchange, :interchange_point),
  join: locomotive in assoc(interchange, :locomotive),
  join: status in assoc(interchange, :wagon_status),
  join: fee in assoc(interchange, :interchange_fee),
  join: commodity in assoc(interchange, :commodity),
  order_by: [
    desc: interchange.adminstrator_id,
    desc: interchange.entry_date,
    desc: interchange.exit_date,
    desc: interchange.direction,
    desc: interchange.commodity_id,
    desc: interchange.interchange_point
  ],
  select: %{
    # I lost my patience, but you should handle that one on your own
  }

EDIT:

BTW you should really check the plan for this query and think about creating some views in the DB to make it clearer for the developer, and maybe easier for the DB to handle.

2 Likes