I have 2 tables: customers and customer_consent_records. As the names imply, the one is just simple customer records, the other contains consent records. The consent records can be for 'News', 'Offers' etc and can be true (consenting) or false (not consenting). A customer can have many consent records.
I want to create a scope that joins the 2 tables on the customer ID, AND the newest consent record that is 'true' AND for a given type ('News').
I have a working query in SQL, I need to replicate this in Eloquent.
Here is the SQL:
SELECT c.id, c.last_name, c.first_name, ccr.id, ccr.consent
FROM customers AS c
LEFT JOIN customer_consent_records AS ccr ON ccr.customer_id = c.id
AND ccr.type = 'News'
AND ccr.consent = 1
WHERE
ccr.id = (
SELECT MAX(id)
FROM customer_consent_records
WHERE customer_id = c.id AND type = 'News' AND consent = 1
)
So far I have the following in my Customer model:
/**
* Scope - customers who have consented to receive the newsletter / wants / interests.
*/
public function scopeConsents(Builder $query, $type = null): void
{
$query->leftJoin('customer_consent_records', function (JoinClause $join) use ($type) {
$join->on('customers.id', '=', 'customer_consent_records.customer_id')
->where('customer_consent_records.type', '=', $type)
->where('customer_consent_records.consent', '=', '1');
})
->where(function (Builder $q) use ($type) {
$q->selectRaw('max(id)')
->from('customer_consent_records')
->whereColumn('customer_id', 'customers.id')
->whereColumn('type', '=', $type')
->whereColumn('consent', '1');
});
}
But when I run this query:
$customers = App\Models\Customer::select('customers.id', 'customers.last_name', 'customers.first_name')->consents('News')->limit('100')->toRawSql();
return dd($customers);
I get the following SQL:
select
`customers`.`id`,
`customers`.`last_name`,
`customers`.`first_name`
from
`customers`
left join `customer_consent_records` on `customers`.`id` = `customer_consent_records`.`customer_id`
and `customer_consent_records`.`type` = 'News'
and `customer_consent_records`.`consent` = '1'
where
(
`customer_id` = 'customers.id'
and `type` = `News`
and `consent` = '1'
)
limit
100
It seems to be ignoring the selectRaw entirely. Any suggestions on what I'm doing wrong?
Thanks, Steve
Since you seem to have a working query, you can always push that query into the database as a view, and then you can use Eloquent on the view, and make any scopes you want.
https://tray2.se/posts/use-a-view-instead-of-a-complex-eloquent-query-in-your-laravel-application
Hello Steve,
It looks like the issue in your Eloquent scope is related to how you're using the where clause with the subquery. You need to ensure that the subquery is correctly used to filter the main query based on the maximum ID condition. Here's how you can adjust your scope to match the SQL query you provided:
/**
* Scope - customers who have consented to receive the newsletter / wants / interests.
*/
public function scopeConsents($query, $type = 'News')
{
$query->leftJoin('customer_consent_records', function ($join) use ($type) {
$join->on('customer_consent_records.customer_id', '=', 'customers.id')
->where('customer_consent_records.type', '=', $type)
->where('customer_consent_records.consent', '=', 1);
})
->whereExists(function ($subQuery) use ($type) {
$subQuery->select(DB::raw(1))
->from('customer_consent_records as ccr2')
->whereColumn('ccr2.customer_id', 'customers.id')
->where('ccr2.type', '=', $type)
->where('ccr2.consent', '=', 1)
->havingRaw('ccr2.id = MAX(ccr2.id)');
});
}
leftJoin is set up to join the customer_consent_records table where the type matches and consent is true.whereExists method is used to ensure that there exists a consent record that matches the maximum ID condition for the given type and consent. This replaces the need for a direct comparison in the WHERE clause of the main query.havingRaw: This is used to ensure that the ID of the consent record matches the maximum ID found for that customer and type, which ensures we're only considering the latest consent record.You can use this scope in your query like this:
$customers = Customer::select('customers.id', 'customers.last_name', 'customers.first_name')
->consents('News')
->limit(100)
->get();
This should give you the correct results as per your SQL query. Make sure to test this thoroughly to ensure it behaves as expected.
Appreciate all the suggestions to this issue. However, I really wanted to write this scope in eloquent. After much fiddling around I seem to have this working. Just thought I'd share the final solution to this. I ended up using a separate sub-query that was then joined in. And finally, I needed an extra join.
/**
* Scope - customers who have consented to receive the newsletter / wants / interests.
*/
public function scopeConsents(Builder $query, $type = null): void
{
$latestRecord = \DB::table('customer_consent_records')
->select('customer_id', \DB::raw('MAX(id) as latest_record'))
->where('type', $type)
->groupBy(['customer_id']);
$query->leftJoinSub($latestRecord, 'ccr', function(JoinClause $join) {
$join->on('customers.id', '=', 'ccr.customer_id');
});
$query->leftJoin('customer_consent_records as ccr2', 'ccr2.id', '=', 'latest_record');
$query->whereNotNull('latest_record')->where('consent', true)->orderBy('customers.id');
}
Thanks again, Steve
Please or to participate in this conversation.