CODEWITHSUNDEEP
phpmysql
Irfan
Irfan

Reputation: 85

What's the best practice of getting a user's list of article likes from a database?

I'm sort of working on a CMS type structure. I've got to the point where mostly everything is in place except for small things that I keep pondering on like this one.

So let us suppose there is a user table and an articles table. Now, ofcourse if someone 'likes' an article, the user's ID will be stored in a column in the article's table. Something like this 11241,12412,12312. (these are random user IDs)

Now lets say that there's a user's profile page as well and I want to iterate through the user's liked posts and display it there.

Now, I've thought up of 2 ways of doing this.

First method being that the article IDs that the user has liked are stored in the user's table in his row and we get it from that but this seems sort of inefficient. Because if the user has liked a lot of articles, then I could run out of space in the column of the database?

Second method would be to go through every article and see if the user's ID exists in the list of likers saved of the article. However this method would be really slow and a really bad practice, IMO.

So what's the best practice for this?

Upvotes: 1

Views: 113

Answers (1)

Martin Bean
Martin Bean

Reputation: 39439

Create a table that has two columns: user_id and article_id. When a user “likes” an article, insert a row into this table. Then when you want to see which articles user #123 has liked, you can just issue a query like this:

SELECT `article_id` FROM `article_user` WHERE `user_id` = '123';

If you need the article data, it’s easy enough to just join on the article_id column:

SELECT `article`.*
FROM `article`
LEFT JOIN `article_user` ON `article`.`id` = `article_user`.`article_id`
WHERE `article_user`.`user_id` = '123';

Upvotes: 2

Related Questions