Laravel lookup avg result in other table

Question

I've got this query in Laravel:

DB::table('score')
    ->select('score.score_nl', DB::raw('count(*) as total'), DB::raw('round(avg(rating_results.rating)) as final_rating'))
    ->join('rating', 'rating.score_id', '=', 'score.id')
    ->join('rating_results', 'rating.rating_result_id', '=', 'rating_results.id')
    ->groupBy('score_nl')
    ->get();

Result:

[{"score_nl":"emphatisch","total":1,"final_rating":"1"},{"score_nl":"huilen","total":2,"final_rating":"3"},{"score_nl":"knuffelig","total":1,"final_rating":"1"},{"score_nl":"zindelijkheid","total":2,"final_rating":"3"}]

I have a table called rating_results (see images) in this table I want to look up the final_rating and get the associated result_en.

How could I do this in laravel ?

Any questions please let me know!

--EDIT--

I have tried this;

$q = Result::select('result_nl')
    ->whereColumn('rating_results.rating', 'final_rating')
    ->whereColumn('rating_results.score_id', 'score_id')
    ->getQuery();

DB::table('score')
    ->select('score.score_nl', DB::raw('count(*) as total'), DB::raw('round(avg(rating_results.rating)) as final_rating'))
    ->join('rating', 'rating.score_id', '=', 'score.id')
    ->join('rating_results', 'rating.rating_result_id', '=', 'rating_results.id')
    ->selectSub($q, 'result_nl')
    ->groupBy('score_nl')
    ->get();

But then I get this error:

SQLSTATE[42S22]: Column not found: 1247 Reference 'final_rating' not supported (reference to group function) (SQL: select `score`.`score_nl`, count(*) as total, round(avg(rating_results.rating)) as final_rating, (select `result_nl` from `rating_results` where `rating_results`.`rating` = `final_rating` and `rating_results`.`score_id` = `score_id`) as `result_nl` from `score` inner join `rating` on `rating`.`score_id` = `score`.`id` inner join `rating_results` on `rating`.`rating_result_id` = `rating_results`.`id` group by `score_nl`)

It looks like I need to use the joinSub

Answer 1

You were close. You can use joinSub here.

As far as I can see from your attempt, you also need to join by score_id? If so, here's the solution:

// Your initial query
$query = DB::table('score')
    ->selectRaw('
        score.score_id, 
        score.score_nl, 
        count(*) as total, 
        round(avg(rating_results.rating)) as final_rating
    ')
    ->join('rating', 'rating.score_id', '=', 'score.id')
    ->join('rating_results', 'rating.rating_result_id', '=', 'rating_results.id')
    ->groupBy('score_nl');

// Now use joinSub (just like simple joins above)
$result = DB::table('rating_results')
    ->joinSub($query, 'subtable', function($join) {
        $join->on('subtable.final_rating', '=', 'rating_results.rating')
             ->on('subtable.score_id', '=', 'rating_results.score_id');
    })
    ->selectRaw('subtable.*, rating_results.result_en')
    ->get();

---

But I'd personally go with a raw SQL here. When the query gets too cumbersome it might be a good idea to avoid ORM so you are not limited to its functionality:

$result = DB::select('
    select subtable.*, rating_results.result_en
    from rating_results
    join (
        select
            score.score_nl, 
            count(*) as total, 
            round(avg(rating_results.rating)) as final_rating 
        from score
        join rating on rating.score_id = score.id
        join rating_results on rating.rating_result_id = rating_results.id 
        group by score_nl
    ) as subtable on subtable.final_rating = rating_results.rating
');