Conditions in expr (calculations)

I have a calculation (taken from the getting started guide):

  calculations do
    calculate :percent_open, :float, expr(open_tickets / total_tickets)
  end

  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

  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

  aggregates do
    count :total_tickets, :tickets

    count :open_tickets, :tickets do
      filter expr(status == :open)
    end
  end

Has anyone an idea?

23 Replies

ZachDaniel•3y ago

try this

count :total_tickets, :tickets do
  default 0
end

count :open_tickets, :tickets do
  filter expr(status == :open)
  default 0
end

count :total_tickets, :tickets do
  default 0
end

count :open_tickets, :tickets do
  filter expr(status == :open)
  default 0
end

scflodeOP•3y ago

that was quick 😄

ZachDaniel•3y ago

Perhaps a bug, because the default value for a count aggregate should already be 0

scflodeOP•3y ago

This did not change anything unfortunately.

ZachDaniel•3y ago

ah, well perhaps a good thing 😆 🤔

scflodeOP•3y ago

That's the output

scflodeOP•3y ago

the 0.0 ones should be around 0.75

ZachDaniel•3y ago

I wonder...what if you cast them to floats first

scflodeOP•3y ago

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]

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•3y ago

type(value, :float) when dividing them

scflodeOP•3y ago

That's the resulting query

ZachDaniel•3y ago

We do some excessive type casting, still need to clean that up, but it shouldn't be problematic

scflodeOP•3y ago

looks a bit massive as I have the archival extension enabled will try the casting

ZachDaniel•3y ago

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 I think that will do the trick

scflodeOP•3y ago

that was it

ZachDaniel•3y ago

Yeah, so its the difference between these two expressions:

SELECT 10 / 7
SELECT 10.0 / 7.0

SELECT 10 / 7
SELECT 10.0 / 7.0

Can you open an issue on ash_postgres?

scflodeOP•3y ago

  calculations do
    calculate :percent_open,
              :float,
              expr(if(total_tickets > 0, type(open_tickets / total_tickets, :float), 100.0))
  end

  calculations do
    calculate :percent_open,
              :float,
              expr(if(total_tickets > 0, type(open_tickets / total_tickets, :float), 100.0))
  end

ZachDaniel•3y ago

You shouldn't have had to do that

scflodeOP•3y ago

will do (maybe you need to clarify the description as I have no idea what the root cause is) thanks for this awesomely quick help! is this an AshPostgres or Ash.Query issue?

ZachDaniel•3y ago

Its an AshPostgres issue, that its treatment of the / operator is not the same as Elixir and to get the same treatment as elixir it has to first cast the left/right side to a float or a decimal

scflodeOP•3y ago

OK!

ZachDaniel•3y ago

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

scflodeOP•3y ago

Created a ticket: https://github.com/ash-project/ash_postgres/issues/128 Will close here. Thanks again!

GitHub

Treatment of the / operator in expr · Issue #128 · ash-project/as...

Describe the bug I have a calculation on a resource looking like this: calculations do calculate :percent_open, :float, expr(if(total_tickets > 0, open_tickets / total_tickets, 100.0)) end t...

Gaming

Programming

Conditions in expr (calculations)

Did you find this page helpful?