How to join with DB::table and orderBy jointable attribute set 0 if no join found

I have a DB::table query that i make a join with. In case the join is not possible, i want to set the value of the attribute that i'm looking for with my join to 0 for the order. With my current query, entries that have no join are ignored.

$d_list = DB::table('d')->where('active', 1)
        ->join('d_scores', 'd_id', '=', 'd.id')
        ->orderBy(DB::raw('(case when d_scores.totalscore is null then 0 else d_scores.totalscore end)'), 'desc')
        ->orderBy('u_24h', 'desc')
        ->orderBy('d.nf', 'desc')
        ->orderBy('d.id', 'desc')
        ->get(array('d.id', 'd_scores.totalscore'));

    foreach($d_list as $key => $d){
        if($d->id == $data['d']->id){
            $d_rank = $key+1;
            break;
        }
    }

I have a table with 1000 entries. Out of these 1000, only 100 have a row in the join table. I want to order the result of the 1000 based on the value of the attribute that only 100 have though. The 900 other entries that don't have a row in the join table need to get the value 0 for that specific attribute that doesn't exist for them (d_scores.totalscore). But because the join doesn't exists for them, these entries are not taken anymore at all.

from Newest questions tagged laravel-5 - Stack Overflow https://ift.tt/2RHwHas
via IFTTT