I need to write eloquent query that return messages having specific tag

Question

messages Table
+----+----------+
| id |   text   |
+----+----------+
|  1 | message1 |
|  2 | message2 |
+----+----------+

tags table 

+----+-----------+
| id |   name    |
+----+-----------+
|  1 | valentine |
|  2 | funny     |
|  3 | santa     |
+----+-----------+

message_tag
+----+------------+--------+
| id | message_id | tag_id |
+----+------------+--------+
|  1 |          1 |      1 |
|  2 |          2 |      1 |
|  3 |          2 |      2 |
+----+------------+--------+

I have written this query so far to get all messages but i cant return the message where tag_id = 1;

    Message::with([
        'tags'=>function($q){
            $q->select("name","tag_id");
        }
    ])->select("id", "text")->paginate(10);

i have used where clause inside function($q){$q->where('tag_id',1)}. but it didn't worked.

Answer 1

You have to include use Illuminate\Support\Facades\DB; at the top of class

$tagId = 1; 
$return = DB::table('message_tag')
            ->join('messages', 'messages.id', '=', 'message_tag.message_id')
            ->join('tags', 'tags.id', '=', 'message_tag.tag_id')
            ->where("message_tag.tag_id", "=", $tagId)
            ->select('messages.text', 'tags.text')
            ->get();