Laravel Join multiple table not working

Question

I'm having an issue on Laravel 5.4 when I try to use only one join it works ok and returns correct data, but their add another join it doesn't work.

$data = Player::select(DB::raw('CONCAT(familyName,", ",firstName) AS fullName'))
    ->where('firstname', 'like', '%'.$search.'%')
    ->orWhere('familyName', 'like', '%'.$search.'%')
    ->orderBy('familyName', 'asc')
    ->join('teams', 'players.primaryClubId', '=', 'teams.clubId')
    ->join('person_competition_statistics', 'players.personId', '=', 'person_competition_statistics.personId')
    ->addSelect(['players.*', 'teams.teamName', 'teams.teamNickname', 'teams.teamCode'])
    ->get()
    ->unique() //remove duplicates
    ->groupBy(function($item, $key) { //group familyName that starts in same letter
        return substr($item['familyName'], 0, 1);
    })
    ->map(function ($subCollection) {
        return $subCollection->chunk(4); // put your group size
    });

return $data;

Returned Error:

QueryException in Connection.php line 647:
SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'familyName' in field list is ambiguous (SQL: select CONCAT(familyName,", ",firstName) AS fullName, `players`.*, `teams`.`teamName`, `teams`.`teamNickname`, `teams`.`teamCode` from `players` inner join `teams` on `players`.`primaryClubId` = `teams`.`clubId` inner join `person_competition_statistics` on `players`.`personId` = `person_competition_statistics`.`personId` where `firstname` like %% or `familyName` like %% order by `familyName` asc)

Do you have the column familyName in more than one table of all the 3 you are joining? If so, you need to prefix it with the table name everywhere you use it in the query. like DB::raw('CONCAT(players.familyName,", ",firstName) AS fullName') — ayip
– ayip, Commented May 24, 2017 at 3:37
@PenAndPapers If you are joining table then you should give table alias. like team as t , players as p and then column name like p.playername — Nikhil Radadiya
– Nikhil Radadiya, Commented May 24, 2017 at 4:59

Nikhil Radadiya · Accepted Answer · 2017-05-24 05:23:26Z

1

If you are joining table then you should give table alias. like team as t , players as p and then column name like p.playername

answered May 24, 2017 at 5:23

Nikhil Radadiya

2,0653 gold badges25 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Laravel Join multiple table not working

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related