Laravel查询构建器检索错误的ID

Question

I'm retrieving Employee data from my database.

The table looks like this (I left out most of the columns which are not relevant)

+------------------+-------------------------------+------+-----+---------+----------------+
| Field            | Type                          | Null | Key | Default | Extra          |
+------------------+-------------------------------+------+-----+---------+----------------+
| id               | int(11)                       | NO   | PRI | NULL    | auto_increment |
| home_address_id  | int(11)                       | NO   | MUL | NULL    |                |
| created_at       | datetime                      | NO   |     | NULL    |                |
| updated_at       | datetime                      | NO   |     | NULL    |                |
| deleted_at       | datetime                      | YES  |     | NULL    |                |
+------------------+-------------------------------+------+-----+---------+----------------+

My query:
($data is the requirements the user has given over POST)

Employee::where('deleted_at', '=', null)
        ->join('addresses', 'employees.home_address_id', '=', 'addresses.id')
        ->leftJoin('employee_has_types', 'employees.id', '=', 'employee_has_types.employee_id')
        ->where('first_name', 'LIKE', $data->first_name)
        ->where('last_name', 'LIKE', $data->last_name)
        ->where('type_id', 'LIKE', $typeid->type_id)
        ->where('street', 'LIKE', $data->street_name)
        ->where('house_number', 'LIKE', $data->house_number)
        ->where('zip_code', 'LIKE', $data->zip_code)
        ->where('city', 'LIKE', $data->city)
        ->where('country', 'LIKE', $data->country)
        ->where('birth_date', 'LIKE', $data->birth_date)
        ->where('gender', 'LIKE', $data->gender)
        ->where('github', 'LIKE', $data->github)
        ->where('nationality', 'LIKE', $data->nationality)
        ->where('email', 'LIKE', $data->email)
        ->where('key_skills', 'LIKE', $data->skills)->get();

And when I var_dump() the result, it shows two results, which were expected.

But there is one problem with my Id. it shows the same Id twice.

array (size=27)
  'id' => int 1
//Rest of my data

and

    array (size=27)
  'id' => int 1
//Rest of the other data.

What have I done wrong? the data doesn't have duplicate Id's in my database.

Answer 1

You need to add select and groupBy statment:

Employee::select('employees.*')where('deleted_at', '=', null)
    ->join('addresses', 'employees.home_address_id', '=', 'addresses.id')
    ->leftJoin('employee_has_types', 'employees.id', '=', 'employee_has_types.employee_id')
    ->where('first_name', 'LIKE', $data->first_name)
    ->where('last_name', 'LIKE', $data->last_name)
    ->where('type_id', 'LIKE', $typeid->type_id)
    ->where('street', 'LIKE', $data->street_name)
    ->where('house_number', 'LIKE', $data->house_number)
    ->where('zip_code', 'LIKE', $data->zip_code)
    ->where('city', 'LIKE', $data->city)
    ->where('country', 'LIKE', $data->country)
    ->where('birth_date', 'LIKE', $data->birth_date)
    ->where('gender', 'LIKE', $data->gender)
    ->where('github', 'LIKE', $data->github)
    ->where('nationality', 'LIKE', $data->nationality)
    ->where('email', 'LIKE', $data->email)
    ->where('key_skills', 'LIKE', $data->skills)
    ->groupBy('employees.id')
    ->get();

becaue you're using join queries so the id can be override for the other table :)