public function scopePhase($query, $phase) { if (trim($phase) != "") { $query->where(\DB::raw("CONCAT(phase)"), "LIKE", "%{$phase}%"); Session::flash('message', 'Fase:' . ' ' . $phase . ' ' . 'Resultado de la busqueda'); } }
public static function getMealsWithTotals($selectedDate, $user_id) { $meals = Meal::select(\DB::raw('*, meals.id as meal_id'))->leftJoin('foods', 'meals.food_id', '=', 'foods.id')->where('planed_food', '0')->where('date', $selectedDate)->where('meals.user_id', $user_id)->orderBy('meals.created_at', 'desc')->paginate(100); $meals_planed = Meal::select(\DB::raw('*, meals.id as meal_id'))->leftJoin('foods', 'meals.food_id', '=', 'foods.id')->where('planed_food', '1')->where('date', $selectedDate)->where('meals.user_id', $user_id)->orderBy('meals.created_at', 'desc')->paginate(100); $totals = ['sum_weight' => 0, 'sum_kcal' => 0, 'sum_proteins' => 0, 'sum_carbs' => 0, 'sum_fibre' => 0, 'sum_fats' => 0]; $totals_planed = ['sum_weight' => 0, 'sum_kcal' => 0, 'sum_proteins' => 0, 'sum_carbs' => 0, 'sum_fibre' => 0, 'sum_fats' => 0]; foreach ($meals as $meal) { $totals['sum_weight'] += $meal['weight']; $totals['sum_kcal'] += $meal['kcal'] * $meal['weight'] / 100; $totals['sum_proteins'] += $meal['proteins'] * $meal['weight'] / 100; $totals['sum_carbs'] += $meal['carbs'] * $meal['weight'] / 100; $totals['sum_fats'] += $meal['fats'] * $meal['weight'] / 100; $totals['sum_fibre'] += $meal['fibre'] * $meal['weight'] / 100; } foreach ($meals_planed as $meal) { $totals_planed['sum_weight'] += $meal['weight']; $totals_planed['sum_kcal'] += $meal['kcal'] * $meal['weight'] / 100; $totals_planed['sum_proteins'] += $meal['proteins'] * $meal['weight'] / 100; $totals_planed['sum_carbs'] += $meal['carbs'] * $meal['weight'] / 100; $totals_planed['sum_fats'] += $meal['fats'] * $meal['weight'] / 100; $totals_planed['sum_fibre'] += $meal['fibre'] * $meal['weight'] / 100; } //TODO calculate totals above. //$totals = []; return ['meals' => $meals, 'meals_planed' => $meals_planed, 'totals' => $totals, 'totals_planed' => $totals_planed]; }
public function getDesigns(array $options = array()) { $designs = $this->select(\DB::raw(' designs.*, c.value AS cityName, d.value AS districtName, w.value AS wardName, c.slug AS citySlug, d.slug AS districtSlug, w.slug AS wardSlug '))->leftJoin('locations AS c', function ($join) { $join->on('designs.city', '=', 'c.id')->where('c.type', '=', 1); })->leftJoin('locations AS d', function ($join) { $join->on('designs.district', '=', 'd.id')->where('d.type', '=', 2); })->leftJoin('locations AS w', function ($join) { $join->on('designs.district', '=', 'w.id')->where('w.type', '=', 3); }); if (isset($options['citySlug'])) { $designs = $designs->where('c.slug', $options['citySlug']); } if (isset($options['districtSlug'])) { $designs = $designs->where('d.slug', $options['districtSlug']); } if (isset($options['wardSlug'])) { $designs = $designs->where('w.slug', $options['wardSlug']); } return $designs->orderBy('designs.id', 'desc'); }
public function scoperegistro($query, $registro) { if (trim($registro) != "") { $query->where(\DB::raw("CONCAT(veh_movil)"), "ILIKE", "%{$registro}%"); //$query->where('full_name',"LIKE", "%$name%"); } }
public function scopeName($query, $name) { if (trim($name) != "") { $query->where(\DB::raw("CONCAT(name)"), "LIKE", "%{$name}%"); Session::flash('message', 'Nombre:' . ' ' . $name . ' ' . 'Resultado de la busqueda'); } }
public function scopeEfficiency($query, $efficiency) { if (trim($efficiency) != "") { $query->where(\DB::raw("CONCAT(efficiency)"), "LIKE", "%{$efficiency}%"); Session::flash('message', 'Rendimiento:' . ' ' . $efficiency . ' ' . 'Resultado de la busqueda'); } }
public function scopeNPerGroupRedefine($query, $group, $n = 10, $custom) { // queried table $table = $this->getTable(); // initialize MySQL variables inline $query->from(\DB::raw("(SELECT @rank:=0, @group:=0) as vars, ({$custom}) as `{$table}`")); // if no columns already selected, let's select * if (!$query->getQuery()->columns) { $query->select("*"); } // make sure column aliases are unique $groupAlias = 'group_' . md5(time()); $rankAlias = 'rank_' . md5(time()); // apply mysql variables $query->addSelect(\DB::raw("@rank := IF(@group = {$group}, @rank+1, 1) as {$rankAlias}, @group := {$group} as {$groupAlias}")); // make sure first order clause is the group order $query->getQuery()->orders = (array) $query->getQuery()->orders; array_unshift($query->getQuery()->orders, ['column' => $group, 'direction' => 'asc']); // prepare subquery $subQuery = $query->toSql(); // prepare new main base Query\Builder $newBase = $this->newQuery()->from(\DB::raw("({$subQuery}) as {$table}"))->mergeBindings($query->getQuery())->where($rankAlias, '<=', $n)->getQuery(); // replace underlying builder to get rid of previous clauses $query->setQuery($newBase); }
public function scopeCategory($query, $category) { if (trim($category) != "") { $query->where(\DB::raw("CONCAT(category)"), "LIKE", "%{$category}%"); Session::flash('message', 'Categoria:' . ' ' . $category . ' ' . 'Resultado de la busqueda'); } }
public function scopeAn8($query, $an8) { if (trim($an8) != "") { $query->where(\DB::raw("CONCAT(emp_nombre,' ',emp_apellido,emp_an8,emp_identificacion)"), "ILIKE", "%{$an8}%"); //$query->where('full_name',"LIKE", "%$name%"); } }
public function getFeira($id) { $dados = \DB::connection('mysql')->select(\DB::raw("SELECT *, date_format(data, '%d-%m-%Y %H:%i') as dataBR FROM feira WHERE id={$id}"))[0]; // $function = new Functions(); // $dados->data = $function->convertDataToBR("2016-01-02"); return $dados; }
public function scopecontrol($query, $control) { if (trim($control) != "") { $query->where(\DB::raw("CONCAT(ctl_id)"), "ILIKE", "%{$control}%"); //$query->where('full_name',"LIKE", "%$name%"); } }
function getName() { DB::table('users')->whereExists(function ($query) { $query->select(DB::raw(1))->from('orders')->whereRaw('orders.user_id = users.id'); })->get(); //select * from users where exists (select 1 from orders where orders.user_id = users.id) //生成上面那句语句 exists 判断括号内语句是否为真 为真则搜索 为假则放弃 }
public function newQuery($excludeDeleted = true) { $raw = ''; foreach ($this->geofields as $column) { $raw .= ' astext(' . $column . ') as ' . $column . ' '; } return parent::newQuery($excludeDeleted)->addSelect('*', DB::raw($raw)); }
public function scopeName($query, $name) { if (trim($name) != "") { $query->where(\DB::raw("usr_name"), "LIKE", "%{$name}%"); //consulta Db::raw //$query->where('full_name',"LIKE", "%$name%"); } }
private function getRandomWord() { if (self::USE_DATABASE == 0) { $request = Requests::get('http://randomword.setgetgo.com/get.php'); return strtolower($request->body); } return \App\Word::orderBy(\DB::raw('RAND()'))->first()->word; }
/** * Returns an array of all possible question types. * * @return array */ public static function possibleTypes() { $type = \DB::select(\DB::raw('SHOW COLUMNS FROM questions WHERE Field = "question_type"'))[0]->Type; preg_match('/^enum\\((.*)\\)$/', $type, $matches); $values = []; foreach (explode(',', $matches[1]) as $value) { $values[] = trim($value, "'"); } return $values; }
/** * Returns board uris with this permission. * * @param \App\Contracts\PermissionUser|null $user User roles must belong to. Defaults to null. * @param bool $anonymous Determines if we should allow generic, unassigned roles. Defaults true. * @return Collection of \App\Board->board_uri strings */ public function getBoardsWithPermissions(PermissionUser $user = null, $anonymous = true) { // Identify roles which affect this user. // Sometimes we will only want direct assignments. // This includes null user_id assignments for anonymouse users. $userRoles = UserRole::select('role_id')->where(function ($query) use($user, $anonymous) { if ($anonymous) { $query->whereNull('user_id'); } if ($user instanceof PermissionUser && !$user->isAnonymous()) { $query->orWhere('user_id', $user->user_id); } else { if (!$anonymous) { $query->where(\DB::raw('0'), '1'); } } })->get()->pluck('role_id'); if (!$userRoles) { return collect(); } $inheritRoles = Role::select('role_id', 'inherit_id')->whereIn('role_id', $userRoles)->get()->pluck('inherit_id')->filter(function ($item) { return !is_null($item); }); // Identify roles which use this permission, // or which borrow inherited roles. $validRoles = RolePermission::select('role_id', 'permission_id')->where(function ($query) use($userRoles, $inheritRoles) { $query->orWhereIn('role_id', $userRoles); if ($inheritRoles) { $query->orWhereIn('role_id', $inheritRoles); } })->where('permission_id', $this->permission_id)->get()->pluck('role_id'); if (!$validRoles) { return collect(); } // Find the intersection of roles we have and roles we want. $intersectIdents = collect($userRoles)->intersect(collect($validRoles)); $inheritIdents = collect($inheritRoles)->intersect(collect($validRoles)); $intersectRoles = collect(); if ($intersectIdents) { // These are only roles which are directly assigned to us with // this permission. $intersectRoles = collect(Role::select('role_id', 'board_uri')->whereIn('role_id', $intersectIdents)->get()->pluck('board_uri')); } if ($inheritIdents) { $intersectRoles = collect(Role::select('role_id', 'board_uri')->whereIn('inherit_id', $inheritIdents)->whereIn('role_id', $userRoles)->get()->pluck('board_uri'))->merge($intersectRoles); } return $intersectRoles; }
public function scopeGetActiveQuestions($query, $wheres = array(), $sort_field = "questions.id", $sort_type = "questions.desc") { return $query->leftJoin(\DB::raw('( SELECT question_id, COUNT(*) total_votes FROM votes) votes'), function ($join) { $join->on('questions.id', '=', 'votes.question_id'); })->where(function ($query) use($wheres) { foreach ($wheres as $field => $value) { if ($value == null) { continue; } $operators = "="; $query->where($field, $operators, $value); } })->where("questions.active", 1)->select("questions.*", 'votes.total_votes')->orderBy($sort_field, $sort_type)->get(); }
public function scopeHaversine($query, $lat, $lng, $max_distance = 20, $units = 'kilometers', $fields = false) { if (empty($lat)) { $lat = 0; } if (empty($lng)) { $lng = 0; } /* * Allow for changing of units of measurement */ switch ($units) { case 'miles': //radius of the great circle in miles $gr_circle_radius = 3959; break; case 'kilometers': //radius of the great circle in kilometers $gr_circle_radius = 6371; break; } // dd($lat,$lng); /* * Support the selection of certain fields */ if (!$fields) { $fields = array('place_name', 'CONCAT(lng, ",", lat) as pos ', ' tips'); } /* * Generate the select field for disctance */ $distance_select = sprintf("ROUND(( %d * acos( cos( radians(%s) ) " . " * cos( radians( lat ) ) " . " * cos( radians( lng ) - radians(%s) ) " . " + sin( radians(%s) ) * sin( radians( lat ) ) " . " ) " . ")\n \t\t\t\t\t\t\t, 2 ) " . "AS distance\n\t\t\t\t\t ", $gr_circle_radius, $lat, $lng, $lat); $data = $query->select(\DB::raw(implode(',', $fields) . ',' . $distance_select))->having('distance', '<=', $max_distance)->orderBy('distance', 'desc'); //echo '<pre>'; //echo $query->toSQL(); //echo $distance_select; //echo '</pre>'; //die(); // //$queries = DB::getQueryLog(); //$last_query = end($queries); //var_dump($last_query); //die(); return $data; }
/** * Returns a few posts for the front page. * * @static * @param int $number How many to pull. * @param boolean $sfwOnly If we only want SFW boards. * @return Collection of static */ public static function getRecentImages($number = 16, $sfwOnly = true) { $query = static::where('is_spoiler', false)->whereHas('storage', function ($query) { $query->where('has_thumbnail', true); })->whereHas('post.board', function ($query) use($sfwOnly) { $query->where('is_indexed', true); $query->where('is_overboard', true); if ($sfwOnly) { $query->where('is_worksafe', '=', true); } })->with('storage')->with('post.board')->take($number); if ($query->getQuery()->getConnection() instanceof \Illuminate\Database\PostgresConnection) { // PostgreSQL does not support the MySQL standards non-compliant group_by syntax. // DISTINCT itself selects distinct combinations [attachment_id,file_idd, not just file_id. // We have to use raw SQL to accomplish this. $query->select(\DB::raw("DISTINCT ON (file_id) *")); $query->orderBy('file_id', 'desc'); } else { $query->orderBy('attachment_id', 'desc'); $query->groupBy('file_id'); } return $query->get(); }
public static function score($group, $date) { switch ($group) { case 'SSD-AMM': $q = ['SSD-AMM-GSL', 'SSD-CLOUD', 'SSD-AMM-SV', 'SSD-MOBILITY', 'SSD-DSC']; break; case 'SSD-BOM': $q = ['SSD-BOM', 'PSD-BOM']; break; case 'SSD-DEL': $q = ['SSD-DEL', 'PSD-DEL']; break; case 'SSD-FRA': $q = ['SSD-FRA']; break; case 'SSD-MOW': $q = ['SSD-MOW']; break; case 'SSD-SJO': $q = ['SSD-SJO', 'PSD-SJO']; break; default: ['SSD-AMM-GSL', 'SSD-CLOUD', 'SSD-AMM-SV', 'SSD-MOBILITY', 'SSD-BOM', 'PSD-BOM', 'SSD-DEL', 'PSD-DEL', 'SSD-FRA', 'SSD-MOW', 'SSD-SJO', 'PSD-SJO', 'SSD-DSC']; break; } $Ticket = Ticket::select(\DB::raw(' Format( IfNull(( ( Sum( `qops`.`tickets_items`.`weight` * `qops`.`tickets_items`.`score` ) / Sum( `qops`.`tickets_items`.`weight` ) ) * 100), 0), 2) AS `score`'))->join('tickets_items', 'ticket.ref_number', '=', 'tickets_items.ref_number')->whereIn('requester', $q)->where('ticket.status', 3)->get(); return $Ticket->count(); }
public static function getLastTen() { return self::select(\DB::raw('concat(first_name, " ", middle_name, " ", last_name ) as fullname, IF(sensortype_id = 1,timestamp, null) AS timein, IF(sensortype_id = 0,timestamp, null ) AS timeout '), 'terminal')->join('employees', 'employees.id', '=', 'employee_times.employee_id')->join('terminals', 'terminals.id', '=', 'employee_times.terminal_id')->orderBy('timestamp', 'desc')->get(); }
public function scopeNombre($query, $nombre) { $query->where(\DB::raw("CONCAT(Nombre,' ', APaterno)"), "LIKE", "%{$nombre}%"); //$query->where('id', "=", "$nombre"); }
public function scopeMonthly($query) { $dt = new \DateTime(); $month = intval($dt->format('n')); $year = intval($dt->format('Y')); $target_year_month = null; if ($month == '1') { $target_year_month = $year - 1 . '12'; } else { $target_year_month = $year . ($month - 1 < 10 ? "0" . ($month - 1) : $month - 1); } $query->Join('projects', function ($join) { $join->on('claims.project_id', '=', 'projects.id')->where('projects.reports_type', '=', \DB::raw('monthly')); })->whereRaw('EXTRACT(YEAR_MONTH FROM claims.created_at)=' . $target_year_month); return $query; }
public function fullname($id) { $doctor = Doctor::select(\DB::raw("CONCAT(doctor_fname,' ', doctor_mname,' ',doctor_lname) AS full_name"))->where('id', '=', $id)->lists('full_name')->first(); return $doctor; }
public function scopeName($query, $name) { if (trim($name) != "") { $query->where(\DB::raw("CONCAT(first_name,' ',last_name)"), "LIKE", "%{$name}%"); } }
public function ScopeId_producto($query, $id_producto) { //$query->where('id_producto',$id_producto); $query->where(\DB::raw("CONCAT(id_producto, ' ', marca, ' ',modelo)"), "LIKE", "%{$id_producto}%"); }
public function scopeFull($query) { return $query->where(\DB::raw(0), '<', function ($sql) { $sql->select(\DB::raw('COUNT(products.id)'))->from('products')->whereRaw('categories.id=products.category_id'); }); }
/** * Filter data from session_times table. * * @var array */ public function scopeSearch($query, $search) { if (trim($search) != '') { $query->where(\DB::raw("CONCAT( Name, ' ' , Address)"), 'LIKE', "%{$search}%"); } }
public function scopeDate($query, $date) { if (trim($date) != "") { $query->where(\DB::raw("CONCAT(date)"), "LIKE", "%{$date}%"); Session::flash('message', 'Fecha:' . ' ' . $date . ' ' . 'Resultado de la busqueda'); } }