count totalLikes per post with supabase-js

Issue

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

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

Solution

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.

Answered By – Andrew Smith

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0