Calling aggregate functions with supabase-js

[kennethacohen]

I'm new to Supabase, so apologies if I'm missing something here. I'm coming from Object/Knex, just having trouble translating what should be a simple query.

Simplified schema as follows:

authors
---
id
...other stuff

articles
---
id
author_id
...other stuff

payment_intents
---
id
customer_id
article_id
amount_cents
...other stuff

const { data, error, count } = await db
  .from("payment_intents")
  .select(`
    *,
    articles(*)
  `, { count: "exact" })
  .filter("articles.author_id", "eq", id)

I'm trying to answer:

• how many PaymentIntents does a given author have?
• what's the total amount_cents for a given author?

When running the count above, all PaymentIntent records are included, and the filter is just removing the nested Article object...which isn't ideal.

Appreciate any help here! Thanks.

[steve-chavez]

You can resort to SQL functions if your query cannot be expressed purely in supabase-js.

Right now, calling the sum aggregate function and doing an inner join(embedding is done through a left join) is not possible with supabase-js directly. These capabilities are being worked on though.

Answering your questions:

how many PaymentIntents does a given author have?

create or replace function "TotalPaymentIntents"(author_id int)
returns bigint as $$
  select
    count(pi)
  from articles a
  join payment_intents pi on pi.article_id = a.id
  where a.author_id = $1;
$$ language sql stable;

// Then you can call it through supabase-js like:
const { data, error } = await db
  .rpc("TotalPaymentIntents", { author_id: 1} )

what's the total amount_cents for a given author?

create or replace function "TotalAmountCents"(author_id int)
returns float as $$
  select
    sum(pi.amount_cents)
  from articles a
  join payment_intents pi on pi.article_id = a.id
  where a.author_id = $1;
$$ language sql stable;

// Then you can call it through supabase-js like:
const { data, error } = await db
  .rpc("TotalAmountCents", { author_id: 1} )

[kennethacohen]

Got it - thanks for the detailed response!!

[christiangenco]

Aggregation functions and inner joins in the Supabase JS SDK are currently supported!

To run a query that counts the PaymentIntents and totals PaymentIntents.amount_cents for a given author your query might look like:

const { data, error } = await supabase
  .from('payment_intents')
  .select('count, amount_cents.sum()', {
    count: 'exact'
  })
  .eq('articles.author_id', authorId)

To use an inner join so payment intents without an article aren't included you would use the !inner hint:

const { data, error, count } = await db
  .from("payment_intents")
  .select(`
    *,
    articles!inner(*)
  `, { count: "exact" })
  .filter("articles.author_id", "eq", id)