Supabase: count and discount raws from the likes table
Tags: supabase, postgres, sql, chatgp
Published: 2023-02-19 14:40:18

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();
```

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zlffpz6er9pjv3g85euw.png)

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/62ra1hujm5ue0n8gx0jz.png)

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();
```

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0zy83vr1japfgbw2ufkw.png)

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7myv0lm1f9j4s24sndko.png)

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