S
Supabase•9mo ago
jarrettsorensen

Aggregate functions are not allowed in WHERE

I'm getting an error in my query shown below when trying to filter to get only the transactions that have a single allocation related to them 
  const { data, error } = await supabaseClient
    .from('transaction')
    .select('id, allocation(count)')
    .eq('allocation.count', 1);
  const { data, error } = await supabaseClient
    .from('transaction')
    .select('id, allocation(count)')
    .eq('allocation.count', 1);
I understand that this is not allowed for aggregate functions and that Postgres requires using the HAVING clause instead of the WHERE clause, but I don't know how to do that with the JS client. I looked through documentation, but couldn't find anything Any help would be greatly appreciated!
2 Replies
garyaustin
garyaustin•9mo ago
There may not be a way to do it with rpc call or a view. I just searched on count here: https://docs.postgrest.org/en/stable/ Which is the REST API Supabase uses. https://github.com/orgs/supabase/discussions/7629#discussioncomment-9667665 Says no, but look at the open issue... I just glanced that it was still open.
jarrettsorensen
jarrettsorensenOP•9mo ago
Dang..hopefully they add that sometime soon. Thanks for the info! I've managed to achieve what I want with a view 
CREATE OR REPLACE VIEW schema.view_name AS
SELECT
  t.transaction_id
FROM
  schema.transaction t
INNER JOIN
  schema.allocation a ON
  a.xpk_transaction_id = t.transaction_id
GROUP BY
  t.transaction_id,
  t.date
HAVING
  COUNT(a) = 1
ORDER BY
  t.date
;
CREATE OR REPLACE VIEW schema.view_name AS
SELECT
  t.transaction_id
FROM
  schema.transaction t
INNER JOIN
  schema.allocation a ON
  a.xpk_transaction_id = t.transaction_id
GROUP BY
  t.transaction_id,
  t.date
HAVING
  COUNT(a) = 1
ORDER BY
  t.date
;

Did you find this page helpful?