Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
Hello
I'm building search functionality for my app. To simplify things: there are two tables: shops and subscriptions
Each shop can have multiple subscription records, subscription has field expires_at. Now, I assume that shop has active subscription if subscription exsists and at least one of shop's subscripion expires_at date is bigger than now().
It is one of the conditions to the whole query. Here is code:
$shops = Shop::when($subscription, function($query, $subscription) {
$query->doesntHave('subscriptions')->orWhereHas('subscriptions', function($q) use ($subscription, $query) {
$query->where('expires_at', '<', now());
});
});
It doesn't work as expected because if i.e. shop has three related subscriptions and at least one of them is expired – it assumes that shop has no active subscription (even though it has).
I would need to implement some nested function inside or whereHas, I guess, to sort by expires_at desc and then limit to one and only then pass where expires_at clause, however I've no idea how.
And I rather need to stick with Eloquent Query Builder rather than DB facade or raw sql.
What if you try this way?
$shops = Shop::WhereHas('subscriptions')->withCount('subscriptions as
active_subscriptions_count' => function ($query) {
$query->where('expires_at', '<', now());
}])->having('active_subscriptions_count', '>=', 3)->get();
@tisuchi Thank you for your comment. Unfortunately it doesn't work. Any other solution Sir?
I have use join now. Can you try this?
$query->select('shops.*')
->leftJoin('subscriptions', 'shops.id', 'subscriptions.shop_id')
->whereNull('subscriptions.id')
->orWhere('subscriptions.expires_at', '<', now())
->where('subscriptions.id', function($q) {
$q->select('id')
->from('subscriptions')
->whereColumn('shop_id', 'shops.id')
->latest('expires_at')
->limit(1);
});
@tisuchi query should be ok, but with the sign changed
$shops = Shop::WhereHas('subscriptions')->withCount('subscriptions as
active_subscriptions_count' => function ($query) {
$query->where('expires_at', '>', now());
}])->having('active_subscriptions_count', '>=', 3)->get();
or just ?
$shops = Shop::whereHas('subscriptions' function($query){
$query->where('expires_at','>',now());
})->get();
Thank you all. Finally it works. I just tweaks a bit tisuchi's answer. Appreciated.
Please or to participate in this conversation.