1

I am new to Laravel. I am making an app to rate seasons of a TV show. I want to get the information for each season with the average rating and the current user's personal rating.

Currently I am just doing accessing it with raw MySQL.

$seasons = \DB::select('

SELECT * FROM seasons

LEFT JOIN (SELECT season_id, AVG(rating) as avg_rating FROM ratings_season 
GROUP BY season_id) t2 ON seasons.id = t2.season_id

LEFT JOIN (SELECT season_id, rating FROM ratings_season WHERE user_id = 1) t3 ON seasons.id = t3.season_id

ORDER BY seasons.number DESC');

How can I convert this raw query to one using Laravel Relationships?

MZK
  • 13
  • 4

3 Answers3

1

Try to adapt this code to your liking 

DB::table('seasons')
->select('*')
->leftJoin(
    DB::raw('(SELECT season_id, AVG(rating) as avg_rating FROM ratings_season GROUP BY (season_id)
    ) as t2'), function ($join) {
        $join->on ( 'seasons.id', '=', 't2.season_id' );
    }
)
->leftJoin(
    DB::raw('(SELECT season_id, rating FROM ratings_season WHERE user_id = 1) as t3'), function ($join) {
        $join->on ( 'seasons.id', '=', 't3.season_id' );
    }
)
->orderBy('seasons.number', 'desc')
->get();
EddyTheDove
  • 12,979
  • 2
  • 37
  • 45
0

I suggest that you take a look at Laravel Relationships. It's a super clean and easy way to join multiple tables without having to worry about the actual SQL queries and the disadvantages that come with them. (e.g. SQL Injections)

Matthias Weiß
  • 508
  • 7
  • 17
-1

Except Laravel Relationships as Matthias suggested, you can also do this using Query Builder

$select = DB::table('seasons as s')
->leftJoin('ratings_season as t2', function($join) {
    $join->on('t2.season_id', '=', 's.id')
})
->leftJoin('ratings_season as t3', function($join) {
    $join->on('t3.season_id', '=', 's.id')
         ->where('t3.user_id', '=', 1)
})
->orderBy('s.number', 'DESC')
->get();
Rashad
  • 1,344
  • 2
  • 17
  • 33