Ollie Ollie - 2 months ago 6
MySQL Question

How can I efficiently query 'Like' data from MySQL?

I am developing a social network iOS app using a PHP backend with a MySQL database. I have 3 tables in my database to store Posts, Comments and Likes.

Currently, the app requests up to 50 posts at a time which includes various specific data. I do this using a SELECT query from MySQL. My 'Likes' are stored in the 'Likes table' as 1 Like per row. This includes their username and which post they 'liked'.

When the app is refreshed, I need to gather the 'Likes' data on the posts that have just been loaded. My problem is that I do not know the best way to query MySQL to return all associated likes with that user. This means that when I load some posts, I want the user to see which posts they have already liked.

Here is a visual description of my problem:
Here is a visual description of my problem

Please understand that up to 50 of these posts will be shown at one time.

Answer

There is several way to do this and depending on your project you might want to optimise my answer.

I assume your Likes table has a structure of (int) id, (int) user_id, (int) post_id.

To answer directly to your question (about doing a query only for the refresh), the next MySQL query will give you the list of post_id liked by the user. You need to replace %user_id% by the id of the user and %post_ids% by the list of the 50 posts ids, separated by a comma:

SELECT post_id
FROM Likes
WHERE user_id = %user_id% AND
      post_id IN (%post_ids%)

However, if the refresh implies the whole page (and not only an AJAX request or so on) I would consider implementing this directly in the query getting the posts. For example :

SELECT p.id, p.content, p...., l.id AS `Liked`
FROM Posts AS p
LEFT JOIN Likes AS l
       ON p.id = l.post_id AND
          l.user_id = %user_id%  
WHERE ... 

Please also consider adding indexes on the right columns.