Compare two columns from two related tables using scopes in Laravel 9

Question

I'm trying to create a scope that will compare two columns on two related tables.

Based on these tables I would like to be able to get all instances of the ServiceCall model where the next_service_date is within the next 15 days, where the Customer model either has a null value for the last_contact_date or where it's before the ServiceCall's next_service_date.

Relevant table structure:

customers

• id
• last_contact_date

service_calls

• id
• customer_id
• next_service_date

Working SQL for what I'm trying to accomplish:

SELECT service_calls.next_service_date, customers.last_contact_date FROM service_calls 
INNER JOIN customers ON service_calls.customer_id = customers.id
WHERE service_calls.next_service_date BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 15 DAY)
AND (customers.last_contact_date < service_calls.next_service_date OR customers.last_contact_date IS NULL);

Is there a way to accomplish this part of the SQL customers.last_contact_date < service_calls.next_service_date with scopes?

This is what I have so far which does everything except the above.

Customer model:

public function scopeNotContacted(Builder $builder): Builder
{
    return $builder->whereNull('last_contact_date');
}

ServiceCall model:

public function scopeUpcoming(Builder $builder): Builder
{
    return $builder->whereBetween('next_service_date', [
        Carbon::today(),
        Carbon::today()->addDays(15)
    ])->whereHas('customer', fn ($builder) => $builder->notContacted());
}

Thanks!

Answer 1

I was able to solve this using an additional package kirschbaum-development/eloquent-power-joins

I removed the scope from the Customer model

ServiceCall model:

public function scopeNotContacted($builder)
{
    $builder->joinRelationship('customers', function ($join) {
        $join->where(function ($query) {
            $query->whereNull('customers.last_contact_date')
                  ->orWhereRaw('customers.last_contact_date < service_calls.next_service_date');
        });
    });
}

public function scopeUpcoming($builder)
{
    $builder->whereBetween('next_service_date', [
        Carbon::today(),
        Carbon::today()->addDays(15)
    ])->notContacted();
}