CODEWITHSUNDEEP
phpmysqlcakephp-3.0
mauriblint
mauriblint

Reputation: 1707

MySQL: show posts feed and post likes to the current user

I wanted to hear your opinions about which will be the best approach to do these I explain in the title briefly.

Basically I show in the homepage a feed of posts, that can be liked or faved by the users. So my intension is to show in this feed if the user has liked previously this post.

Supouse this are my main tables

Table 'posts'
-------------
id
title
content
tags

Table 'likes'
-------------
user_id
post_id
date

And this is my main query in homepage

SELECT * FROM posts LIMIT 20

How can I include in this query if the current_user() has liked previuosly each posts? Should I need to exec a separe query?

Thanks guys!!

EDIT: I don't want to filter the query by the user likes, just want to get in each post like a boolean value that said if the current user like or not each post!

Upvotes: 0

Views: 290

Answers (2)

mauriblint
mauriblint

Reputation: 1707

The solution using CakePHP3

// Posts Model
$this->hasMany ('Likes', [
    'joinType' => 'LEFT',
    'propertyName' => 'favs'
]);

// the query
$this->Posts
    ->find('all')
    ->contain([
       'Likes' => [
           'conditions' => array('Likes.user_id' => <user id>)
       ]
    ]
);

Upvotes: 0

Andrej
Andrej

Reputation: 7504

You can solve this task in two ways: one query with join and two separate queries. You should test the performance and choose the best approach.

The first solution:

SELECT posts.* FROM posts 
INNER JOIN likes on posts.id=likes.post_id 
WHERE likes.user_id='<user id of current user >' LIMIT 20

The second solution

SELECT post_id 
FROM likes 
WHERE user_id='<user id of current user >' 
LIMIT 20

SELECT * FROM posts 
WHERE id in (<list of ids from the first query>)

The solution for comment:

SQL

SELECT posts.*, likes.user_id as liked FROM posts 
LEFT JOIN likes on posts.id=likes.post_id and likes.user_id='<user id of current user >'
LIMIT 20

PHP

foreach($rows as $row) {
   if ($row['liked']) {
       //do something
   }
}

Upvotes: 1

Related Questions