How can I do select count(*) with "group by" in supabase-js?

Question

How do I write a "group by" query in supabase-js?

In traditional SQL it would look like this:

SELECT COUNT(*), PLAYER FROM GAMES GROUP BY PLAYER

Using React and supabase-js, my code so far is like this:

    const { data, error } = await supabase
        .from('games')
        .select('count(*), player')

        // I need something like this: .groupBy('player')

How can I do group by?

Answer 1

A workaround is to create an sql view

create view player_games as SELECT COUNT(*) total, PLAYER FROM GAMES GROUP BY PLAYER

and you can then query player_games it as you would query a regular table

Answer 2

I believe this can only be done by using the RPC (PostgreSQL functions).

• Unfortunately, I think this is the only way so far - but maybe this can be changed in the future.
• With the current supabase-js SKD's you can only count rows with .match() method. But this will count occurrence of every row that matches the criteria—it won't group them (so you can't use aggregate functions on them after)