Laravel – Add additional where constraints from Eloquent Model belongsToMany relationship

I am new to Laravel (using v7) so I apologise if I am doing this incorrectly.
I have a custom subscription setup for my users.

I have my users table

I have my subscriptions table

And a subscription_user pivot table

So as per the above entries, John Doe subscribed on the Basic subscription, then upgraded to the Platinum and then finally downgrading to the Silver. Which is his current subscription.

I have the following methods (within User.php) for first getting a list of the users subscriptions

public function subscriptions()
{
  return $this->belongsToMany('AppSubscription')
    ->orderby('pivot_created_at', 'desc')
    ->withPivot('created_at', 'expired_at')
    ->withTimestamps();
}

And then to get the current subscription

public function subscription()
{
  return $this->subscriptions->first();
}

However there could be instances where by a user has all subscriptions expired, therefore they have no current subscription in place. i.e in the above records Jane Doe who subscribed to 2 plans but both have expired.

If i call $user->subscription() on Jane Doe it still returns the latest expired entry.
I have tried added where() clauses into my subscription method but it has no effect:

public function subscription()
{
  return $this->subscriptions->where('expired_at', NULL)->first();
}

Is there a better way to do what I am trying to achieve? I need a method to fetch all the users subscriptions, whether they are active or expired. (which I have – is this the best way?)

But then also fetch the current subscription which should always not have an expired_at.
It seems I cannot add any further conditions within mt subscription method.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

You need to use the query builder for the subscription method using subscriptions() instead of the collection subscriptions

public function subscription()
{
  return $this->subscriptions()->whereNull('expired_at')->first();
}

calling $this->subscriptions is equivalent to $this->subscriptions = $this->subscriptions()->get(). where $this->subscriptions returns a collection.

$this->subscriptions(), using the method, return a query builder.

PS: it did not trigger an error calling first() because a Collection has a method called first() too.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0