Sql join query in Laravel query builder

DB::table('candidates')
        ->join('choices', function ($join) {
            $join->on('candidates.option1', '=', 'choices.idoptions')->orOn('candidates.option2', '=', 'choices.idoptions')->orOn('candidates.option3', '=', 'choices.idoptions');
        })
        ->select('candidate.name', 'choices.program')->orderBy('name')->distinct()->get();

You will find a lot of answer here : https://laravel.com/docs/5.7/queries#joins

Thanks @VILFAGO

I love this best answer :')

You're welcome @pathum

@VILFAGO - :-P, my mistake

I guess my question would be, if you already have the raw query and it's working, why do you want to use query builder stuff? Sounds like unneeded work to me.

@KLEEH - Hello, are you liking laravel so far.

Some like converting to that, and that just converts back at run time. So who knows.

I still prefer

$sql = "SELECT DISTINCT powners.ownerid, powners.oname, ";
          $sql .= "COUNT(pets.petid) AS CountOfpetid ";
          $sql .= "FROM powners LEFT JOIN pets ON ";
          $sql .= "powners.ownerid = pets.ownerid ";
          $sql .= "GROUP BY powners.ownerid ";
          $sql .= "ORDER BY powners.oname";

But I can also do:

 $quy = DB::connection('mysqlv2')->table('dc_powners')
                ->leftJoin('dc_pets', 'dc_powners.ownerid', '=', 'dc_pets.ownerid')
                ->select('dc_powners.ownerid', 'dc_powners.oname')->distinct()
                ->selectRaw('count(dc_pets.petid) as CountOfpetid')
                ->groupby('dc_powners.ownerid')
                ->orderby('dc_powners.oname')
                ->get();

Both are the same.

I'm liking it pretty well. I have another developer helping to code, and of course we have different ways of doing things but that's fine. I have been building SQL queries since 1990, so I really prefer your first method. But everyone has to do what makes sense to them.

What's weird is these eloquent queries have to convert the normal SQL at runtime.

And if you get to a certain complexity you cannot write a query in eloquent, have to switch to normal SQL anyway.

I still write normal queries probably 70% of the time.

I do use Query Builder some for some things.