Laravel Join在DB::Raw下不能正常工作 - Laravel Join not working as expected with DB::Raw

所以我得到的错误是SQLSTATE[42S02]: Base table or view not found: 1109 Unknown table 'team_members' in field list

所以这似乎并没有真正发生。有人看到代码中的问题了吗?

$data = DB::select(DB::raw('
                Select
                  team_members.name As teamMemberName,
                  Sum(TimestampDiff(minute, production_hours.time_start,
                  production_hours.time_stop)) / 60 As hoursWorked,
                  production_activities.name As groupName,
                  production_hours.team_member_id,
                  production_hours.production_activities_id,
                  production_hours.hourly_rate
                  '))
              ->join('production_days', 'production_hours.production_day_id', '=', 'production_days.id')
              ->join('production_activities', 'production_hours.production_activities_id', '=', 'production_activities_id')
              ->where('DATE(production_day.begin)', '>', $beginDate)
              ->where('DATE(production_day.begin)', '<', $endDate)
              ->groupBy('team_members.name')
              ->groupBy('production_activities.name')
              ->get();

编辑:

Mark Baker指出我从来没有指定过桌子。正确的查询(修复了几个其他错误):

$data = DB::table('production_days')->select(DB::raw('
                  team_members.name As teamMemberName,
                  Sum(TimestampDiff(minute, production_hours.time_start,
                  production_hours.time_stop)) / 60 As hoursWorked,
                  production_activities.name As groupName,
                  production_hours.team_member_id,
                  production_hours.production_activities_id,
                  production_hours.hourly_rate
                  '))
              ->join('production_hours', 'production_hours.production_day_id', '=', 'production_days.id')
              ->join('production_activities', 'production_hours.production_activities_id', '=', 'production_activities_id')
              ->join('team_members', 'production_hours.team_member_id', '=', 'team_members.id')
              ->where(DB::raw('DATE(`production_days`.`begin`)'), '>', $beginDate)
              ->where(DB::raw('DATE(`production_days`.`begin`)'), '<', $endDate)
              ->groupBy('team_members.name')
              ->groupBy('production_activities.name')
              ->get();

最佳范例

。

    $issueitems = 'DB::table('products_items')->where("products_items.deleted", "0");
    $data=$issueitems->select(array(
                "itemcategories.name as itemcategoryname",
                "itemsubcategories.name as itemsubcategoryname",
                "items.name as itemsname",
                "items.id as item_id",
                "products_items.stock as stock",
                "measureds.name as measureds",
                'DB::raw("COALESCE(issue_stock.price,0) as price"),
                'DB::raw("COALESCE(issue_stock.iquantity,0) as issuequantity")))
            ->join("products", function($join) {
                $join->on('products.id', '=', 'products_items.product_id')
                ->on("products.deleted", "=", "products_items.deleted");
            })
            ->join("items", "items.id", "=", "products_items.item_id")
            ->join("itemcategories", "itemcategories.id", "=", "items.item_category_id")
            ->join("itemsubcategories", "itemsubcategories.id", "=", "items.item_subcategory_id")
            ->join("measureds", "measureds.id", "=", "items.measured_id")
            ->leftjoin(
                    'DB::raw('(SELECT 
                            productissues.product_id,itemstocks.item_id,sum(productissue_items.quantity) as iquantity
                            ,sum(itemstocks.price * (COALESCE(productissue_items.quantity,0))) as price
                            FROM 
                            productissues
                                INNER JOIN productissue_items ON productissues.id = productissue_items.productissues_id
                                INNER JOIN itemstocks ON itemstocks.id = productissue_items.itemstock_id
                                WHERE itemstocks.deleted = "0" AND productissue_items.deleted = "0"
                                group by productissues.product_id,itemstocks.item_id
                            ) issue_stock'), function($join) {
                $join->on("issue_stock.product_id", "=", "products_items.product_id")
                ->on("issue_stock.item_id", "=", "products_items.item_id");
            })->get();