redshot redshot - 1 month ago 11
MySQL Question

How to query third table with hasOne relationship in Laravel?

I have a tickets table. The main tickets table hasOne customer. The customer table belongs to the user table.

Ticket.php

public function user()
{
return $this->hasOne('App\Models\User');
}

/**
* Get the TicketStatus record associated with the Ticket.
* One ticket has only one status
*/
public function ticket_status(){
return $this->hasOne('App\TicketStatus','id','ticket_status_id');
}

/**
* Get the Customer record associated with the Ticket.
* One ticket has only one customer
*/
public function customer()
{
return $this->hasOne('App\Models\Customer', 'id', 'customer_id');
}


Customer.php

public function user()
{
return $this->belongsTo(User::class);
}

/**
* Get the Ticket that owns the Customer.
*/
public function ticket()
{
return $this->belongsTo('App\Ticket');
}


User.php

public function customer()
{
return $this->hasOne('App\Models\Customer');
}
/**
* Get the Ticket record associated with the user.
*
*/
public function ticket()
{
return $this->belongsTo('App\Ticket');
}


index.blade.php

@foreach($tickets as $item)
<tr>
<td>{{ $item->customer->first_name }}</td>
</tr>
@endforeach


The main table is tickets. It has a foreign_key to users and customers. Customers table has a foreign key to user which contains first_name and last_name.

Problem: I want to access $item->customer->first_name.


Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'users.ticket_id' in 'where clause' (SQL: select * from
users
where
users
.
deleted_at
is null and
users
.
ticket_id
= 1 and
users
.
ticket_id
is not null limit 1)


Any help is appreciated.

Answer

If your schema and relationship are defined properly then you can write your code as:

@foreach($tickets as $item)
<tr>
    <td>{{ $item->customer->user->first_name }}</td>
</tr>
@endforeach

OR

@foreach($tickets as $item)
<tr>
    <td>{{ $item->user->first_name }}</td>
</tr>
@endforeach