laravel - 使用数据透视表过滤数据 Laravel Eloquent

Question

我想根据subscription_status存储在数据透视表中的用户过滤用户。我有三张桌子users，，subscription_packagessubscription_package_user

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

此代码返回响应是

 [
{
    "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,
                    }
                }
            ]
        }
    ]
}]

我只想返回拥有subscription_status =$filter.

$filter='acive'or 'on_free_trail'

我的模型是

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

我努力了

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

但得到错误列未找到“枢轴”

标签： laraveleloquent

Answer 1

您需要wherePivot与以下内容一起使用orWhere：

public function subscriptionsStatus()
{
    return $this->belongsToMany(SubscriptionPackage::class)
                    ->withTimestamps()
                    ->withPivot('subscription_status','expires_on','id')
                    ->wherePivot(function($q){
                        return $q->where('subscription_status','=','active')
                            ->orWhere('subscription_status','=','on_free_trail');
                    });
}

更新

或在您的控制器中：

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