Retrieving "likes" tied to users from a database

Question

I'm new to database structure. I'm trying to create an app that allows users to like certain entries, but I want to be able to tie likes to users so that I can change the visuals before/after the like action.

I think from research that I should have an 'entries' and 'users' table and then have a 'likes' table that ties the two to each other.

The only thing I'm unsure of is, when getting and displaying the contents... how would I write the queries? If I query for all the entries I need, do I then go back and individually query each to see if it has a like tied to it for the current user? That seems like it might be a costly operation. Is there a more efficient way?

Hope that makes sense, Thanks.

Answer 1

Hi, I think u can retrieve the entries and query the likes table at the same time to get if the current user likes the entry performing a stored procedure. So u can control the value of the set of data returned by the query for example returning one colum for the entry text and one boolean column to evaluates the current user likes... In this way you will at least one parameter for the stored procedure to indicate who is the current user

I hope this idea help u...

Answer 2

Assuming you have a table Entries with a column entity_id (and whatever else you store about the entity) and a second table UserLikes that contains the columns user_id and entity_id, you would do the following:

 SELECT Entries.col1, Entries.col1 . . ., UserLikes.user_id
     FROM Entries LEFT OUTER JOIN UserLikes ON 
         Entries.entity_id = UserLikes.entity_id
     WHERE UserLikes.user_id = :user_id
         AND Entity.col_whatever = :whatever

In this example, Entries.col1, Entries.col2 . . . is the list of columns you want to get back about the Entries. The :user_id is a parameter that contains the id of the user you're currently trying to display Entries for. And the last line is standing in for whatever limitations you want to put on the Entries are returned.

This query will give you a row for each Entry you searched for. You can check the value the returned column user_id. If it's NULL then it was not liked by the user, if it contains the user's id, it was liked by the user.

Answer 3

I think you have the right database design in mind. As far as queries are concerned, assume tables as such:

Users

ID | Name
1  | Bob
2  | Sally

Entries

ID | Name
1  | Red
2  | Blue
3  | Yellow

Likes

UserID | EntryID
1      | 1
1      | 2
2      | 2
2      | 3

So we can say Bob likes Red and Blue while Sally likes Blue and Yellow. So a query to retrieve all entries, plus an indicator of what Bob likes would be:

SELECT 
e.ID,
e.Name,
l.UserID
FROM Entries e LEFT JOIN Likes l ON l.EntryID = e.ID
WHERE l.UserID = 1 -- Bob's User ID
ORDER BY e.Name

This would return

ID | Name   | UserID
2  | Blue   | 1
1  | Red    | NULL
3  | Yellow | 1

The UserID column indicates if Bob likes the entry or not - a NULL is No and a value is Yes.