Mysql - PHP - Checking a users posts and comments likes-CodePudding

Would anyone be able to recommend the best way to check if a user has liked a post or comment?

I am currently building a website that has similair features to Facebooks wall. My website will show a 'wall' of posts from people you follow that you can like or comment on.

For example, comments I have:

Comments table containing: id, user_id, text (plus other columns)
Comments Likes table: comment_id, user_id, created

This is the current query I use to get the comments and checks if user has liked it using an inner join on the likes table. It uses an IF() to return liked as either 1 or empty, which works fine:

SELECT comments.id, comments.post_id, comments.user_id, comments.reply_id, comments.created, comments.text, comments.likes, comments.replies, comments.flags, user.name, user.tagline, user.photo_id, user.photo_file, user.public_key,             
            **IF(likes.created IS NULL, '', '1') as 'liked'**
            FROM events_feed_comments AS comments              
            INNER JOIN user AS user ON comments.user_id = user.id
            **LEFT JOIN events_feed_comments_likes AS likes ON comments.id = likes.comment_id AND likes.user_id = :user**                      
            WHERE comments.post_id = :post_id AND comments.reply_id IS NULL                 
            ORDER BY comments.created DESC  
            LIMIT :limit OFFSET :offset

However, I realise that this will not be cacheable for anyone else as it contains the logged in users likes. There may end up being a lot of posts and so will need to introduce caching.

I am wondering what the best way to check the likes will be?

At the moment these are the solutions i can think of:

I could either select all the comments limited to say 30 at a time (cacheable) Then loop over each result doing a fetch/count query in the likes table to see if a user has liked it.
I could do a fetch from the likes table doing a where in clause using the returned 30 id results. Then do some sort of looping to see if the likes value matches the returned results.
Fetch all of the comments (cacheable), fetch all of a users likes (could be cacheable?), then do some looping / comparing to see if the values match.

I am just not sure what would be the best solution, or if there is some other recommended way to achieve this?

I am thinking the second approach may be best but i'm interested to see what you think?

Updates to show the table Create statements

CREATE TABLE `events_feed_comments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `post_id` int(11) NOT NULL,
  `reply_id` int(11) DEFAULT NULL,
  `text` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `likes` int(11) NOT NULL,
  `replies` int(11) NOT NULL,
  `flags` smallint(6) NOT NULL,
  `created` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci


CREATE TABLE `events_feed_comments_likes` (
  `comment_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `created` datetime NOT NULL,
  PRIMARY KEY (`comment_id`,`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci


CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `photo_id` int(11) DEFAULT NULL,
  `email` varchar(180) COLLATE utf8mb4_unicode_ci NOT NULL,
  `roles` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`roles`)),
  `password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(80) COLLATE utf8mb4_unicode_ci NOT NULL,
  `tagline` varchar(120) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `biography` varchar(2000) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `social` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`social`)),
  `specialties` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `available` smallint(6) NOT NULL DEFAULT 0,
  `theme` varchar(7) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `photo_file` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `following` int(11) NOT NULL,
  `followers` int(11) NOT NULL,
  `is_private` smallint(6) NOT NULL,
  `public_key` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `show_groups` smallint(6) NOT NULL,
  `show_feed` smallint(6) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQ_8D93D649E7927C74` (`email`),
  UNIQUE KEY `UNIQ_8D93D64966F9D463` (`public_key`),
  UNIQUE KEY `UNIQ_8D93D6497E9E4C8C` (`photo_id`),
  CONSTRAINT `FK_8D93D6497E9E4C8C` FOREIGN KEY (`photo_id`) REFERENCES `photos` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

CodePudding user response：

Instead of using If in your SQL query, consider using SQL Case to simplify the query.

    CASE --
           WHEN ---- THEN '----'
           
           ELSE '---'
    END

CodePudding user response：

For performance:

comments:  INDEX(post_id, reply_id, created)
likes:  INDEX(comment_id, user_id, created)

Those improvements may eliminate the need for "caching".

Please put "filtering" in WHERE, such as AND likes.user_id = :user** and put "relations" in ON. It can matter when using LEFT, and does help a human reading the query.

If events_feed_comments_likes is a many-to-many mapping table, you may want INDEX(user_id) also.