PHP DB::raw方法代码示例

 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 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 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 scopeDate($query, $date)
 {
     if (trim($date) != "") {
         $query->where(\DB::raw("CONCAT(date)"), "LIKE", "%{$date}%");
         Session::flash('message', 'Fecha:' . ' ' . $date . '  ' . 'Resultado de la busqueda');
     }
 }

 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%");
     }
 }