PHP SQLQuery::sql方法代码示例

 public function importPass()
 {
     $query = $this->getRemoteObjectsQuery();
     $items = $this->task->query($query);
     $itemsCount = $items->numRecords();
     $this->task->message(" * Found {$itemsCount} items to import");
     $total = 0;
     $updated = 0;
     $inserted = 0;
     foreach ($items as $item) {
         $this->task->progress(++$total, $itemsCount);
         // Build select query for existing object
         $values = array();
         $select = new SQLQuery("ID", "\"{$this->tableName}\"");
         foreach ($this->fields as $field => $class) {
             $value = intval($item[$field]);
             $values[] = $value;
             $select->addWhere(sprintf("\"{$field}\" = %d", $value));
         }
         $values[] = $item['ID'];
         // Check for existing record
         if ($localID = DB::query($select->sql())->value()) {
             // Update local many_many record instead of making new row
             DB::query(sprintf('UPDATE "%s" SET "LegacyID" = %d WHERE "ID" = %d', $this->tableName, $item['ID'], $localID));
             $updated++;
         } else {
             // Insert mapping into the many_many table
             $insert = "INSERT INTO \"{$this->tableName}\" (";
             $insert .= '"' . implode('", "', array_keys($this->fields)) . '"';
             $insert .= ', LegacyID';
             $insert .= ') VALUES (';
             $insert .= implode(', ', $values);
             $insert .= ')';
             DB::query($insert);
             $localID = DB::getGeneratedID($this->tableName);
             $inserted++;
         }
         // Mark this relation as imported in the remote table
         $conn = $this->task->getRemoteConnection();
         $conn->query(sprintf('UPDATE "%s" SET "_ImportedID" = %d, "_ImportedDate" = NOW() WHERE "ID" = %d', $this->tableName, $localID, $item['ID']));
     }
     // Done!
     $this->task->message(" * Result: {$inserted} added, {$updated} updated");
 }

 private function checkBlogEntryPermissions()
 {
     $authorsId = array();
     $sqlQuery = new SQLQuery();
     $sqlQuery->setFrom('SiteTree_versions');
     $sqlQuery->selectField('AuthorID');
     $sqlQuery->addWhere('RecordID = ' . $this->ID);
     $sqlQuery->setOrderBy('ID DESC');
     $rawSQL = $sqlQuery->sql();
     $result = $sqlQuery->execute();
     foreach ($result as $row) {
         $authorsId[] = $row['AuthorID'];
     }
     $sqlQuery->setDelete(true);
     if (in_array(Member::currentUser()->ID, $authorsId) || $this->parent->OwnerID == Member::currentUser()->ID || Permission::check('ADMIN')) {
         return true;
     } else {
         return false;
     }
 }

 public function testSelect()
 {
     $query = new SQLQuery('"Title"', '"MyTable"');
     $query->addSelect('"TestField"');
     $this->assertEquals('SELECT "Title", "TestField" FROM "MyTable"', $query->sql());
     // Test replacement of select
     $query->setSelect(array('Field' => '"Field"', 'AnotherAlias' => '"AnotherField"'));
     $this->assertEquals('SELECT "Field", "AnotherField" AS "AnotherAlias" FROM "MyTable"', $query->sql());
     // Check that ' as ' selects don't get mistaken as aliases
     $query->addSelect(array('Relevance' => "MATCH (Title, MenuTitle) AGAINST ('Two as One')"));
     $this->assertEquals('SELECT "Field", "AnotherField" AS "AnotherAlias", MATCH (Title, MenuTitle) AGAINST (' . '\'Two as One\') AS "Relevance" FROM "MyTable"', $query->sql());
 }

 public function testJoinSubSelect()
 {
     $query = new SQLQuery();
     $query->setFrom('MyTable');
     $query->addInnerJoin('(SELECT * FROM MyOtherTable)', 'Mot.MyTableID = MyTable.ID', 'Mot');
     $query->addLeftJoin('(SELECT MyLastTable.MyOtherTableID, COUNT(1) as MyLastTableCount FROM MyLastTable ' . 'GROUP BY MyOtherTableID)', 'Mlt.MyOtherTableID = Mot.ID', 'Mlt');
     $query->setOrderBy('COALESCE(Mlt.MyLastTableCount, 0) DESC');
     $this->assertSQLEquals('SELECT *, COALESCE(Mlt.MyLastTableCount, 0) AS "_SortColumn0" FROM MyTable ' . 'INNER JOIN (SELECT * FROM MyOtherTable) AS "Mot" ON Mot.MyTableID = MyTable.ID ' . 'LEFT JOIN (SELECT MyLastTable.MyOtherTableID, COUNT(1) as MyLastTableCount FROM MyLastTable ' . 'GROUP BY MyOtherTableID) AS "Mlt" ON Mlt.MyOtherTableID = Mot.ID ' . 'ORDER BY "_SortColumn0" DESC', $query->sql($parameters));
 }

 public function testWhereAny()
 {
     $query = new SQLQuery();
     $query->setFrom('MyTable');
     $query->whereAny(array("Monkey = 'Chimp'", "Color = 'Brown'"));
     $this->assertEquals("SELECT * FROM MyTable WHERE (Monkey = 'Chimp' OR Color = 'Brown')", $query->sql());
 }

 function augmentSQL(SQLQuery &$query)
 {
     if (!$this->stat('enabled')) {
         return false;
     }
     if (($lang = self::current_lang()) && !self::is_default_lang() || self::$bypass) {
         foreach ($query->from as $table => $dummy) {
             if (!isset($baseTable)) {
                 $baseTable = $table;
             }
             if (self::table_exists("{$table}_lang")) {
                 $query->renameTable($table, $table . '_lang');
                 if (stripos($query->sql(), '.ID')) {
                     // Every reference to ID is now OriginalLangID
                     $query->replaceText(".ID", ".OriginalLangID");
                     $query->where = str_replace("`ID`", "`OriginalLangID`", $query->where);
                     $query->select[] = "`{$baseTable}_lang`.OriginalLangID AS ID";
                 }
                 if ($query->where) {
                     foreach ($query->where as $i => $wherecl) {
                         if (substr($wherecl, 0, 4) == 'ID =') {
                             // Another reference to ID to be changed
                             $query->where[$i] = str_replace('ID =', 'OriginalLangID =', $wherecl);
                         } else {
                             $parts = explode(' AND ', $wherecl);
                             foreach ($parts as $j => $part) {
                                 // Divide this clause between the left ($innerparts[1]) and right($innerparts[2]) part of the condition
                                 ereg('(`?[[:alnum:]_-]*`?\\.?`?[[:alnum:]_-]*`?)(.*)', $part, $innerparts);
                                 if (strpos($innerparts[1], '.') === false) {
                                     //it may be ambiguous, so sometimes we will need to add the table
                                     $parts[$j] = ($this->isInAugmentedTable($innerparts[1], $table) ? "`{$table}_lang`." : "") . "{$part}";
                                 } else {
                                     /* if the table has been specified we have to determine if the original (without _lang) name has to be used
                                      * because we don't have the queried field in the augmented table (which usually means
                                      * that is not a translatable field)
                                      */
                                     $clauseparts = explode('.', $innerparts[1]);
                                     $originalTable = str_replace('`', '', str_replace('_lang', '', $clauseparts[0]));
                                     $parts[$j] = ($this->isInAugmentedTable($clauseparts[1], $originalTable) ? "`{$originalTable}_lang`" : "`{$originalTable}`") . ".{$clauseparts[1]}{$innerparts[2]}";
                                 }
                             }
                             $query->where[$i] = implode(' AND ', $parts);
                         }
                     }
                 }
                 if ($table != $baseTable) {
                     $query->from["{$table}_lang"] = $query->from[$table];
                 } else {
                     // _lang is now the base table (the first one)
                     $query->from = array("{$table}_lang" => $query->from[$table]) + $query->from;
                 }
                 // unless we are bypassing this query, add the language filter
                 if (!self::$bypass) {
                     $query->where[] = "`{$table}_lang`.Lang = '{$lang}'";
                 }
                 // unless this is a deletion, the query is applied to the joined table
                 if (!$query->delete) {
                     $query->from[$table] = "INNER JOIN `{$table}`" . " ON `{$table}_lang`.OriginalLangID = `{$table}`.ID";
                     /* if we are selecting fields (not doing counts for example) we need to select everything from
                      * the original table (was renamed to _lang) since some fields that we require may be there
                      */
                     if ($query->select[0][0] == '`') {
                         $query->select = array_merge(array("`{$table}`.*"), $query->select);
                     }
                 } else {
                     unset($query->from[$table]);
                 }
             } else {
                 $query->from[$table] = str_replace("`{$table}`.OriginalLangID", "`{$table}`.ID", $query->from[$table]);
             }
         }
     }
 }

	function testSelectWithOrderbyClause() {
		// numeric limit
		$query = new SQLQuery();
		$query->from[] = "MyTable";
		$query->orderby('MyName ASC');
		// can't escape as we don't know if ASC or DESC is appended
		$this->assertEquals("SELECT * FROM MyTable ORDER BY MyName ASC", $query->sql());
		
		// array limit
		$query = new SQLQuery();
		$query->from[] = "MyTable";
		$query->orderby(array('sort'=>'MyName'));
		$this->assertEquals("SELECT * FROM MyTable ORDER BY `MyName`", $query->sql());

		// array limit with start (MySQL specific)
		$query = new SQLQuery();
		$query->from[] = "MyTable";
		$query->orderby(array('sort'=>'MyName','dir'=>'desc'));
		$this->assertEquals("SELECT * FROM MyTable ORDER BY `MyName` DESC", $query->sql());
	}

 public function testInnerJoin()
 {
     $query = new SQLQuery();
     $query->from('MyTable');
     $query->innerJoin('MyOtherTable', 'MyOtherTable.ID = 2');
     $query->leftJoin('MyLastTable', 'MyOtherTable.ID = MyLastTable.ID');
     $this->assertEquals('SELECT * FROM MyTable ' . 'INNER JOIN "MyOtherTable" AS "MyOtherTable" ON MyOtherTable.ID = 2 ' . 'LEFT JOIN "MyLastTable" AS "MyLastTable" ON MyOtherTable.ID = MyLastTable.ID', $query->sql());
     $query = new SQLQuery();
     $query->from('MyTable');
     $query->innerJoin('MyOtherTable', 'MyOtherTable.ID = 2', 'table1');
     $query->leftJoin('MyLastTable', 'MyOtherTable.ID = MyLastTable.ID', 'table2');
     $this->assertEquals('SELECT * FROM MyTable ' . 'INNER JOIN "MyOtherTable" AS "table1" ON MyOtherTable.ID = 2 ' . 'LEFT JOIN "MyLastTable" AS "table2" ON MyOtherTable.ID = MyLastTable.ID', $query->sql());
 }

 /**
  * Run a remote query against the remote DB
  *
  * @param SQLQuery $query
  * @return SS_Query
  */
 public function query(SQLQuery $query)
 {
     return $this->getRemoteConnection()->query($query->sql());
 }

    /**
     * Test deprecation of SQLQuery::setDelete/getDelete
     */
    public function testDeprecatedSetDelete()
    {
        // Temporarily disable deprecation
        Deprecation::notification_version(null);
        $query = new SQLQuery();
        $query->setSelect(array('"SQLQueryTest_DO"."Name"'));
        $query->setFrom('"SQLQueryTest_DO"');
        $query->setWhere(array('"SQLQueryTest_DO"."Name"' => 'Andrew'));
        // Check SQL for select
        $this->assertSQLEquals(<<<EOS
SELECT "SQLQueryTest_DO"."Name" FROM "SQLQueryTest_DO"
WHERE ("SQLQueryTest_DO"."Name" = ?)
EOS
, $query->sql($parameters));
        $this->assertEquals(array('Andrew'), $parameters);
        // Check setDelete works
        $query->setDelete(true);
        $this->assertSQLEquals(<<<EOS
DELETE FROM "SQLQueryTest_DO"
WHERE ("SQLQueryTest_DO"."Name" = ?)
EOS
, $query->sql($parameters));
        $this->assertEquals(array('Andrew'), $parameters);
        // Check that setDelete back to false restores the state
        $query->setDelete(false);
        $this->assertSQLEquals(<<<EOS
SELECT "SQLQueryTest_DO"."Name" FROM "SQLQueryTest_DO"
WHERE ("SQLQueryTest_DO"."Name" = ?)
EOS
, $query->sql($parameters));
        $this->assertEquals(array('Andrew'), $parameters);
    }

 /**
  * Polls the message queue. Will return a map of senders with the lastest message ID
  * @see http://stackoverflow.com/questions/12102200/get-records-with-max-value-for-each-group-of-grouped-sql-results
  * @param SS_HTTPRequest $request
  */
 public function get_openchats(SS_HTTPRequest $request)
 {
     if (!Permission::checkMember(Member::currentUser(), "CMS_ACCESS_LiveChatAdmin")) {
         header("HTTP/1.0 403 Forbidden");
         die('You do not have permission to use the live chat module');
     }
     // find the messages that have been sent to you
     $query1 = new SQLQuery("*", "LiveChatMessage", "ToID = " . (int) Member::currentUserID());
     $query1->addOrderBy("ID DESC");
     $query1->addWhere("ClassName = 'LiveChatMessage'");
     $query2 = new SQLQuery("ID, FromID, FromName", '(' . $query1->sql() . ') x');
     $query2->addGroupBy("FromID, FromName");
     // only select the largest ID, unique for each sender
     $result = $query2->execute();
     $returnar = array();
     // add the names to the array
     foreach ($result as $id) {
         $member = Member::get()->byID($id['FromID']);
         $returnar[$id['ID']] = array("Name" => $member ? $member->getName() : $id['FromName'], "FromID" => $id['FromID'] ? $id['FromID'] : md5($id['FromName']));
     }
     header('Content-Type: application/json');
     die(json_encode($returnar));
 }