Trying to reduce schemaless query

Questions / Help
1

I have the following query which I am trying to reduce to apply filters but can’t manage to select the correct column.
My code is:

def list_our_equipments_preloaded_with_hrs_kms(criteria) when is_list(criteria) do

    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: %{id: oe.id, entry_id: e.id, unit_number: oe.unit_number, class_number: oe.class_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}

    Enum.reduce(criteria, query, fn

      {:unit_number, ""}, query ->
        query

      {:unit_number, unit_number}, query ->
        unit_number = "%#{unit_number}%"
        from q in query,
        where: ilike(q.unit_number, ^unit_number)

      {:type, ""}, query ->
        query

      {:type, type}, query ->
        type = "%#{type}%"
        from q in query,
        where:  ilike(q.type, ^type)

      {:make, ""}, query ->
        query

      {:make, make}, query ->
        make = "%#{make}%"
        from q in query,
        where:  ilike(q.make, ^make)

      {:model, ""}, query ->
        query

      {:model, model}, query ->
        model = "%#{model}%"
        from q in query,
        where:  ilike(q.model, ^model)
    end)

    |> Repo.all()

  end

And when I send any criteria to my function I get the following error, for some reason ecto is trying to select the wrong column:

[error] GenServer #PID<0.944.0> terminating
** (Postgrex.Error) ERROR 42703 (undefined_column) column e0.type does not exist

    query: SELECT o3."id", e0."id", o3."unit_number", o3."class_number", o3."type", o3."make", o3."model", o3."serial_number", j1."name", e0."hours", e0."kms", e0."inserted_at" FROM "equipment_hr_km_entries" AS e0 LEFT OUTER JOIN "jobs" AS j1 ON j1."id" = e0."job_id" RIGHT OUTER JOIN (SELECT se0."our_equipment_id" AS "our_equipment_id", max(se0."inserted_at") AS "max_inserted_at" FROM "equipment_hr_km_entries" AS se0 GROUP BY se0."our_equipment_id") AS s2 ON (e0."our_equipment_id" = s2."our_equipment_id") AND (e0."inserted_at" = s2."max_inserted_at") RIGHT OUTER JOIN "our_equipments" AS o3 ON o3."id" = e0."our_equipment_id" WHERE (e0."type" ILIKE $1) ORDER BY o3."unit_number"

    hint: Perhaps you meant to reference the column "o3.type".

In this example I tried sending a “type” criteria.

2

This notation uses positional bindings - so q will bind to the first table in query, equipment_hr_km_entries.

Consider using named bindings (the next section in that link) to make this simpler; otherwise the clauses in Enum.reduce will need to look something like from [_, _, _, table_wanted] in query.

2 Likes
3

Thank you very much, your suggestion of [_, _, _, q] solved my issue.