Eloquent - Join a Subquery

Basically i was trying to translate this Raw Query to Eloquent:

$members = DB::select("
SELECT members.id, members.surname, members.name, members.club
FROM members
INNER JOIN
 (SELECT count(id) as occurences, concat(surname, ' ', name) as fullname
  FROM members
  GROUP BY fullname
  HAVING occurences > 1
 ) as duplicates on duplicates.fullname = concat(members.surname, ' ', members.name)
ORDER BY name"
);

$duplicates = DB::table('members')
    ->selectRaw("count(id) as occurences, concat(surname, ' ', name) as fullname")
    ->groupBy('fullname')
    ->having('occurences', '>', '1');

$members = Member::select(['id', 'surname', 'name', 'club'])
    ->joinSub($duplicates, 'duplicates', function ($join)
    {
        $join->on("concat(surname, ' ', name)", '=', 'duplicates.fullname');
    })
    ->orderBy('name')
    ->get();

The problem seems to be the concatination in the join function. I've tried to use a ->selectRaw(concat...) and multiple other things, nothing would work.

I guess Eloquent doesn't support raw inputs and aggregate functions in the same way the Query-Builder does, but is there maybe a workaround?

Laravel is displaying this error-message:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'concat(members.surname, ' ', members.name)' in 'on clause' (SQL: select `id`, `surname`, `name`, `club` from `members` inner join (select count(id) as occurences, concat(surname, ' ', name) as fullname from `members` group by `fullname` having `occurences` > 1) as `duplicates` on `concat(members`.`surname, ' ', members`.`name)` = `duplicates`.`fullname` order by `name` asc)

The generated SQL-Query seems to be fine..

When joining only on the 'name' column, everything is working fine. So the only problem is using 'CONCAT'.

MichalOravec

6 years ago

Best Answer

Level 75

@pauldiepold Try

$duplicates = DB::table('members')
    ->selectRaw("count(id) as occurences, concat(surname, ' ', name) as fullname")
    ->groupBy('fullname')
    ->having('occurences', '>', '1');

$members = Member::selectRaw("id, surname, name, club, concat(surname, ' ', name) as fullname")
    ->joinSub($duplicates, 'duplicates', function ($join)
    {
        $join->on('members.fullname', '=', 'duplicates.fullname');
    })
    ->orderBy('name')
    ->get();

diepaul

6 years ago

Level 6

Same Error :(

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'members.fullname' in 'on clause' (SQL: select id, surname, name, club, concat(surname, ' ', name) as fullname from `members` inner join (select count(id) as occurences, concat(surname, ' ', name) as fullname from `members` group by `fullname` having `occurences` > 1) as `duplicates` on `members`.`fullname` = `duplicates`.`fullname` order by `name` asc)

MichalOravec

6 years ago

Level 75

@pauldiepold So

$duplicates = DB::table('members')
    ->selectRaw("count(id) as occurences, name, surname, concat(surname, ' ', name) as fullname")
    ->groupBy('fullname')
    ->having('occurences', '>', '1');

$members = Member::select(['id', 'surname', 'name', 'club'])
    ->joinSub($duplicates, 'duplicates', function ($join) {
        $join->on('members.name', '=', 'duplicates.name')->on('members.surname', '=', 'duplicates.surname');
    })->orderBy('name')->get();

diepaul

6 years ago

Level 6

Uff, thanks a lot!

$duplicates = DB::table('members')
    ->selectRaw("count(id) as occurences, surname as dsurname, name as dname")
    ->groupBy('dsurname', 'dname')
    ->having('occurences', '>', '1');

$members = Member::select(['id', 'surname', 'name', 'club'])
    ->joinSub($duplicates, 'duplicates', function ($join)
    {
        $join->on('members.surname', '=', 'duplicates.dsurname')
            ->on('members.name', '=', 'duplicates.dname');
    })
    ->orderBy('name')
    ->get();

Please or to participate in this conversation.