Laravel select records with pivot table

I have a little problem with eloquent. I have 3 tables. Users, phrases, and phrase_user. phrase user contains columns: id, phrase_id, user_id I need to select phrases with pivot table for example. This action with SQL will be like this…

SELECT phrase FROM phrases 
LEFT JOIN phrase_user ON  phrases.id = phrase_user.phrase_id
LEFT JOIN users ON phrase_user.user_id = $userId

This is my index function in controller

$phrases = DB::table('phrases')->orderBy('id','desc')->Paginate(5);
$user = Auth::user(); 
       
return view('pages.phrases.index')->with(['phrases' => $phrases, 'user' => $user]);

I also have written these functions in models… Phrase model:

public function users()
{
    return $this->belongsToMany('AppUser')->withTimestamps();
}

User model:

public function phrases()
{
    return $this->belongsToMany('AppPhrase')->withTimestamps();
}

In this case, paginator is not working properly because $phrases = DB::table('phrases')->orderBy('id','desc')->Paginate(5); There is some problem..

In conclusion: I need to show only my phrases in phrases page. every user must have their phrases.

Answer

$userId = 2;

$phrases = DB::table('phrases')->whereHas('users', function ($q) use ($userId) {
    $q->whereId($userId);
})->orderBy('id','desc')->Paginate(5);

$user = Auth::user();

return view('pages.phrases.index')->with(['phrases' => $phrases, 'user' => $user]);

Advertisement

Answer