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 columnsid
,profile_id
, andpost_id
- The
posts
table has columnsid
,post_like_counter
, andprofile_id
- The
profiles
table has columnsid
- Each profile can only like a post once
- You want to update the
post_like_counter
column in theposts
table with the count of likes for each post, but only for likes made by profiles that exist in theprofiles
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 :)
Top comments (0)