PHP Manager::raw方法代码示例

 public function defaultAction()
 {
     // Line chart
     $memcache = new \Memcached();
     $stats1 = $memcache->get('stats1');
     if ($stats1 === false) {
         $stats = Stat::select(DB::raw('max(`packages`) as packages'), DB::raw('sum(`added`) as added'), DB::raw('date(`created_at`) as date'))->orderBy('date', 'asc')->groupBy('date')->get();
         $stats1 = [['Date', 'Total', 'New']];
         foreach ($stats as $stat) {
             $stats1[] = [$stat->date, (int) $stat->packages, (int) $stat->added];
         }
         $memcache->set('stats1', $stats1, self::DAY);
     }
     // Domain table
     $memcache = new \Memcached();
     $stats2 = $memcache->get('stats2');
     if ($stats2 === false) {
         $stats2 = Package::select(['domain', DB::raw('count(domain) as count')])->groupBy('domain')->where('domain', '<>', '')->orderBy('count', 'desc')->get()->toArray();
         $memcache->set('stats2', $stats2, self::DAY);
     }
     // Types table
     $memcache = new \Memcached();
     $stats3 = $memcache->get('stats3');
     if ($stats3 === false || true) {
         $stats3 = Package::select(['type', DB::raw('count(type) as count')])->groupBy('type')->where('type', '<>', '')->having('count', '>', 9)->orderBy('count', 'desc')->get()->toArray();
         $memcache->set('stats3', $stats3, self::DAY);
     }
     $this->_setTitle('Stats');
     return new StatsView($stats1, $stats2, $stats3);
 }

 function getSectorEmpresas(Request $request, Response $response)
 {
     $response = $response->withHeader('Content-type', 'application/json');
     $data = Parametros::select("*")->first();
     $km = $data["diametro_busqueda"];
     $idSector = $request->getAttribute("id");
     $lat = $request->getAttribute("latitud");
     $lng = $request->getAttribute("longitud");
     $query = "SELECT " . "(6371 * ACOS( SIN(RADIANS(su.latitud)) * SIN(RADIANS({$lat})) + COS(RADIANS(su.longitud - {$lng})) * " . "COS(RADIANS(su.latitud)) * COS(RADIANS({$lat})))) AS distancia, " . "em.id, " . "em.nit, " . "em.razonSocial, " . "em.logo, " . "'' as servicios " . "FROM sucursal su " . "INNER JOIN " . "empresa em ON (em.id = su.idEmpresa) " . "INNER JOIN " . "sectorempresa secemp ON (secemp.idEmpresa = em.id && secemp.idSector =  {$idSector}) " . "WHERE su.Estado = 'ACTIVO' AND em.estado = 'ACTIVO' " . "HAVING distancia < {$km} ORDER BY distancia ASC";
     $data = DB::select(DB::raw($query));
     for ($i = 0; $i < count($data); $i++) {
         $val = "";
         $ser = Servicio::select("nombre")->where("idEmpresa", "=", $data[$i]->id)->get();
         $tam = count($ser);
         for ($j = 0; $j < $tam; $j++) {
             $val .= $ser[$j]->nombre;
             if ($j + 1 < $tam) {
                 $val .= ",";
             }
         }
         $data[$i]->servicios = $val;
     }
     $response->getBody()->write(json_encode($data));
     return $response;
 }

 public function scopeMostActive($query, $limit = false)
 {
     $query->select(['author.id', 'author.name', DB::raw('COUNT(plugin_author.plugin_id) as plugin_count')])->leftJoin('plugin_author', 'author.id', '=', 'plugin_author.author_id')->groupBy('author.name')->orderBy('plugin_count', 'DESC');
     if ($limit != false) {
         $query->take($limit);
     }
     return $query;
 }

 public function create($token, $expireTime, $sessionId)
 {
     $accessToken = new AccessToken();
     $accessToken->token = $token;
     $accessToken->session_id = $sessionId;
     $accessToken->expire_time = Capsule::raw('FROM_UNIXTIME(' . $expireTime . ')');
     $accessToken->save();
 }

 public static function insertSkipDupes($domains)
 {
     $query = sprintf('INSERT INTO domains (name) VALUES ("%s") ON DUPLICATE KEY UPDATE name=name', implode('"),("', $domains));
     $status = DB::statement($query);
     Domain::where('created_at', '0000-00-00 00:00:00')->update(['created_at' => DB::raw('NOW()')]);
     Domain::where('created_at', '0000-00-00 00:00:00')->update(['updated_at' => DB::raw('NOW()')]);
     return $status;
 }

 public function create($token, $expireTime, $accessToken)
 {
     $accessToken = AccessToken::where('token', '=', $accessToken)->first();
     $refreshToken = new RefreshToken();
     $refreshToken->token = $token;
     $refreshToken->access_token_id = $accessToken->id;
     $refreshToken->expire_time = DB::raw('FROM_UNIXTIME(' . $expireTime . ')');
     $refreshToken->save();
 }

 /**
  * Do the migration
  */
 public function up()
 {
     //Create
     $this->schema->create('account_status', function ($table) {
         $table->increments('id');
         $table->string('value', 200);
         $table->dateTime('created_at')->default(DB::raw('CURRENT_TIMESTAMP'));
     });
 }

 public function promedio(Request $request, Response $response)
 {
     $response = $response->withHeader('Content-type', 'application/json');
     $idCliente = $request->getAttribute("idCliente");
     $query = "SELECT COALESCE(AVG(calificacion),0) as promedio FROM calificacioncliente WHERE idCliente = " . $idCliente;
     $data = DB::select(DB::raw($query));
     $response->getBody()->write(json_encode($data));
     return $response;
 }

 function contasector(Request $request, Response $response)
 {
     $response = $response->withHeader('Content-type', 'application/json');
     //sum(ingresos.valor) as total,
     $id = $request->getAttribute("idSector");
     $fechainicial = $request->getAttribute("fechainicial");
     $fechafinal = $request->getAttribute("fechafinal");
     $data = DB::select(DB::raw("select sum(ingresos.valor) as total,ingresos.fecha,sectorempresa.id," . "empresa.razonSocial,sucursal.nombre" . " from sectorempresa " . "inner join empresa on empresa.id = sectorempresa.idEmpresa " . "inner join sucursal on sucursal.idEmpresa = empresa.id " . "inner join empleado on empleado.idSucursal = sucursal.id " . "inner join ingresos on ingresos.idEmpleado = empleado.id " . "where sectorempresa.idSector = " . $id . " and ingresos.fecha BETWEEN '" . $fechainicial . "' and " . "'" . $fechafinal . "' " . "GROUP BY empresa.razonSocial"));
     $response->getBody()->write(json_encode($data));
     return $response;
 }

 /**
  * Do the migration
  */
 public function up()
 {
     //Create the table
     $this->schema->create('account_verification_tokens', function ($table) {
         $table->increments('id');
         $table->integer('user_id')->unsigned();
         $table->string('token', 100);
         $table->enum('status', ['active', 'expired']);
         $table->dateTime('created_at')->default(DB::raw('CURRENT_TIMESTAMP'));
         $table->foreign('user_id')->references('id')->on('users');
     });
 }

 /**
  * Do the migration
  */
 public function up()
 {
     //Create the table
     $this->schema->create('uom_valid_ids', function ($table) {
         $table->string('id', 50);
         $table->string('first_name', 100);
         $table->string('last_name', 100);
         $table->enum('type', ['student', 'lecturer']);
         $table->timestamp('datetime')->default(DB::raw('CURRENT_TIMESTAMP'));
         $table->boolean('valide');
         $table->boolean('has_account');
         $table->primary('id');
     });
 }

 public function getBaseQuery($limit = false, $offset = 0, $orderField = 'fRating', $desc = true, $starred = false)
 {
     $query = DB::table('deals')->select(DB::raw('fmtc_merchants.*, fmtc_deals.*'))->join('merchants', 'deals.nMerchantID', '=', 'merchants.nMerchantID')->where('dtStartDate', '<=', date('Y-m-d H:i:s'))->where('dtEndDate', '>=', date('Y-m-d H:i:s'));
     if ($starred) {
         $query->where('bStarred', 1);
     }
     $order = $desc ? 'desc' : 'asc';
     $query->orderBy($orderField, $order);
     if ($orderField !== 'fRating') {
         $query->orderBy('fRating', 'desc');
     }
     if ($limit !== false) {
         $query->skip($offset);
         $query->take($limit);
     }
     return $query;
 }

 public function __construct($queryBuilder)
 {
     $this->queryBuilder = $queryBuilder;
     // Clone the query builder to compute
     // Collection length with a subquery
     $clone = clone $this->queryBuilder;
     $query = $clone instanceof \Illuminate\Database\Eloquent\Builder ? $clone->getQuery() : $clone;
     $this->length = \Illuminate\Database\Capsule\Manager::table(\Illuminate\Database\Capsule\Manager::raw("({$clone->toSql()}) as sub"))->mergeBindings($query)->count();
     // Parse range headers and compute
     // available range that is going
     // to be returned
     $this->parseRangeHeader();
     if (!$this->currentRange) {
         $this->page = [];
     } else {
         // If parseRangeHeader() worked,
         // we can fetch the page
         $this->page = $this->getPage();
     }
 }

 /**
  * Do the migration
  */
 public function up()
 {
     //Create the table
     $this->schema->create('users', function ($table) {
         $table->increments('id');
         $table->string('first_name', 100);
         $table->string('last_name', 100);
         $table->string('email', 200)->unique();
         $table->string('password', 100);
         $table->enum('user_type', ['student', 'lecturer']);
         $table->date('date_of_birth');
         $table->enum('gender', ['male', 'female']);
         $table->string('uom_id', 50)->unique();
         $table->integer('account_status')->unsigned();
         $table->string('remember_me', 100);
         $table->timestamp('datetime_joined')->default(DB::raw('CURRENT_TIMESTAMP'));
         //Add foreign key constrains
         $table->foreign('uom_id')->references('id')->on('uom_valid_ids');
         $table->foreign('account_status')->references('id')->on('account_status');
     });
 }

 /**
  * This count the items returned by a SQL query
  */
 public static function preCountQuery($queryBuilder)
 {
     $qb = clone $queryBuilder;
     return \Illuminate\Database\Capsule\Manager::table(\Illuminate\Database\Capsule\Manager::raw("({$qb->toSql()}) as sub"))->mergeBindings($qb->getQuery())->count();
 }