Conditions in expr (calculations)
scflode:
I have a
calculation
(taken from the getting started guide):
calculations do
calculate :percent_open, :float, expr(open_tickets / total_tickets)
end
The issue here is (it’s the representative assigned to a ticket) that this will error if a representative has no tickets.
I tried to solve this with the
if
function to no avail.
calculations do
calculate :percent_open, :float, expr(if(total_tickets > 0, open_tickets / total_tickets, 100.0))
end
This returns
100.0
for the
total_tickets == 0
case but
0.0
for the rest.
The resulting query also looks a bit weird (to me at least). It might be a problem that
open_tickets
and
total_tickets
themselves are aggregates.
aggregates do
count :total_tickets, :tickets
count :open_tickets, :tickets do
filter expr(status == :open)
end
end
Has anyone an idea?
ZachDaniel:
try this
count :total_tickets, :tickets do
default 0
end
count :open_tickets, :tickets do
filter expr(status == :open)
default 0
end
scflode:
that was quick 😄
ZachDaniel:
Perhaps a bug, because the default value for a count aggregate should already be
0
scflode:
This did not change anything unfortunately.
ZachDaniel:
ah, well perhaps a good thing 😆
ZachDaniel:
🤔
scflode:
That’s the output
scflode:
the 0.0 ones should be around
0.75
ZachDaniel:
I wonder…what if you cast them to floats first
scflode:
SELECT r0."id", r0."name", coalesce(s1."open_tickets"::bigint, $1::bigint)::bigint, coalesce(s1."total_tickets"::bigint, $2::bigint)::bigint, (CASE WHEN (coalesce(s1."total_tickets", $3::bigint)::bigint > $4::bigint)::boolean THEN (coalesce(s1."open_tickets", $5::bigint)::bigint / coalesce(s1."total_tickets", $6::bigint)::bigint)::float ELSE $7::float END)::float::float FROM "representatives" AS r0 LEFT OUTER JOIN LATERAL (SELECT coalesce(count(st0."id"::uuid) FILTER (WHERE ((st0."archived_at"::timestamp IS NULL) = $8) AND (st0."status"::varchar = $9::varchar)), $10::bigint)::bigint AS "open_tickets", coalesce(count(st0."id"::uuid) FILTER (WHERE (st0."archived_at"::timestamp IS NULL) = $11), $12::bigint)::bigint AS "total_tickets", st0."representative_id" AS "representative_id" FROM "public"."tickets" AS st0 WHERE ((st0."archived_at"::timestamp IS NULL) = $13) AND (r0."id" = st0."representative_id") GROUP BY st0."representative_id") AS s1 ON TRUE LIMIT $14 [0, 0, 0, 0, 0, 0, 100.0, true, :open, 0, true, 0, true, 10]
ZachDaniel:
type(value, :float)
ZachDaniel:
when dividing them
scflode:
That’s the resulting query
ZachDaniel:
We do some excessive type casting, still need to clean that up, but it shouldn’t be problematic
scflode:
looks a bit massive as I have the archival extension enabled
scflode:
will try the casting
ZachDaniel:
Yeah, casting before dividing may be the thing. I think perhaps a bug in ash_postgres, because we want it to beahave like elixir does, and so when dividing we should automatically cast the operators to floats
ZachDaniel:
I think that will do the trick
scflode:
that was it
ZachDaniel:
Yeah, so its the difference between these two expressions:
SELECT 10 / 7
SELECT 10.0 / 7.0
ZachDaniel:
Can you open an issue on ash_postgres?
scflode:
calculations do
calculate :percent_open,
:float,
expr(if(total_tickets > 0, type(open_tickets / total_tickets, :float), 100.0))
end
ZachDaniel:
You shouldn’t have had to do that
scflode:
will do (maybe you need to clarify the description as I have no idea what the root cause is)
scflode:
thanks for this awesomely quick help!
scflode:
is this an AshPostgres or Ash.Query issue?
ZachDaniel:
Its an
AshPostgres
issue, that its treatment of the
/
operator is not the same as Elixir
ZachDaniel:
and to get the same treatment as elixir it has to first cast the left/right side to a float
ZachDaniel:
or a decimal
scflode:
OK!
ZachDaniel:
The goal of Ash expressions is to be data layer agnostic and to have the expressions behave the same in each context, a sort of adaptability layer
scflode:
Created a ticket: https://github.com/ash-project/ash_postgres/issues/128 Will close here. Thanks again!