PHP User::leftJoin方法代码示例

 /**
  * Display the specified resource.
  *
  * @param  int  $id
  * @return Response
  */
 public function show(Request $request, $id)
 {
     $data = $request->all();
     $id_period = $data['id_period'];
     if ($id == 1) {
         // Add code for load teacher whitout period
         $teachers = \App\User::leftJoin('registry_teachers', function ($join) use($id_period) {
             $join->on('users.id', '=', 'registry_teachers.id_teacher')->where('registry_teachers.id_period', '=', $id_period);
         })->whereNull('registry_teachers.id_teacher')->Where('users.active', 1)->Where('users.type', '<>', 'Student')->select('users.id', 'users.first_name', 'users.last_name', 'users.type')->get()->toJson();
         //dd(count(json_decode($teachers,true)));
     } else {
         // Add code for load teacher whit period
         $teachers = \App\RegistryTeacher::Join('users', 'users.id', '=', 'registry_teachers.id_teacher')->select('registry_teachers.id as id_registry_teacher', 'users.id', 'users.first_name', 'users.last_name', 'users.type')->Where('registry_teachers.id_period', $id_period)->get()->toJson();
     }
     $status = count(json_decode($teachers, true)) > 0 ? true : false;
     if ($request->ajax()) {
         return response()->json(['status' => $status, 'data' => $teachers]);
     }
 }

 public function getIndex()
 {
     $limit = Input::get('limit', 20);
     $page = Input::get('page', 1) - 1;
     $search = Input::get('search', '');
     $order = Input::get('order', 'username|asc');
     $rows = User::leftJoin("user_role_user", "user_role_user.user_id", "=", "user_users.id")->leftJoin("user_roles", "user_roles.id", "=", "user_role_user.role_id")->leftJoin("user_users_info", "user_users.id", "=", "user_users_info.user_id")->where("user_roles.id", "!=", "1");
     if ($search != "") {
         $where_search = '(username LIKE ? OR email LIKE ? OR level LIKE ? OR user_users_info.name LIKE ? OR user_users_info.surename LIKE ?)';
         $rows->whereRaw($where_search, array("%{$search}%", "%{$search}%", "%{$search}%", "%{$search}%", "%{$search}%"));
         $total = $rows->count();
     } else {
         $total = $rows->count();
     }
     $order = explode("|", $order);
     $rows->take($limit)->skip($page * $limit)->orderBy($order[0], $order[1]);
     $rows = $rows->get(array("user_users.*", "user_users_info.name", "user_users_info.surename", DB::raw("user_roles.id AS role_id"), DB::raw("user_roles.name AS level")));
     return View::make('admin.users.index')->with("rows", $rows)->with("search", $search)->with("page", $page)->with("limit", $limit)->with("total", $total)->with("show", min(($page + 1) * $limit, $total))->with("torder", $order[1] == "asc" ? "desc" : "asc");
 }

 public static function prefsFirst()
 {
     return User::leftJoin('task_prefs', 'users.id', '=', 'task_prefs.user_id')->available()->groupBy('users.id')->orderByRaw('COUNT(`task_prefs`.`task_id`) DESC, RAND() ASC')->get();
 }

 public function getTop10UsersWithMostFeedbacks()
 {
     return User::leftJoin('project_user', 'users.id', '=', 'project_user.user_id')->select('users.name', DB::raw('COUNT(project_user.project_id) as aggregate'))->groupBy('id')->orderBy('aggregate', 'DESC')->limit(10)->get()->toJSON();
 }

 public function assignPeople(Request $request)
 {
     $user_roll = $request->input('user_roll');
     $task_id = $request->input('task_id');
     $assigned_list = $request->input('assigned_list');
     $assigned_ids = explode(',', $assigned_list);
     if (!CheckLevel::check(2, NULL, $user_roll)) {
         return JSONResponse::response(401);
     }
     $task = Task::where('task_id', '=', $task_id)->where('enabled', '=', true)->first();
     if ($task == NULL) {
         return JSONResponse::response(400);
     }
     $user_list = User::leftJoin('team_members', 'team_members.user_id', '=', 'users.user_id')->whereIn('users.user_roll', $assigned_ids)->where('team_members.team_id', '=', $task->team_id)->get();
     if (count($user_list) != count($assigned_ids)) {
         return JSONResponse::response(400);
     }
     // Remove existing assigned and overwrite it with the new list
     // Dev can know who are assigned with method getAssignedForTask
     //Delete the existing ones
     $success = Assigned::where('task_id', '=', $task_id)->delete();
     //Insert the new ones
     foreach ($user_list as $key => $user) {
         // echo "\ninserting".$task_id." ".$user->user_id;
         $a = new Assigned();
         $a->task_id = $task_id;
         $a->user_id = $user->user_id;
         $success = $a->save();
         // echo "\n".$success;
     }
     // Push Notification Code starts here
     $task_user_rolls = Assigned::where('task_id', '=', $task_id)->leftJoin('users', 'assigned.user_id', '=', 'users.user_id')->lists('user_roll');
     $push_message = Push::jsonEncode('newtask', $task);
     Push::sendMany($task_user_rolls, $push_message);
     // Push Notification Code ends here
     return JSONResponse::response(200, true);
 }

 /**
  * Запрос для выборки данных для таблицы
  *
  * @return Builder
  */
 public function query()
 {
     return User::leftJoin('user_companies', 'user_companies.id', '=', 'users.company_id');
 }

 public function getAccessusers(Request $req)
 {
     if ($req->ajax()) {
         $res = [];
         $out = '<option value="">Pilih Users</option>';
         $users = User::leftJoin('data_akses_gudang', 'data_akses_gudang.id_user', '=', 'users.id_user')->where('users.status', 1)->whereIn('users.permission', [1, 2])->WhereNull('data_akses_gudang.id_user')->orderby('users.name', 'asc')->select('users.*')->get();
         foreach ($users as $user) {
             $out .= '<option value="' . $user->id_user . '">' . $user->name . '</option>';
         }
         $res['content'] = $out;
         return json_encode($res);
     }
 }

 public function redoAssignments(Requests\RedoTeamAssignmentRequest $request)
 {
     $minTeamSize = $request->all()['min'];
     $maxTeamSize = $request->all()['max'];
     // Return to teams page if form was empty
     if ($minTeamSize == "" || $maxTeamSize == "") {
         return redirect('/teams');
     }
     // max must be greater than min for team assignments to work
     if (!($minTeamSize < $maxTeamSize)) {
         return redirect('/teams');
     }
     // Create array of student IDs
     $studentIDs = \App\User::leftJoin('UserRole', 'users.id', '=', 'UserRole.userID')->where('role', 'student')->get()->pluck('id')->toArray();
     // max must be less than the number of students
     if (!($minTeamSize <= count($studentIDs))) {
         return redirect('/teams');
     }
     // Sort students by the number of courses they have taken.
     $sortedStudentIDs = array();
     for ($i = 0; $i <= 4; $i++) {
         foreach ($studentIDs as $studentID) {
             $coursesTaken = \App\userCourseTaken::where('userID', $studentID)->get()->toArray();
             if (count($coursesTaken) == $i) {
                 array_push($sortedStudentIDs, $studentID);
             }
         }
     }
     // Create an array to store the appropriate number of teams
     $teams = array();
     $maxNumberOfTeams = floor(count($studentIDs) / $minTeamSize);
     $teams = array_fill(0, $maxNumberOfTeams, array_fill(0, $maxTeamSize, -1));
     // Place each student in a team using the following priorities:
     // - 1 - try to place student in team with matching language and team style
     // - 2 - try to place student in team with matching language
     // - 3 - try to place student in a brand new team
     // - 4 - try to place student in a team with matching team style
     // - 5 - place the student anywhere
     foreach ($sortedStudentIDs as $studentID) {
         $studentPlaced = false;
         $studentLanguage = \App\userKnownLanguages::where('userID', $studentID)->get()->pluck('languageName')->toArray()[0];
         $studentStyle = \App\userPreferredTeamStyle::where('userID', $studentID)->get()->pluck('teamStyle')->toArray()[0];
         // language + team style
         for ($team = 0; $team < count($teams); $team++) {
             if ($teams[$team][0] == -1) {
                 continue;
             }
             $teamLanguage = \App\userKnownLanguages::where('userID', $teams[$team][0])->get()->pluck('languageName')->toArray()[0];
             $teamStyle = \App\userPreferredTeamStyle::where('userID', $teams[$team][0])->get()->pluck('teamStyle')->toArray()[0];
             if ($studentLanguage == $teamLanguage && $studentStyle == $teamStyle) {
                 for ($slot = 0; $slot < count($teams[$team]); $slot++) {
                     if ($teams[$team][$slot] == -1) {
                         $studentPlaced = true;
                         $teams[$team][$slot] = $studentID;
                         break;
                     }
                 }
                 if ($studentPlaced) {
                     break;
                 }
             }
         }
         if ($studentPlaced) {
             continue;
         }
         // language
         for ($team = 0; $team < count($teams); $team++) {
             if ($teams[$team][0] == -1) {
                 continue;
             }
             $teamLanguage = \App\userKnownLanguages::where('userID', $teams[$team][0])->get()->pluck('languageName')->toArray()[0];
             if ($studentLanguage == $teamLanguage) {
                 for ($slot = 0; $slot < count($teams[$team]); $slot++) {
                     if ($teams[$team][$slot] == -1) {
                         $studentPlaced = true;
                         $teams[$team][$slot] = $studentID;
                         break;
                     }
                 }
                 if ($studentPlaced) {
                     break;
                 }
             }
         }
         if ($studentPlaced) {
             continue;
         }
         // new team
         for ($team = 0; $team < count($teams); $team++) {
             if ($teams[$team][0] == -1) {
                 $studentPlaced = true;
                 $teams[$team][0] = $studentID;
                 break;
             }
         }
         if ($studentPlaced) {
             continue;
         }
         // team style
         for ($team = 0; $team < count($teams); $team++) {
//.........这里部分代码省略.........

 /**
  * Gets all of the users that are not set as the requester's contacts.
  *
  * @param Request $request
  * @return \Illuminate\Http\Response
  */
 public function getAvailableUsers(Request $request)
 {
     $requesterId = $request->input('id');
     $username = $request->input('username');
     $users = User::leftJoin('contacts', 'contacts.contact_id', '=', 'user.id')->where('user.id', '!=', $requesterId)->where('user.username', 'LIKE', '%' . $username . '%')->distinct()->get(['user.*']);
     if ($users != null) {
         $returnValue['status'] = 200;
         $returnValue['message'] = $users;
         return Response::json($returnValue, 200);
     }
     $returnValue['status'] = 405;
     $returnValue['error_message'] = 'No users are registered with given or similar nickname.';
     return Response::json($returnValue, 405);
 }