Laravel - Add additional where constraints from Eloquent Model belongsToMany relationship

Question

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

id	first_name	last_name
1	John	Doe
2	Jane	Doe

I have my subscriptions table

id	name
1	Basic
2	Bronze
3	Silver
4 ​	Gold
5 ​	Platinum

And a subscription_user pivot table

id	user_id	subscription_id	created_at	expired_at
1	1	1	2021-02-01 23:22:12	2021-03-21 08:22:12
2	1	5	2021-03-21 08:22:12	2021-04-04 09:03:21
2	1	3	2021-04-04 09:03:21
2	2	1	2021-01-01 01:00:00	2021-01-05 05:30:00
2	2	2	2021-01-05 05:30:00	2021-01-06 08:34:10

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('App\Subscription')
    ->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.

Answer 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.