John Davidson

Message:

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!

---

My Answer:
To accomplish the comparison between the `last_contact_date` and `next_service_date` columns in the scopes, you can use the `whereRaw` method to add a raw SQL query to the scope. Here's how you can modify your scopes to include the comparison:

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


ServiceCall model:
php
public function scopeUpcoming(Builder $builder): Builder
{
return $builder->whereBetween('next_service_date', [
Carbon::today(),
Carbon::today()->addDays(15)
])->whereHas('customer', function ($query) {
$query->notContacted()->orWhereRaw('last_contact_date < service_calls.next_service_date OR last_contact_date IS NULL');
});
}


In the `ServiceCall` model's `scopeUpcoming` method, we are using the `whereHas` method to filter the results based on the `notContacted` scope from the `Customer` model. Additionally, we are using the `orWhereRaw` method to add the comparison between the `last_contact_date` and `next_service_date` columns as a raw SQL query.

With these modifications, you should be able to retrieve all instances of the `ServiceCall` model where the `next_service_date` is within the next 15 days and 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`.