PHP DB::select方法代码示例

 /**
  * 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;
 }

 public function getPrecioAttribute()
 {
     if (Auth::check()) {
         $lista = Auth::user()->lista;
     } else {
         $lista = config('lista_precio_publico');
     }
     $precio = \DB::select("select  format(precio,2) as precio from item_precios where lista = ? and codigo = ?", [$lista, $this->codigo]);
     //dd($lista);
     return count($precio) == 0 ? '-' : round($precio[0]->precio);
 }

 public static function UserValidate($email, $password)
 {
     $valid = false;
     $sql = 'SELECT u.* , r.role FROM users u JOIN roles r ON r.user_id = u.id WHERE u.email = ?';
     if ($user = \DB::select($sql, [$email])) {
         if (Hash::check($password, $user[0]->password)) {
             session::set('user_id', $user[0]->id);
             session::set('user_name', $user[0]->name);
             session::flash('sm', 'welcome ' . $user[0]->name . '!');
             if ($user[0]->role == 1) {
                 session::set('is_admin', true);
             }
             $valid = true;
         }
     }
     return $valid;
 }

 public function getVerbsFullyConjugated()
 {
     //this query returns a collection object containing verbs_ids for fully conjugated verbs
     $verbs_full_conj = \DB::select("select verb_id from ((SELECT verb_id,COUNT(*) as count FROM conjugations GROUP BY verb_id ORDER BY count DESC)) as ttl where count = 15");
     //this array is used to store just the verb_ids from the above query
     $vfc_array = [];
     foreach ($verbs_full_conj as $a) {
         $vfc_array[$a->verb_id] = $a->verb_id;
     }
     //$verbs = $this->orderBy('infinitive_it', 'ASC')->get();
     //this has been optimized to only retrieve the desired results instead of all verbs
     $verbs = $this->whereIn('id', $vfc_array)->orderBy('infinitive_it', 'ASC')->get();
     $verbs_for_dropdown = [];
     foreach ($verbs as $verb) {
         $verbs_for_dropdown[$verb->id] = $verb->infinitive_it;
     }
     return $verbs_for_dropdown;
 }

    /**
     * スニペットアクティビティを取得
     * @param $id
     */
    public static function getSnippetActivity($id)
    {
        $query = <<<SQL
select
    DATE_FORMAT(snippets.created_at, '%Y-%c-%e') as date,
    count(snippets.created_at) as value
from
    users
    left join
        snippets
    on  users.id = snippets.user_id
where
    snippets.user_id = {$id}
group by
    DATE_FORMAT(snippets.created_at, '%Y-%c-%e')
order by
    snippets.`created_at` DESC
SQL;
        return \DB::select(\DB::raw($query));
    }

 public function ownBookClubBook($bookClubId, $bookId)
 {
     if (count(\DB::select('select * from book_book_club where book_id = ' . $bookId . ' and book_club_id =' . $bookClubId . ' and owner_id = ' . auth()->user()->id . ';'))) {
         return true;
     }
     return false;
 }

 public function selectQuery($sql_stmt)
 {
     return DB::select($sql_stmt);
 }

 /**
  * Возвращает массив ID каталогов, для которых есть открытые заказы
  * @return array
  */
 public function getOpenOrdersCatalogsIdsArr()
 {
     $open_orders_catalogs_arr = \DB::select('select catalog_id from ' . \App\Models\OrderedProduct::TABLE . ' where `user_id` = ? and `status` = 0 and is_deferred = 0 group by `catalog_id`', [$this->id]);
     if (empty($open_orders_catalogs_arr)) {
         return [];
     }
     $open_orders_catalogs_ids_arr = [];
     foreach ($open_orders_catalogs_arr as $open_orders_catalog_mix) {
         $open_orders_catalogs_ids_arr[] = $open_orders_catalog_mix->catalog_id;
     }
     return $open_orders_catalogs_ids_arr;
 }

 public static function getVentasUsuario($id_usuario)
 {
     return \DB::select('select ventas.id, ventas.total, ventas.fecha, (select count(*) from productos_venta where productos_venta.id_venta = ventas.id) as articulos from ventas where ventas.id_usuario = :id order by id desc', ['id' => $id_usuario]);
 }

 public static function getSearches()
 {
     $sql = "SELECT\n              *,\n              searches.id AS search_id\n            FROM searches\n            JOIN status_types\n            ON status_types.id = searches.status_id";
     return \DB::select($sql);
 }

 /**
  * Возвращает доступные средства.
  * Т.е. учитываются заблокированные средства
  */
 public function getCashBalance()
 {
     $resource = \DB::select('SELECT (SUM(admission) - SUM(expense) - SUM(blocking)) AS balance FROM payments_transactions WHERE user_id = ?', [$this->id]);
     return doubleval($resource[0]->balance);
 }

 public function getTallesAttribute()
 {
     $talles = \DB::select("select distinct codtalle, codtalle as talle from mallas where coditm = ?", [$this->coditm]);
     return json_encode($talles);
 }

    public function getFoodSuggestion()
    {
        $age = Carbon::Parse($this->bdate)->diffInYears();
        $ageRange = AgeRange::where('min_age', '<=', $age)->where('max_age', '>=', $age)->first();
        $gender = $this->gender == 1 ? 'F' : 'M';
        if ($this->getFoodHistory()->first() == null) {
            $foodSuggestion = \DB::select(\DB::raw('
SELECT 
    foods.*, SUM(fn.amount_in_food / rem_nutr.remaining_val) / (2000 / foods.calories) as score
FROM
    foods
        INNER JOIN
    food_nutrient AS fn ON foods.id = fn.food_id
        INNER JOIN   
    (SELECT 
        users.id, daily_value AS remaining_val, nutrient_id
    FROM
        users
            INNER JOIN 
        recommended_values
    WHERE
        users.id = ' . $this->id . ' 
            AND 
        recommended_values.age_range = ' . $ageRange->id . ' AND recommended_values.sex = \'' . $gender . '\'
    GROUP BY 
        nutrient_id) AS rem_nutr ON rem_nutr.nutrient_id = fn.nutrient_id  
    and foods.id not in 
    (select food_id from food_restriction as fr inner join restriction_user as ru on ru.restriction_id = fr.restriction_id where user_id = ' . $this->id . ')
GROUP BY foods.id order by score DESC, foods.id, fn.nutrient_id;'));
        } else {
            $foodSuggestion = \DB::select(\DB::raw('
SELECT 
    foods.*, SUM(fn.amount_in_food / rem_nutr.remaining_val) / (2000 / foods.calories) as score
FROM
    foods
        INNER JOIN
    food_nutrient AS fn ON foods.id = fn.food_id
        INNER JOIN   
    (SELECT 
        users.id,
            fn.nutrient_id,
            nutr.daily_value - SUM((quantity * amount_in_food / 100)) AS remaining_val
    FROM
        users
            INNER JOIN 
        user_history AS uh ON users.id = uh.user_id
            INNER JOIN 
        food_nutrient AS fn ON fn.food_id = uh.food_id
            INNER JOIN 
        (SELECT nutrient_id, daily_value
         FROM recommended_values
         WHERE age_range = ' . $ageRange->id . ' AND sex = \'' . $gender . '\') AS nutr ON nutr.nutrient_id = fn.nutrient_id
    WHERE
        timestamp > DATE_SUB(NOW(), INTERVAL 24 HOUR)
            AND 
        users.id = ' . $this->id . '
    GROUP BY 
        nutrient_id) AS rem_nutr ON rem_nutr.nutrient_id = fn.nutrient_id  
    and foods.id not in 
    (select food_id from food_restriction as fr inner join restriction_user as ru on ru.restriction_id = fr.restriction_id where user_id = ' . $this->id . ')
GROUP BY foods.id order by score DESC, foods.id, fn.nutrient_id;'));
        }
        $random = rand(0, 200);
        $foodReturn = Food::where('name', $foodSuggestion[$random]->name)->first();
        return $foodReturn;
    }

 public static function dbo()
 {
     $select = \DB::select(self::table);
     return $select;
 }

 public static function allIndicatorsOfUser()
 {
     $idUser = '205980050';
     // OBTIENE TODOS LOS INDICADORES DEL USUARIO.
     $userIndicators = \DB::select('select PKG_CMI_CONTROLVIEW.FUNC_GET_USERINDICATOR(?) from dual', [$idUser]);
     // OBTIENE LOS DATOS PERSONALES DEL USUARIO Y FACULTAD.
     $employeeNoRelated = \DB::select('select PKG_CMI_EMPLOYEES.FUNC_GET_EMPLOYEENOTRELATED(?) from dual', [$idUser]);
     $tablero;
     $i = 0;
     // BUCLE QUE OBTIENE CADA INDICADOR DEL USUARIOS REGISTRADO.
     foreach ($userIndicators as $userIndicator) {
         // CONSULTAS QUE OBTIENEN LOS DATOS DE UN INDICADOR ESPECIFICO.
         $faculty = \DB::select('select PKG_CMI_FACULTIES.FUNC_GET_FACULTY(?) from dual', [$employeeNoRelated[0]->facultyid]);
         $lastsState = \DB::select('select PKG_CMI_INDICATOR_STATES.FUNC_GET_LASTSSTATE(?) from dual', [$userIndicator->indicatorid]);
         $colorValues = \DB::select('select PKG_CMI_COLORMETRIC.FUNC_GET_COLORVALUES(?) from dual', [$userIndicator->colormetricid]);
         $colometric = \DB::select('select PKG_CMI_COLORMETRIC.FUNC_GET_COLORMETRIC(?) from dual', [$userIndicator->colormetricid]);
         $averageColor = \DB::select('select PKG_CMI_COLORVALUE.FUNC_GET_COLORVALUE(?) from dual', [$userIndicator->actuallevel]);
         //Define la colometría para el campo colorimetría de la tabla.
         /* 
          $averageColor1 = '';
         
         if ( ($userIndicator->actualvalue >= $colorValues[0]->initialvalue) && ($userIndicator->actualvalue <= $colorValues[0]->lastvalue) ) {
           $averageColor1 = $colorValues[0]->color;
         }elseif ( ($userIndicator->actualvalue >= $colorValues[1]->initialvalue) && ($userIndicator->actualvalue <= $colorValues[1]->lastvalue) ) {
           $averageColor1 = $colorValues[1]->color;
         }elseif ( ($userIndicator->actualvalue >= $colorValues[2]->initialvalue) && ($userIndicator->actualvalue <= $colorValues[2]->lastvalue) ) {
           $averageColor1 = $colorValues[2]->color;
         }elseif ( ($userIndicator->actualvalue >= $colorValues[3]->initialvalue) && ($userIndicator->actualvalue <= $colorValues[3]->lastvalue) ) {
           $averageColor1 = $colorValues[3]->color;
         }else{
           $averageColor1 = 'N/A';
         }
         */
         /*--------------------------------------------------
             //PRUEBA PARA CADA INDICADOR
                $tamano=0;
              if ($userIndicator->indicatorid == 'ind-nueve') {
                $tamano = count($lastsState);
                break;
              }
           */
         //Count consulta el tamaño de lasstate para poder sacarle el promedio y le asigna el valor del score,
         //sino le asigna N/A para mostrarlo en Ultimas Alimentaciones.
         $lastsState_0_score = '';
         $lastsState_1_score = '';
         $lastsState_2_score = '';
         $lastsState_3_score = '';
         $colometricAverage = 0.0;
         if (count($lastsState) == 5) {
             if (!empty($lastsState[0]->score)) {
                 $colometricAverage += $lastsState[0]->score / 4;
                 $lastsState_0_score = $lastsState[0]->score;
             } else {
                 $colometricAverage += 0;
                 $lastsState_0_score = 'N/A';
             }
             if (!empty($lastsState[1]->score)) {
                 $colometricAverage += $lastsState[1]->score / 4;
                 $lastsState_1_score = $lastsState[1]->score;
             } else {
                 $colometricAverage += 0;
                 $lastsState_1_score = 'N/A';
             }
             if (!empty($lastsState[2]->score)) {
                 $colometricAverage += $lastsState[2]->score / 4;
                 $lastsState_2_score = $lastsState[2]->score;
             } else {
                 $colometricAverage += 0;
                 $lastsState_2_score = 'N/A';
             }
             if (!empty($lastsState[3]->score)) {
                 $colometricAverage += $lastsState[3]->score / 4;
                 $lastsState_3_score = $lastsState[3]->score;
             } else {
                 $colometricAverage += 0;
                 $lastsState_3_score = 'N/A';
             }
         } elseif (count($lastsState) == 4) {
             if (!empty($lastsState[0]->score)) {
                 $colometricAverage += $lastsState[0]->score / 3;
                 $lastsState_0_score = $lastsState[0]->score;
             } else {
                 $colometricAverage += 0;
                 $lastsState_0_score = 'N/A';
             }
             if (!empty($lastsState[1]->score)) {
                 $colometricAverage += $lastsState[1]->score / 3;
                 $lastsState_1_score = $lastsState[1]->score;
             } else {
                 $colometricAverage += 0;
                 $lastsState_1_score = 'N/A';
             }
             if (!empty($lastsState[2]->score)) {
                 $colometricAverage += $lastsState[2]->score / 3;
                 $lastsState_2_score = $lastsState[2]->score;
             } else {
                 $colometricAverage += 0;
                 $lastsState_2_score = 'N/A';
             }
             $lastsState_3_score = 'N/A';
//.........这里部分代码省略.........