Eloquent query where column is in another table

Question

I have two tables, posts and likes. I need to create a query, using Eloquent, that gets all posts that have been liked by a specific user_id.

In other words, it should be something like this:

SELECT * FROM posts p LEFT JOIN likes l ON p.id = l.post_id WHERE l.user_id = 2 ORDER BY l.created_at DESC

posts table:

+----+---------+------------+-------------+
| id | user_id |  message   | created_at  |
+----+---------+------------+-------------+
|  1 |       2 | Hello!     | <SOME TIME> |
|  2 |       3 | World!     | <SOME TIME> |
|  3 |       2 | Something. | <SOME TIME> |
|  4 |       2 | Another.   | <SOME TIME> |
+----+---------+------------+-------------+

likes table:

+----+---------+---------+-------------+
| id | post_id | user_id | created_at  |
+----+---------+---------+-------------+
|  1 |       1 |       2 | <SOME TIME> |
|  2 |       2 |       2 | <SOME TIME> |
|  3 |       1 |       3 | <SOME TIME> |
|  4 |       3 |       2 | <SOME TIME> |
+----+---------+---------+-------------+

Here is my Postclass:

<?php

class Post extends Eloquent {

    protected $table = 'posts';

    public function likes()
    {
        return $this->hasMany('Like');
    }

}

And the Like class:

<?php

class Like extends Eloquent {

    protected $table = 'likes';


    public function post()
    {
        return $this->belongsTo('Post');
    }

}

How can I do this?

Answer 1

This should work:

$userId = //however you get the userid here.

$posts = Post::whereHas('likes', function ($q) use ($userId) {
    $q->where('user_id', $user_id);
})->get();

Answer 2

You can use Laravel's DB class to perform joins on two or more tables, following is how your query will be executed in laravel:

$users = DB::table('posts')
        ->leftJoin('likes', 'posts.id', '=', 'likes.post_id')
        ->select('posts.*', 'likes.*')
        ->where('likes.user_id', '=', '2')
        ->orderBy('likes.created_at', 'desc')
        ->get();

Don't forget to use DB class on the top of your controller;

If you want to do it with eloquent, you should do the follwing:

$result = Post::whereHas('likes', function ($q) use($user_id)
                {
                   $q->where('user_id', $user_id);
                })
                  ->orderBy('likes.created_at')
                  ->get();