The problem is I introduced a 2nd composeable query that uses
where along with
or_where and now Ecto is composing it with a bunch of other composed queries, but it’s putting the
OR in an unexpected spot when it composes everything together.
The parenthesis are ending up in an unfavorable spot in the query and I don’t know where to being to solve this problem.
Here’s the fully composed function:
def eligible_discounts(package_id, code, select_code \\ false) do __MODULE__ |> for_package(package_id) |> with_discount() |> enabled() |> usage_count_less_than_usage_limit() |> maybe_code(code) |> maybe_select_code(select_code) end
And it is being composed of these functions:
def for_package(query \\ __MODULE__, package_id) do from(q in query, where: is_nil(q.package_id), or_where: q.package_id == ^package_id ) end def with_discount(query \\ __MODULE__) do from(q in query, join: d in assoc(q, :discount)) end def enabled(query \\ __MODULE__) do from([_q, d] in query, where: d.is_enabled) end def usage_count_less_than_usage_limit(query \\ __MODULE__) do from([q, d] in query, where: d.usage_limit == 0, or_where: q.usage_count < d.usage_limit ) end def maybe_code(query \\ __MODULE__, code) do if code do from([_q, d] in query, where: d.code == ^String.upcase(code)) else query end end def maybe_select_code(query \\ __MODULE__, code) do if code do from([_q, d] in query, select: d.code) else from([_q, d] in query, preload: [discount: d]) end end
usage_count_less_than_usage_limit bit is the newest edition to the pipeline, and it’s causing issues because the final query being executed is:
SELECT d0."id", d0."usage_count", d0."inserted_at", d0."updated_at", d0."discount_id", d0."package_id", d1."id", d1."code", d1."is_enabled", d1."usage_limit", d1."inserted_at", d1."updated_at" FROM "discount_packages" AS d0 INNER JOIN "discounts" AS d1 ON d1."id" = d0."discount_id" WHERE ((((d0."package_id" IS NULL)) OR (d0."package_id" = $1)) AND (d1."is_enabled") AND (d1."usage_limit" = 0)) OR (d0."usage_count" < d1."usage_limit") 
Ecto is slapping this to the end of the query
AND (d1."usage_limit" = 0)) OR (d0."usage_count" < d1."usage_limit"). It’s attaching the
usage_limit = 0 condition in the
AND near the end, but then it finishes it off with
OR (d0."usage_count" < d1."usage_limit").
That’s not the intended result because that returns back discount codes even with invalid package_ids (as seen with 9999999999999 example input) since the last condition is true and the final
OR isn’t in the correct parenthesis. I’d really like the usage_count function’s
or_where to be isolated to only its composed bits, not the entire query.
How do you do that? I can’t find an example of this anywhere.