php - 拉拉维尔 5.5 : Query Building with Join and Where Clause

Question

我有两张 table

'Orders' with Columns 

['subtotal', 'taxes', 'delivery_fee', 'total', 'discount', 'status', 'delivery_status','payment_status', 'special_instructions', 'address_id', 'store_id', 'user_id', 'delivery_profile_id','payment_method_id', 'type', 'scheduled_on'];

还有

'Stores' with Columns

['name', 'tagline', 'image_url', 'delivery_time', 'minimum_order','delivery_fee', 'details', 'delivery_limit', 'area', 'address', 'longitude', 'latitude', 'preorder','serves_non_veg', 'cost_for_two', 'status', 'owner_id', 'apibot', 'admin_id','admin_mobile', 'opens_at', 'closes_at'];

我正在运行 Laravel 5.5 框架，并且很难花时间构建查询。以下是我根据移动 API 请求的状态返回订单列表的完整代码

        $orders = Order::where('store_id', Auth::user()->store->id);

        if($request->status) {
            $orders = $orders->where('status', $request->status);
        }

        // filter for active orders
        if($request->active_orders) {
            $orders = $orders->whereIn('status', ['new', 'accepted', 'preparing', 'dispatched']);
        }

        // for delivery tab in store app
        if($request->deliveries) {
            $orders = $orders->whereIn('status', ['accepted', 'preparing', 'dispatched'])
                ->where('delivery_profile_id', '<>', null)
                ->whereIn('delivery_status', ['allotted', 'started', 'complete']);
        }

在下面的代码中，我需要添加一个 Join 和Where 子句组合来将 Store 与订单连接起来

$orders = Order::where('store_id', Auth::user()->store->id);

例如:一般SQL查询如下

   Select * From Orders, Stores Where Stores.id=Orders.store_id and Stores.admin_id = 17

我尝试了可能的解决方案，但没有成功。提前谢谢您。

Answer 1

您首先需要在 Store 模型上创建一个 belongsTo 来创建关系:

public function store() {
   return $this->belongsTo(\App\Store::class);
}

那么你的查询将如下所示:

$orders = Order::with(['store' => function($query) {
    return $query->where('admin_id', auth()->user->id)
}]);