How to run an aggregate query in supabase JS

In the docs, it mentions that we can make select queries in JavaScript.

However, I want to run an aggregate query to fetch the total credits of an organization along with its id, name, and other details.

I have the following SQL function for calculating the total credits:

CREATE OR REPLACE FUNCTION get_organization_credits(org_id bigint) 
RETURNS INTEGER 
SECURITY DEFINER 
AS $$
BEGIN
  RETURN (
    SELECT COALESCE(SUM(credits_remaining), 0)
    FROM credit_sources
    WHERE organization_id = org_id
  );
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION get_organization_credits(org_id bigint) 
RETURNS INTEGER 
SECURITY DEFINER 
AS $$
BEGIN
  RETURN (
    SELECT COALESCE(SUM(credits_remaining), 0)
    FROM credit_sources
    WHERE organization_id = org_id
  );
END;
$$ LANGUAGE plpgsql;

But how can I retrieve the organization details (id, name, etc.) along with the remaining credits in a single query?

Here are my table definitions for reference:

CREATE TABLE IF NOT EXISTS organizations (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name TEXT NOT NULL DEFAULT 'My Team'::TEXT
);

CREATE TABLE credit_sources (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  organization_id BIGINT NOT NULL REFERENCES organizations (id) ON DELETE CASCADE,
  credits_remaining INTEGER NOT NULL DEFAULT 0 CHECK (credits_remaining >= 0)
);

CREATE TABLE IF NOT EXISTS organizations (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name TEXT NOT NULL DEFAULT 'My Team'::TEXT
);

CREATE TABLE credit_sources (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  organization_id BIGINT NOT NULL REFERENCES organizations (id) ON DELETE CASCADE,
  credits_remaining INTEGER NOT NULL DEFAULT 0 CHECK (credits_remaining >= 0)
);

I tried the following, but it doesn't work:

await supabase.from('organizations').select(`
  id,
  name,
  remaining_credits: get_organization_credits(id)
`);

await supabase.from('organizations').select(`
  id,
  name,
  remaining_credits: get_organization_credits(id)
`);

How to run an aggregate query in supabase JS

CREATE OR REPLACE FUNCTION get_organization_credits(org_id bigint) 
RETURNS INTEGER 
SECURITY DEFINER 
AS $$
BEGIN
  RETURN (
    SELECT COALESCE(SUM(credits_remaining), 0)
    FROM credit_sources
    WHERE organization_id = org_id
  );
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION get_organization_credits(org_id bigint) 
RETURNS INTEGER 
SECURITY DEFINER 
AS $$
BEGIN
  RETURN (
    SELECT COALESCE(SUM(credits_remaining), 0)
    FROM credit_sources
    WHERE organization_id = org_id
  );
END;
$$ LANGUAGE plpgsql;

But how can I retrieve the organization details (id, name, etc.) along with the remaining credits in a single query?

Here are my table definitions for reference:

CREATE TABLE IF NOT EXISTS organizations (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name TEXT NOT NULL DEFAULT 'My Team'::TEXT
);

CREATE TABLE credit_sources (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  organization_id BIGINT NOT NULL REFERENCES organizations (id) ON DELETE CASCADE,
  credits_remaining INTEGER NOT NULL DEFAULT 0 CHECK (credits_remaining >= 0)
);

CREATE TABLE IF NOT EXISTS organizations (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name TEXT NOT NULL DEFAULT 'My Team'::TEXT
);

CREATE TABLE credit_sources (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  organization_id BIGINT NOT NULL REFERENCES organizations (id) ON DELETE CASCADE,
  credits_remaining INTEGER NOT NULL DEFAULT 0 CHECK (credits_remaining >= 0)
);

I tried the following, but it doesn't work:

await supabase.from('organizations').select(`
  id,
  name,
  remaining_credits: get_organization_credits(id)
`);

await supabase.from('organizations').select(`
  id,
  name,
  remaining_credits: get_organization_credits(id)
`);

How to run an aggregate query in supabase JS

Similar Threads

How to run an aggregate query in supabase JS

Similar Threads

Similar Threads

Similar Threads