CODEWITHSUNDEEP
phpdatabaserestapiarchitecture
TheSteed
TheSteed

Reputation: 830

Highlighting users liked posts on a Twitter like feed

I'm building an app with a very similar UI as Twitter, including the ability to like/favourite posts, using react for the client and an API endpoint.

Posts come from a variety of sources including external ones, so for the purpose of this question lets say Youtube videos and articles.

As the posts are coming from a variety of sources, not necessarily stored in the local db these "likes" are stored across two tables.

Table 1 is a "Like Resource" table which stores the post type e.g. 'video' and it's identifier e.g. '2DFlghK1iuZz', so something like the following:

TABLE `likes_resources` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` varchar(255),
  `identifier` varchar(255) 
)

Table 2 is the actual like table, which stores against the resource and the user, roughly the following:

TABLE `likes` (
  `like_resource_id` int(11),
  `user_id` int(11)
)

This set up works fine for liking and unliking posts as well as listing likes by post or by user.

What I'm struggling with is the most efficient way of highlighting which posts have been liked by a user when viewing the main feed, which infinitely scrolls much like Twitters feed, without pulling back a potentially huge list of all posts liked by a user and comparing that to the posts currently visible in the feed.

My initial thoughts are to post a list of currently visible feed items to an API endpoint, which then returns a list of all those that have been liked from that list so they can be highlighted as such.

Does this seem like a reasonable way of achieving this functionality, or is there a better way?

Upvotes: 0

Views: 51

Answers (1)

Rob Conklin
Rob Conklin

Reputation: 9473

Left join is your friend.

Select likes_resource.*, likes.user_id FROM likes_resource LEFT JOIN likes ON likes_resource.identifier = like_resource_id and user_id = @current_user_id

Anywhere the join finds a match it will bring in and populate the user_id field. If the user_id is null, then the user didn't like it and you shouldn't highlight it.

Upvotes: 1

Related Questions