filter data with check condition with a pivot column laravel

I am stuck at BelongsToMany Relationship.
I have Models
USER MODEL

public function subscriptionsStatus()
    {
        return $this->belongsToMany(SubscriptionPackage::class)->withTimestamps()->withPivot('subscription_status','expires_on','id');
    }

SUBSCRIPTION Package model

public function users()
    {
        return $this->belongsToMany(User::class, 'subscription_package_user','subscription_package_id','user_id');
    }

I have tried to return all users with their subscriptions and courses.. for that I have tried the code below which is working fine.

$user=User::with(['studentDetails','subscriptionsSatus.courses'])
                        ->withPagination($offset,$perPage)
                        ->get()
                        ->sortBy('first_name')->values();

the above code is returning response is given below

[
{
    "id": 44,
    "first_name": "Abcd Test",
    "last_name": "Test lastname",
    "student_details": null,
    "subscriptions_satus": [
        {
            "id": 1,
            "name": "Gold",
            "price": 3000,
            "user_id": "2"
            "pivot": {
                "user_id": 44,
                "subscription_package_id": 1,
                "subscription_status": "on_free_trial",
                "expires_on": null,
                "id": 9
            },
            "courses": [
                {
                    "id": 18,
                    "title": "English Grammar for Class 3",
                    "price": 400,
                    "strikethrough_price": null,
                    "status": "draft",
                    "user_id": 2,
                    "image": "http://127.0.0.1:8000/courses/1615702915.png",
                    "description": null,
                    "pivot": {
                        "subscription_package_id": 1,
                        "course_id": 18,
                    }
                }
            ]
        }
    ]
}]

BUT I want to return users who have subscription_status=$filter in the pivot table. for that, I have tried.

$filter=$request->input('filter')??"active";
$user=User::with(['studentDetails','subscriptionsStatus.courses'])
                         ->whereHas('subscriptionsStatus', function($query) use($filter){
                             $query->wherePivot('subscription_status','=',$filter);
                         })
                            ->withPagination($offset,$perPage)
                            ->get()
                            ->sortBy('first_name')->values();

Later I found that wherePivot can only used in models.now I am stuck here the above code gives me error=unknown column ‘pivot‘ .
How do I can return all users having subscription_status=$filter??

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

as you have only 2 status so you can create 2 more function with relationship like

public function activeSubscriptions()
{
    return $this->belongsToMany(SubscriptionPackage::class)
        ->withTimestamps()
        ->wherePivot('subscription_status','active');
} 

public function freeTrailSubscriptions()
{
    return $this->belongsToMany(SubscriptionPackage::class)
        ->withTimestamps()
        ->wherePivot('subscription_status','on_free_trail'); // change this base on your value
}

then in controller you can filter by like this

$filter=$request->input('filter') ?? "active";

$user=User::with(['studentDetails','subscriptionsStatus.courses'])

    ->when($filter == 'active',function ($q) {
        $q->with('activeSubscriptions');
    })
    ->when($filter == 'on_free_trail',function ($q) {
        $q->with('freeTrailSubscriptions');
    })
    ->withPagination($offset,$perPage)
    ->get()
    ->sortBy('first_name')->values();

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