Laravel mysql Inner join and also select a specific field from another table

Question

I have the following query:

Ratings::join('users', 'movieratings.rated_by', '=', 'users.usr_id')
        ->where('rated_on', $movieId)
        ->orderBy('rated_at', 'desc')
        ->select('comment', 'rating', 'rated_as', 'rated_at', 'username')
        ->paginate(20);

This will get all the feedback ratings for a specific movie. But I have another table which contains the total good and bad ratings for a specific movie movie, the only problem is that I cant get it to work to query that table as well at the same time.

If I do another query I would simply write: Movie::where('movie_id', $movieId)->select('total_good_ratings', 'total_bad_ratings')->get(); this would output eg "22, 15" but is it possible to only fetch two columns from a specific row then do a inner join between two tables and paginate the result?

thanks

Answer 1

You can do a leftJoin with the table that contains the good and bad ratings, where the join condition will be the id of the movie.

Ratings::join('users', 'movieratings.rated_by', '=', 'users.usr_id')
        ->leftJoin('movie', 'movie.id', '=', 'movieratings.rated_on')
        ->where('rated_on', $movieId)
        ->orderBy('rated_at', 'desc')
        ->select('comment', 'rating', 'rated_as', 'rated_at', 'username', 'total_good_ratings', 'total_bad_ratings')
        ->paginate(20);