PHP Select::getSqlString方法代码示例

 public function query(\Zend\Db\Sql\Select $select)
 {
     $dbAdapter = $this->getDbAdapter();
     $platform = $dbAdapter->getPlatform();
     $sql = $select->getSqlString($platform);
     $result = $dbAdapter->query($sql)->execute()->current();
     return $result;
 }

 /**
  * @param PlatformInterface $platform
  * @return string
  */
 public function getSqlString(PlatformInterface $platform = null)
 {
     // localize variables
     foreach (get_object_vars($this->select) as $name => $value) {
         $this->{$name} = $value;
     }
     return parent::getSqlString($platform);
 }

 /**
  * @param  PlatformInterface $platform
  * @return string
  */
 public function getSqlString(PlatformInterface $platform = null)
 {
     // localize variables
     foreach (get_object_vars($this->select) as $name => $value) {
         $this->{$name} = $value;
     }
     unset($this->specifications[self::LIMIT]);
     unset($this->specifications[self::OFFSET]);
     $this->specifications['LIMITOFFSET'] = null;
     return parent::getSqlString($platform);
 }

 /**
  * @param PlatformInterface $platform
  * @return string
  */
 public function getSqlString(PlatformInterface $platform = null)
 {
     // localize variables
     foreach (get_object_vars($this->select) as $name => $value) {
         $this->{$name} = $value;
     }
     if ($this->limit === null && $this->offset !== null) {
         $this->specifications[self::LIMIT] = 'LIMIT 18446744073709551615';
     }
     return parent::getSqlString($platform);
 }

 public function searchSerial($serial)
 {
     $select = new Select($this->tableGateway->getTable());
     $select->columns(array());
     $select->join("products", "products.id = " . $this->tableGateway->getTable() . ".product", array('id' => 'id'));
     $select->join("products_receive_inventory", "products_receive_inventory.details_receive_inventory = " . $this->tableGateway->getTable() . ".id", array('serial' => 'serial'));
     $select->where(array("products_receive_inventory.status" => 0));
     $select->where->like("products_receive_inventory.serial", "%" . $serial . "%");
     error_log($select->getSqlString());
     $result = $this->tableGateway->selectWith($select);
     return $result;
 }

 /**
  * Handles job get report event by fetching messages from the log table
  * 
  * @param Event $event
  * @return \SporkTools\Core\Job\Report
  */
 public function report(Event $event)
 {
     $job = $event->getTarget();
     $serviceManager = $job->getServiceManager();
     $this->initialize($serviceManager);
     $report = new Report();
     $runId = $this->fetchLastBatchId($job);
     $select = new Select($this->table);
     $select->columns(array('message' => $this->columnMap['message'], 'type' => $this->columnMap['priority'], 'datetime' => $this->columnMap['timestamp']))->where->like($this->columnMap['message'], "% [{$runId}]");
     $results = $this->db->query($select->getSqlString($this->db->getPlatform()), Adapter::QUERY_MODE_EXECUTE);
     foreach ($results as $row) {
         $type = isset($this->typeMap[$row->type]) ? $this->typeMap[$row->type] : $row->type;
         $report->addMessage(new Message($row->message, $type, $row->datetime));
     }
     return $report;
 }

 /**
  * @param array $accList
  * @param string $date
  *
  * @return \DDD\Domain\ApartmentGroup\ConciergeView[]|\ArrayObject
  */
 public function getArrivalsByDay($accList, $date)
 {
     $columns = array('id', 'ki_page_hash', 'guest_first_name', 'guest_last_name', 'res_number', 'pax' => 'man_count', 'occupancy', 'date_to', 'arrival_status', 'overbooking_status', 'model', 'check_charged', 'guest_email', 'guest_balance', 'housekeeping_comment' => new Expression("(\n                SELECT\n                GROUP_CONCAT(\n                    CONCAT('<blockquote class=\"comment-blockquote\">', '<p>', value, '</p><footer>', users.firstname, ' ', users.lastname, ', ',  `timestamp`, ' (Amsterdam Time)', '</footer></blockquote>') SEPARATOR ''\n                )\n                FROM " . DbTables::TBL_ACTION_LOGS . "\n                LEFT JOIN " . DbTables::TBL_BACKOFFICE_USERS . " AS users ON users.id = " . DbTables::TBL_ACTION_LOGS . ".user_id\n                WHERE module_id = " . Logger::MODULE_BOOKING . " AND identity_id = " . $this->getTable() . ".`id` AND action_id = " . Logger::ACTION_HOUSEKEEPING_COMMENT . "\n            )"), 'ki_page_status' => 'ki_page_status', 'provide_cc_page_status', 'provide_cc_page_hash');
     $select = new Select($this->getTable());
     $select->join(['charge' => DbTables::TBL_CHARGE], new Expression($this->getTable() . '.id = charge.reservation_id AND charge.addons_type = 6 AND charge.status = 0'), ['parking' => 'id'], Select::JOIN_LEFT);
     $select->join(['apartment' => DbTables::TBL_APARTMENTS], $this->getTable() . '.apartment_id_assigned = apartment.id', ['unitNumber' => 'unit_number', 'acc_name' => 'name'], Select::JOIN_LEFT);
     $select->join(['cc_token' => DbTables::TBL_TOKEN], new Expression($this->getTable() . '.customer_id = cc_token.customer_id AND cc_token.status in (' . CardServiceUse::CC_STATUS_VALID . ',' . CardServiceUse::CC_STATUS_UNKNOWN . ')'), ['first_digits', 'cc_type' => 'brand', 'salt'], Select::JOIN_LEFT);
     $select->where($this->getTable() . ".date_from = '" . $date . "'")->where($this->getTable() . '.status = 1')->where->notEqualTo($this->getTable() . '.overbooking_status', BookingTicket::OVERBOOKING_STATUS_OVERBOOKED);
     $select->where->in($this->getTable() . '.apartment_id_assigned', $accList);
     $select->columns($columns)->order(['cc_token.is_default DESC', 'cc_token.status DESC', 'arrival_status ASC', 'guest_last_name ASC']);
     $sql = $select->getSqlString($this->getAdapter()->getPlatform());
     $sql = "SELECT * FROM ({$sql}) AS main GROUP BY res_number ORDER BY arrival_status ASC, guest_last_name ASC";
     $statement = $this->adapter->createStatement($sql);
     $result = $statement->execute();
     $resultSet = clone $this->resultSetPrototype;
     $resultSet->initialize($result);
     return $resultSet;
 }

 /**
  * Get SQL string for this statement
  *
  * @param  null|PlatformInterface $adapterPlatform Defaults to Sql92 if none provided
  * @return string
  */
 public function getSqlString(PlatformInterface $adapterPlatform = null)
 {
     $adapterPlatform = $adapterPlatform ?: new Sql92();
     $table = $this->table;
     $schema = null;
     // create quoted table name to use in insert processing
     if ($table instanceof TableIdentifier) {
         list($table, $schema) = $table->getTableAndSchema();
     }
     $table = $adapterPlatform->quoteIdentifier($table);
     if ($schema) {
         $table = $adapterPlatform->quoteIdentifier($schema) . $adapterPlatform->getIdentifierSeparator() . $table;
     }
     $columns = array_map(array($adapterPlatform, 'quoteIdentifier'), $this->columns);
     $columns = implode(', ', $columns);
     if (is_array($this->values)) {
         $values = array();
         foreach ($this->values as $value) {
             if ($value instanceof Expression) {
                 $exprData = $this->processExpression($value, $adapterPlatform);
                 $values[] = $exprData->getSql();
             } elseif ($value === null) {
                 $values[] = 'NULL';
             } else {
                 $values[] = $adapterPlatform->quoteValue($value);
             }
         }
         return sprintf($this->specifications[static::SPECIFICATION_INSERT], $table, $columns, implode(', ', $values));
     } elseif ($this->values instanceof Select) {
         $selectString = $this->values->getSqlString($adapterPlatform);
         if ($columns) {
             $columns = "({$columns})";
         }
         return sprintf($this->specifications[static::SPECIFICATION_SELECT], $table, $columns, $selectString);
     } else {
         throw new Exception\InvalidArgumentException('values or select should be present');
     }
 }

 /**
  * @param Select $select
  * @param PlatformInterface $platform
  */
 public static function dumpSQL($select, $platform = null)
 {
     die(str_replace('"', '', $select->getSqlString($platform)));
 }

 /**
  * @param \Zend\Db\Sql\Select $select
  * @return mixed
  */
 protected function getSql($select)
 {
     return $select->getSqlString($this->tableGateway->getAdapter()->getPlatform());
 }

 /**
  * @param string $expectedSql
  */
 protected function assertTableGatewayLastSqlSelect($expectedSql)
 {
     $actualSql = $this->select->getSqlString($this->mysqlPlatform);
     $this->assertSqlEquals($expectedSql, $actualSql);
 }

 /**
  * @testdox unit test: Test getSqlString() will produce expected sql and parameters based on a variety of provided arguments [uses data provider]
  * @covers Zend\Db\Sql\Select::getSqlString
  * @dataProvider providerData
  */
 public function testGetSqlString(Select $select, $unused, $unused2, $expectedSqlString)
 {
     $this->assertEquals($expectedSqlString, $select->getSqlString(new TrustingSql92Platform()));
 }

 /**
  * @param Select $select
  * @return string $sqlString
  */
 public function getSqlString($select)
 {
     $adapterPlatform = $this->getAdapter()->getPlatform();
     $sqlString = $select->getSqlString($adapterPlatform);
     return $sqlString;
 }

 /**
  * @covers Zend\Db\Sql\Select::getSqlString
  */
 public function testGetSqlString()
 {
     $this->select->from('foo')->columns(array('bee', 'baz'))->join('zac', 'm = n')->where('x = y');
     $this->assertEquals('SELECT "bee", "baz", "zac".* FROM "foo" INNER JOIN "zac" ON "m" = "n" WHERE x = y', $this->select->getSqlString());
 }

 /**
  * @inheritdoc
  */
 public function findAll()
 {
     $collection = new TrackingUnitCollection();
     $dbAdapter = $this->getDbAdapter();
     $select = new Select(Utils::getTrackingTableName());
     $select->order(array(Utils::getTrackingTableName(), Select::ORDER_ASCENDING));
     $sql = $select->getSqlString($dbAdapter->getPlatform());
     $results = $dbAdapter->query($sql, DbAdapter::QUERY_MODE_EXECUTE);
     foreach ($results as $rowData) {
         $trackingUnit = $this->loadTrackingUnit($rowData);
         $collection->attach($trackingUnit);
     }
     //end loop
     return $collection;
 }