How to query third table with hasOne relationship in Laravel?

Question

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.

Amit Gupta Amit Gupta · Accepted Answer · 2016-10-28T06:13:42

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

How to query third table with hasOne relationship in Laravel?

2 Answers