How to query a customize key in Laravel?

Question

Considering this tables: person and employee to which connected to person by person_id. This person_id is also the Primary & Foreign key of employee table. Thus in my migrations, i have this:

Schema::create('employees', function (Blueprint $table) {
    $table->bigIncrements('person_id');
    $table->foreign('person_id')->references('id')->on('persons')->onDelete('cascade');
});

and my show method is like this one

public function show(Employee $employee){
    dd($employee->person_id);
    $employee = Employee::where('person_id', $employee->person_id)->orderBy('employee_number', 'asc')->join('persons', 'employees.person_id', '=', 'persons.id')->first();
    return view('employee.show', compact('employee'));
}

But i am experiencing this issue:

Illuminate\Database\QueryException
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'id' in 'where clause' (SQL: select * from `employees` where `id` = 5 limit 1)

Does the query is not aware of the column i am using?

`select * from employees where id = 5 limit 1` ? You sure this PHP code is generating that SQL? As `Employee::where('person_id', $employee->person_id)` does not use the `id` column ... — Raymond Nijland, Oct 20 '19 at 14:32
Can you run and post the results of `SHOW CREATE TABLE employees` and `SHOW CREATE TABLE persons` ? — Raymond Nijland, Oct 20 '19 at 14:34
... and the code off `Employee` class also check @Saly3301 's answer as that answer might be right atleast it makes alot of sense .. — Raymond Nijland, Oct 20 '19 at 14:36

Salim Djerbouh · Accepted Answer · 2019-10-20T14:46:36.907

3

Change the primary key in the Employee model

class Employee extends Model
{
    /**
     * The primary key associated with the table.
     *
     * @var string
     */
    protected $primaryKey = 'person_id';

    public function person()
    {
        return $this->belongsTo('App\Employee', 'person_id', 'person_id');
    }
}

Then query using find

Employee::find($employee->person_id)->with('person')->first();

orderBy and first are redundant on one result queries

And if you want to return the Employee with the Person, just access the relationship object (employee is returned from route model binding)

public function show(Employee $employee) {
    $person = $employee->person;
    return view('employee.show', compact('employee', 'person'));
}

Hope this helps

edited Oct 20 '19 at 14:46

answered Oct 20 '19 at 14:32

Salim Djerbouh

10,719
6
29
61

Thank you very much there are still lot of things i have to explore in this frameworks, and thank for pointing out ```orderBy``` and ```first```. – Anirudh Lou Oct 20 '19 at 14:38
You're welcome, please consider accepting the answer if it solves the problem :) – Salim Djerbouh Oct 20 '19 at 14:39
2

@AnirudhLou database abstraction frameworks layers makes it a bit harder to see when something is redundant as it is abstracted which SQL it generates .. in SQL it is more easy to see if id column is unique things like DISTINCT / GROUP BY / ORDER BY / LIMIT can be redundant consider this assume id column to have a `PRIMARY KEY` then .. `SELECT DISTINCT id ...`, `SELECT ... GROUP BY id` (wierd example i know) , ... `SELECT .. WHERE id = 1 LIMIT 1`, `SELECT .. WHERE id = 1 ORDER BY id LIMIT 1` and `SELECT .. WHERE id = 1 ORDER BY id` .. notice it much more easy to see in SQL.. – Raymond Nijland Oct 20 '19 at 14:44
Using this find, why it only returns my employee? I mean, it does not includes person data? – Anirudh Lou Oct 20 '19 at 14:45
You need to eager load the relationship, I updated my answer – Salim Djerbouh Oct 20 '19 at 14:46
1

.. also @AnirudhLou be sure not to run into SELECT N + 1 problems which is also very easy ... [What is the “N+1 selects problem” in ORM (Object-Relational Mapping)?](https://stackoverflow.com/questions/97197/what-is-the-n1-selects-problem-in-orm-object-relational-mapping) – Raymond Nijland Oct 20 '19 at 14:48
on the other side, that is the ```hasOne``` is it also ```$this->hasOne('App\Employee', 'person_id', 'person_id');``` or ```$this->hasOne('App\Employee', 'person_id', 'id');```? – Anirudh Lou Oct 20 '19 at 14:49
1

second argument is foreign key, third argument is local key – Salim Djerbouh Oct 20 '19 at 14:50

How to query a customize key in Laravel?

1 Answers1