MerryChristmas's avatar

WhereHas() and comparing columns

Subscription has many Invoices. I'm trying to retrieve Subscription with the last Invoice.

Something like this:

Subscription::whereHas('invoice', function($q){
$q->where('subscription.expires_at', 'invoice.expires_at');
})->get();

// Subscriptions
+----+------------+
| id | expires at |
+----+------------+
| 1  | 1999-12-12         |
+----+------------+

// Invoices
+----+-----------------+------------+
| id | subscription_id | expires_at|
+----+-----------------+------------+
| 5  | 1               | 1999-12-12 |
+----+-----------------+------------+
| 6  | 1               | 1998-12-12 |
+----+-----------------+------------+
| 7  | 1               | 1997-12-12 |
+----+-----------------+------------+
0 likes
4 replies
MerryChristmas's avatar

@webrobert

OK, but in this case question still stands:

public function latestInvoice(): HasOne
{
    return $this->hasOne(Invoice::class)->ofMany(
// how do i define here where('subscription.expires_at' == 'invoice.expires_at')
);
}
MerryChristmas's avatar
MerryChristmas
OP
3 years ago
Best Answer
Level 1
Subscription::whereHas('invoice', function ($q) {
            $q->whereColumn('subscriptions.expires_at', 'invoices.expires_at');
        })->get()

Please or to participate in this conversation.