CODEWITHSUNDEEP
phpsqldatabaselaraveleloquent
Marcus Christiansen
Marcus Christiansen

Reputation: 3197

Laravel HasOneThrough Relationship

I have the following database schema set up in a Laravel app. I'm only mentioning the important columns for simplicity sake.

Task

Job

User

Inside the Task model I want to have a relationship method to the users table, which in this case is referred to as the Client (client_id) on the intermediate jobs table.

In my code I want to able to reference $task->client();

I've looked through the docs and found either:

public function client()
{
    return $this->belongsToMany('App\User', 'users', 'client_id');
}

returns:

"SQLSTATE[42S22]: Column not found: 1054 Unknown column 'jobs.user_id' in 'field list' (SQL: select users.*, jobs.client_id as pivot_client_id, jobs.user_id as pivot_user_id from users inner join jobs on users.id = jobs.user_id where jobs.client_id = 112 and users.deleted_at is null)"

or

public function client()
{
    return $this->hasOneThrough('App\User', 'App\Job', 'client_id', 'user_id');
}

returns:

"SQLSTATE[42S22]: Column not found: 1054 Unknown column 'users.user_id' in 'on clause' (SQL: select users.*, jobs.client_id as laravel_through_key from users inner join jobs on jobs.id = users.user_id where jobs.deleted_at is null and jobs.client_id = 111 and users.deleted_at is null limit 1)"

How can I retrieve the User model from the Task model?

Upvotes: 3

Views: 1498

Answers (2)

Philip
Philip

Reputation: 2988

For anyone still looking for the working answer:

Inside your Task model class:

public function client()
{
    return $this->hasOneThrough(
        User::class, // 'App\User' or 'App\Models\User' in Laravel 8
        Job::class, // 'App\Job' or 'App\Models\Job' in Laravel 8
        'id', // Foreign key on the jobs table...       <--,
        'id', // Foreign key on the users table...         |    <--,
        'job_id', // Local key on the tasks table...    ---'       |
        'client_id' // Local key on the jobs table...           ---'
    );
}

The arrows show which column is used to link to the other.

The correct referencing always has me puzzled for a minute, so hope this helps. :)

For reference, see Has One Through - Key conventions

In practice:

When you run Task::with(['client'])->first(), the framework runs 2 SQL queries:

select * from `tasks`

From these result rows it extracts all unique job_ids to use in the bindings (?) of the second SQL:

select
    `users`.*,
    `jobs`.`id` as `laravel_through_key`
from `users`
inner join `jobs`
    on `jobs`.`client_id` = `users`.`id`
where `jobs`.`id` in (?, ? ...etc)

Tested with Laravel 8.47.0 using MySQL.

Upvotes: 4

Remul
Remul

Reputation: 8242

Could you try the following:

public function client()
{
    return $this->hasOneThrough('App\User', 'App\Job', 'id', 'id', 'id', 'client_id');
}

Upvotes: 0

Related Questions