Problem Defining a Special Relation

[MannikJ]

This is probably not the best place for my question but it was the closest to the topic I could think of. So I hope there will be some hints however.

Is there any possibility to define the following call as a relation?

CustomerService::where([
    'customer_id' => $this->job->customer_id,
    'service_id' => $this->service_id,
])->first();

I have so far not been able to figure it out, because it's some kind of hybrid between belongsToThrough and a belongsTo relation with composite keys. I also use Compoships.

Thanks to your package I could at least already define the customer relation:

public function customer()
{
    return $this->belongsToThrough(
        User::class,
        Job::class,
    );
}

I would really like to benefit from the built-in query reduction, eager loading etc. that a relation would provide.

Here the database migrations:

Schema::create('users', function (Blueprint $table) {
    $table->increments('id');
});

Schema::create('jobs', function (Blueprint $table) {
    $table->increments('id');
    $table->integer('customer_id')->unsigned()->nullable()->index();
    $table->foreign('customer_id')->references('id')->on('users')->onDelete('cascade');
});

Schema::create('services', function (Blueprint $table) {
    $table->increments('id');
});

Schema::create('service_jobs', function (Blueprint $table) {
    $table->increments('id');
    $table->integer('job_id')->unsigned()->index();
    $table->foreign('job_id')->references('id')->on('jobs')->onDelete('cascade');
    $table->integer('service_id')->unsigned()->index();
    $table->foreign('service_id')->references('id')->on('services')->onDelete('cascade');
});

Schema::create('customer_services', function (Blueprint $table) {
    $table->increments('id');
    $table->integer('customer_id')->unsigned()->index();
    $table->foreign('customer_id')->references('id')->on('users')->onDelete('cascade');
    $table->integer('service_id')->unsigned()->index();
    $table->foreign('service_id')->references('id')->on('services')->onDelete('cascade');
    $table->timestamps();
});

[staudenmeir]

Please post the migrations of all the tables involved.

[MannikJ]

@staudenmeir Just added the important parts of the migrations to the first post. Hope I didn't forget anything.

The crucial part although is that there is a join required to get the customer_id from the jobs table and the service_id foreign key must also be taken into account.

[staudenmeir]

Which one should be the parent model (where the relationship is defined)?

[MannikJ]

@staudenmeir The service_jobs table that was missing ;)

[staudenmeir]

In your CustomerService::where(...)->first(); example: What is $this?

What would the raw SQL query look like?

[MannikJ]

An instance of \App\ServiceJob

I am not exactly sure how the query would look like to be honest.
Probably something like

SELECT
  customer_services.*
FROM
  customer_services
  JOIN service_jobs ON customer_services.service_id = service_jobs.service_id
  JOIN jobs ON jobs.id = service_jobs.job_id
WHERE customer_services.service_id = service_jobs.service_id
AND customer_services.customer_id = jobs.customer_id
AND service_jobs.id = ?

[staudenmeir]

I don't see a way to define a relationship for this query.

What makes this case so complicated is the fact that multiple constraints reference the parent table service_jobs. If there were additional constraints between intermediate tables and/or the related table, you could use whereColumn().