PHP DB::Raw方法代码示例

 public function cleanup()
 {
     $prefix = config('larablog.table.prefix');
     // Clean out any old pivot data.
     DB::statement("DELETE {$prefix}_post_tag FROM {$prefix}_post_tag LEFT JOIN {$prefix}_posts ON {$prefix}_post_tag.post_id = {$prefix}_posts.id WHERE NOT({$prefix}_post_tag.post_id = {$prefix}_posts.id AND {$prefix}_posts.status = 'active' AND {$prefix}_posts.type='post')");
     // TODO: convert to eloquent?
     DB::table($prefix . '_tags')->update(['posts_count' => DB::Raw("(SELECT COUNT(*) FROM {$prefix}_post_tag WHERE {$prefix}_post_tag.tag_id = {$prefix}_tags.id)")]);
     Tag::where('posts_count', 0)->delete();
 }

 /**
  * Counts current query
  * @param string $count variable to store to 'count_all' for iTotalRecords, 'display_all' for iTotalDisplayRecords
  * @return null
  */
 private function count($count = 'count_all')
 {
     //Get columns to temp var.
     if ($this->query_type == 'eloquent') {
         $query = $this->query->getQuery();
         $connection = $this->query->getModel()->getConnection()->getName();
     } else {
         $query = $this->query;
         $connection = $query->getConnection()->getName();
     }
     // if its a normal query ( no union ) replace the slect with static text to improve performance
     $myQuery = clone $query;
     if (!preg_match('/UNION/i', $myQuery->toSql())) {
         $myQuery->select(DB::Raw("'1' as row"));
     }
     $this->{$count} = DB::connection($connection)->table(DB::raw('(' . $myQuery->toSql() . ') AS count_row_table'))->setBindings($myQuery->getBindings())->remember(1)->count();
 }

 public function cleanup()
 {
     $prefix = config('larablog.table.prefix');
     DB::table($prefix . '_series')->update(['posts_count' => DB::Raw("(SELECT COUNT(*) FROM {$prefix}_posts WHERE {$prefix}_posts.serie_id = {$prefix}_series.id)")]);
     Serie::where('posts_count', 0)->delete();
 }

 private function productQuery($language_id)
 {
     /*
     $sql="SELECT DISTINCT *,
            pd.name AS name,
            p.image,
            m.name AS manufacturer,
         (SELECT price
          FROM   products_discount pd2
          WHERE  pd2.product_id = p.id
                 AND pd2.customer_group_id = '1'
                 AND pd2.quantity = '1'
                 AND ( ( pd2.date_start = '0000-00-00'
                          OR pd2.date_start < Now() )
                       AND ( pd2.date_end = '0000-00-00'
                              OR pd2.date_end > Now() ) )
          ORDER  BY pd2.priority ASC,
                    pd2.price ASC
          LIMIT  1) AS discount,
     
         (SELECT price
          FROM   products_special ps
          WHERE  ps.product_id = p.id
                 AND ps.customer_group_id = '1'
                 AND ( ( ps.date_start = '0000-00-00'
                          OR ps.date_start < Now() )
                       AND ( ps.date_end = '0000-00-00'
                              OR ps.date_end > Now() ) )
          ORDER  BY ps.priority ASC,
                    ps.price ASC
          LIMIT  1) AS special,
     
         (SELECT points
          FROM   products_reward pr
          WHERE  pr.product_id = p.id
                 AND customer_group_id = '1') AS reward,
     
         (SELECT ss.name
          FROM   oc_stock_status ss
          WHERE  ss.stock_status_id = p.stock_status_id
                 AND ss.language_id = '1')    AS stock_status,
     
         (SELECT wcd.unit
          FROM   oc_weight_class_description wcd
          WHERE  p.weight_class_id = wcd.weight_class_id
                 AND wcd.language_id = '1')   AS weight_class,
     
         (SELECT lcd.unit
          FROM   oc_length_class_description lcd
          WHERE  p.length_class_id = lcd.length_class_id
                 AND lcd.language_id = '1')   AS length_class,
     
         (SELECT Avg(rating) AS total
          FROM   oc_review r1
          WHERE  r1.product_id = p.id
                 AND r1.status = '1'
          GROUP  BY r1.product_id) AS rating,
     
         (SELECT Count(*) AS total
          FROM   oc_review r2
          WHERE  r2.product_id = p.id
                 AND r2.status = '1'
          GROUP  BY r2.product_id) AS reviews
     
         FROM   products p
                LEFT JOIN products_description pd
                       ON ( p.id = pd.product_id )
                LEFT JOIN products_to_store p2s
                       ON ( p.id = p2s.product_id )
                LEFT JOIN oc_manufacturer m
                       ON ( p.manufacturer_id = m.manufacturer_id)
     
         WHERE  p.id = ?
                AND pd.language_id = ?
                AND p.status = '1'
                AND p.date_available <= Now()
                AND p2s.store_id = '0'";
     
     $items=DB::select($sql,[$product_id,$this->language_id]);
     
     return $item;
     */
     $query = DB::table('products as p')->select('p.id', 'p.model', 'p.price', 'p.tax_class_id', 'tc.name AS tax_class_name', 'p.weight_class_id', 'wcd.name AS weight_class_name', 'wcd.unit AS weight_class_unit', 'p.length_class_id', 'lcd.name AS length_class_name', 'lcd.unit AS length_class_unit', 'p.price', 'p.image', 'pd.name AS name', 'pd.description AS description', 'm.id AS manufacturer_id', 'm.name AS manufacturer_name', 'ss.name AS stock_status', DB::Raw('(SELECT points FROM products_rewards pr WHERE  pr.product_id = p.id AND customer_group_id = 1) AS reward_points'), DB::Raw('(SELECT Avg(rating) FROM products_reviews pr WHERE pr.product_id = p.id AND pr.approved = 1 GROUP  BY pr.product_id) AS rating'), DB::Raw('(SELECT Count(*) FROM products_reviews pr WHERE  pr.product_id = p.id AND pr.approved = 1) AS reviews_count'), DB::Raw('(SELECT Count(*) FROM products_to_attributes pa WHERE  pa.product_id = p.id) AS attributes_count'))->leftJoin('products_descriptions as pd', 'p.id', '=', 'pd.product_id')->leftJoin('manufacturers AS m', 'm.id', '=', 'p.manufacturer_id')->leftJoin('tax_classes AS tc', 'tc.id', '=', 'p.tax_class_id')->leftJoin('weight_classes_descriptions AS wcd', function ($join) use($language_id) {
         $join->on('wcd.weight_class_id', '=', 'p.weight_class_id');
         $join->where('wcd.language_id', '=', Context::getLanguageId());
     })->leftJoin('length_classes_descriptions AS lcd', function ($join) use($language_id) {
         $join->on('lcd.length_class_id', '=', 'p.length_class_id');
         $join->where('lcd.language_id', '=', Context::getLanguageId());
     })->leftJoin('stock_statuses AS ss', function ($join) use($language_id) {
         $join->on('ss.stock_status_id', '=', 'p.stock_status_id');
         $join->where('ss.language_id', '=', Context::getLanguageId());
     })->where('pd.language_id', Context::getLanguageId());
     return $query;
 }