4

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?

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
vikingsteve
  • 38,481
  • 23
  • 112
  • 156

2 Answers2

2

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

Xavier
  • 1,157
  • 9
  • 29
1

I believe this can only be done by using the RCP (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)
Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Jakub Szlaur
  • 1,852
  • 10
  • 39