CODEWITHSUNDEEP
laraveleloquent
JEJ
JEJ

Reputation: 904

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??

Upvotes: 0

Views: 667

Answers (1)

Kamlesh Paul
Kamlesh Paul

Reputation: 12391

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();

Upvotes: 1

Related Questions