0

I've been using the supabase-js for my application and I stumbled upon a problem. I want to get a table like this

Post TotalLikes
0001 3
0002 12
0005 0

The SQL-query for this would be:

SELECT Post, COUNT(*) as TotalLikes from "Votes"
where positive_vote = true
GROUP BY Post

I found this stackoverflow: How to get "COUNT(*)" in Supabase

however this does not help in this case.

Any help/suggestion is welcome

Tony
  • 5
  • 4
  • why didn't it help? errors, it seems quote straight forward – nbk Oct 08 '22 at 16:32
  • because here i'm using group by, in his example he just needs 1 row – Tony Oct 08 '22 at 16:34
  • yet another framework that has not enough functions, i believe you need https://supabase.com/docs/reference/javascript/rpc to do that – nbk Oct 08 '22 at 16:42
  • i know about RPC, however this method is limited and is not suitable for my project. – Tony Oct 08 '22 at 17:05
  • why you have a simple select, and supabase, can_'t make t another way. so basically swith framework or use qa procedure, i still don't know why it is not suitable, it gove you a wide range of functions that supabase doesn't have – nbk Oct 08 '22 at 17:08
  • because there is a "rate-limit" and also you can have only 10functions per supabase-project. Or I would need to pay 25$ a month, which is something im not aiming for right now – Tony Oct 08 '22 at 17:09
  • Tony I think you are confusing edge functions with postgres functions, there are no limits on postgres functions besides your database storage size. You can have more than 10 postgres function (`.rpc`) in your project without needing to pay $25 a month. – Andrew Smith Oct 09 '22 at 14:42
  • Can you update the question to include what your database schema would look like for this? – Andrew Smith Oct 10 '22 at 00:23

1 Answers1

0

You can create a view with this query and then run it using a normal select in the supabase-js library.

Create the view with the following code

CREATE VIEW the_name_of_the_view AS
SELECT Post, COUNT(*) as TotalLikes from "Votes"
where positive_vote = true
GROUP BY Post

And then call it with

await supabase.from('the_name_of_the_view').select('*')

Do note that you can't apply row level security (rls) on views in Postgres 14 and below.

Andrew Smith
  • 1,224
  • 6
  • 9