How to write a nested join query using distinct method and concat two columns into one in laravel query builder

My original query is

    select distinct positions.pid, positions.eid, positions.name as position_name, elections.name as election_name, candidates.uname, firstname, surname 
from positions 
    inner join elections on positions.eid = elections.eid 
    inner join candidates on positions.pid = candidates.pid 
    inner join erole2 on candidates.uname = erole2.clogin 
    where elections.complaints_deadline <= CURRENT_TIMESTAMP;

I have written the above query in laravel controller like the following

$positions = DB::table('positions')
        ->join('elections', 'positions.eid', '=', 'elections.eid')
        ->join('candidates', 'positions.pid', '=', 'candidates.pid')
        ->join('erole2', 'candidates.uname', '=', 'erole2.clogin')
        ->select('positions.pid', 'positions.eid', 'positions.name as position_name', 'elections.name as election_name', 'candidates.uname', 'firstname', 'surname')
        ->where('elections.complaints_deadline', '<=', 'CURRENT_TIMESTAMP')
        ->distinct()->get();

But its not giving the same result. The original query returning 12 rows whereas the query I have written in my controller is returning an empty set. What i am doing wrong. How to write that query in laravel. I am using larvel 9.0

Secondly

I want to concatenate two columns from the above query ( firstname + surname as fullname) into one single column name. how to do that? In the above code I have tried writing the select clause like this

 ->select('positions.pid', 'positions.eid', 'positions.name as position_name', 'elections.name as election_name', 'candidates.uname', '"CONCAT('firstname','surname') AS fullname"')

But I am getting error saying Concat is not a valid column name.

Can you please guide me correct my wrong?

>Solution :

You’re using CURRENT_TIMESTAMP as a string not as a MySQL function. For that, you need to use DB::raw() function. You can use DB::raw() for the name as well.

Try this

$positions = DB::table('positions')
    ->join('elections', 'positions.eid', '=', 'elections.eid')
    ->join('candidates', 'positions.pid', '=', 'candidates.pid')
    ->join('erole2', 'candidates.uname', '=', 'erole2.clogin')
    ->select('positions.pid', 'positions.eid', 'positions.name as position_name', 'elections.name as election_name', 'candidates.uname', DB::raw('CONCAT(firstname, " ", surname) AS fullname'))
    ->where('elections.complaints_deadline', '<=', DB::raw('CURRENT_TIMESTAMP'))
    ->distinct()->get();