Supabase: count and discount raws from the likes table

Hi guys. Here's how I solved the count or discount problem of raws from the likes table with a unique post_id that matches a unique profile_id column.

This function is designed to be called as a trigger function, and it takes no parameters. Instead, it uses the NEW row variable to get the post_id value of the row that was just inserted or updated in the likes table. It then updates the post_like_counter column in the posts table using the post_id value from the NEW row.

Assuming the following:

• The likes table has columns id, profile_id, and post_id
• The posts table has columns id, post_like_counter, and profile_id
• The profiles table has columns id
• Each profile can only like a post once
• You want to update the post_like_counter column in the posts table with the count of likes for each post, but only for likes made by profiles that exist in the profiles table

Finally, the trigger function returns the NEW row to indicate that the trigger executed successfully. You can use this function as a trigger function to automatically update the post_like_counter column whenever a row is inserted or updated in the likes table.

You can use the following PostgreSQL query to achieve that:

-- Create function counter that increments post likes
CREATE OR REPLACE FUNCTION get_count()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
UPDATE posts
SET post_like_counter = (
SELECT count(*)
FROM (
SELECT DISTINCT ON (likes.profile_id) likes.post_id
FROM likes
WHERE likes.post_id = NEW.post_id
AND likes.profile_id IN (SELECT id FROM profiles)
) AS unique_likes
)
WHERE id = NEW.post_id;

RETURN NEW;
END;
$$;

-- Trigger the function every time a user is added/liked a post in the likes table
CREATE trigger on_post_user_liked
after insert on likes
for each row execute procedure public.get_count();

To create a function that decrements the post_like_counter counter, you can modify the existing function to subtract 1 from the post_like_counter instead of counting the likes.

Here's the function named get_uncount:

-- Create function counter that decrements the value in the 'post_like_counter' column in the 'posts' table
CREATE OR REPLACE FUNCTION decrement_count()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
UPDATE posts
SET post_like_counter = post_like_counter - 1
WHERE id = OLD.post_id;

RETURN OLD;
END;
$$;

-- Trigger the function every time a user unliked/ delited post in the `likes` table
CREATE TRIGGER on_post_user_unliked
AFTER DELETE ON likes
FOR EACH ROW EXECUTE PROCEDURE public.decrement_count();

Please let me know if there is a more elegant way to count the likes. Thanks :)