我试图获取在购物车中的食物列表以及收藏夹。我使用以下查询访问它。在条件条款节中,我想检查食物是否在收藏表中,它也显示出来,还是在购物车表中显示。但是它无法在Where子句内部征用食物ID在购物车表中,而不是在该用户中。因此,它应该返回cart_id为null,但它显示了cart_id,尽管该用户没有添加到他的购物车表中。在收藏表中也发生了同样的事情。我该如何正确?
$foods = DB::table('foods')
->leftjoin('carts','carts.food_id','foods.id')
->leftjoin('favorites','favorites.food_id','foods.id')
->select('foods.food_name', DB::raw('carts.id as cart_id'),DB::raw('favorites.id as favorite_id'),'foods.id','foods.display_image','foods.price','foods.category_id','foods.description','foods.restaurant_id' )
->where('foods.restaurant_id','=',$request->Input(['restaurant_id']))
->orwhere(function ($query) {
$query->where('carts.user_id','=',Auth::user()->id)
->where('favorites.user_id','=',Auth::user()->id);
})
->get();
假设您正在查询属于餐厅和用户添加或喜欢的食物,您在SQL中的条件条款是:
WHERE restaurant_id=:restaurant_id
AND (carts.user_id=:user_id OR favorites.user_id=:user_id)
您的Laravel条件条款将是:
->where('foods.restaurant_id', $request->input('restaurant_id')
->where(function ($query) {
$query->where('carts.user_id', Auth::user()->id)
->orWhere('favorites.user_id', Auth::user()->id);
})
尝试以下:
$foods = DB::table('foods')
->leftJoin('carts', function($join) {
$join->on('carts.food_id', 'foods.id')
->where('carts.user_id', Auth::id());
})
->leftJoin('favorites', function($join) {
$join->on('favorites.food_id', 'foods.id')
->where('favorites.user_id', Auth::id());
})
->select(...)
->where('foods.restaurant_id', $request->input('restaurant_id'))
->get();
btw:您不需要DB::raw()
对于列别名:
->select('carts.id as cart_id')