Conditions in expr (calculations)

scflode
2023-01-26

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!