So the error I am getting is SQLSTATE[42S02]: Base table or view not found: 1109 Unknown table 'team_members' in field list
So it seems the join in not actually happening. Does anyone see the problem in the code?
$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();
EDIT:
Mark Baker pointed out I never specified the table. The correct query(with a couple other bugs fixed):
$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();