PHP SQLSelect类代码示例

 /**
  * Update any requests to limit the results to the current site
  */
 public function augmentSQL(SQLSelect $query, DataQuery $dataQuery = NULL)
 {
     if (Subsite::$disable_subsite_filter) {
         return;
     }
     // If you're querying by ID, ignore the sub-site - this is a bit ugly...
     if ($query->filtersOnID()) {
         return;
     }
     $regexp = '/^(.*\\.)?("|`)?SubsiteID("|`)?\\s?=/';
     foreach ($query->getWhereParameterised($parameters) as $predicate) {
         if (preg_match($regexp, $predicate)) {
             return;
         }
     }
     try {
         $subsiteID = (int) Subsite::currentSubsiteID();
         $froms = $query->getFrom();
         $froms = array_keys($froms);
         $tableName = array_shift($froms);
         if ($tableName != 'SiteConfig') {
             return;
         }
         $query->addWhere("\"{$tableName}\".\"SubsiteID\" IN ({$subsiteID})");
     } catch (UnexpectedValueException $e) {
         // No subsites exist yet
     }
 }

 /**
  * Update any requests to limit the results to the current site
  */
 public function augmentSQL(SQLSelect $query, DataQuery $dataQuery = NULL)
 {
     if (Subsite::$disable_subsite_filter) {
         return;
     }
     if ($dataQuery->getQueryParam('Subsite.filter') === false) {
         return;
     }
     // If you're querying by ID, ignore the sub-site - this is a bit ugly...
     // if(!$query->where || (strpos($query->where[0], ".\"ID\" = ") === false && strpos($query->where[0], ".`ID` = ") === false && strpos($query->where[0], ".ID = ") === false && strpos($query->where[0], "ID = ") !== 0)) {
     if ($query->filtersOnID()) {
         return;
     }
     if (Subsite::$force_subsite) {
         $subsiteID = Subsite::$force_subsite;
     } else {
         /*if($context = DataObject::context_obj()) $subsiteID = (int)$context->SubsiteID;
           else */
         $subsiteID = (int) Subsite::currentSubsiteID();
     }
     // The foreach is an ugly way of getting the first key :-)
     foreach ($query->getFrom() as $tableName => $info) {
         // The tableName should be SiteTree or SiteTree_Live...
         if (strpos($tableName, 'SiteTree') === false) {
             break;
         }
         $query->addWhere("\"{$tableName}\".\"SubsiteID\" IN ({$subsiteID})");
         break;
     }
 }

 /**
  * Update any requests to limit the results to the current site
  */
 public function augmentSQL(SQLSelect $query)
 {
     if (Subsite::$disable_subsite_filter) {
         return;
     }
     // If you're querying by ID, ignore the sub-site - this is a bit ugly...
     if ($query->filtersOnID()) {
         return;
     }
     $regexp = '/^(.*\\.)?("|`)?SubsiteID("|`)?\\s?=/';
     foreach ($query->getWhereParameterised($parameters) as $predicate) {
         if (preg_match($regexp, $predicate)) {
             return;
         }
     }
     /*if($context = DataObject::context_obj()) $subsiteID = (int)$context->SubsiteID;
     		else */
     $subsiteID = (int) Subsite::currentSubsiteID();
     $froms = $query->getFrom();
     $froms = array_keys($froms);
     $tableName = array_shift($froms);
     if ($tableName != 'SiteConfig') {
         return;
     }
     $query->addWhere("\"{$tableName}\".\"SubsiteID\" IN ({$subsiteID})");
 }

 /**
  * Return the LIMIT clause ready for inserting into a query.
  *
  * @param SQLSelect $query The expression object to build from
  * @param array $parameters Out parameter for the resulting query parameters
  * @return string The finalised limit SQL fragment
  */
 public function buildLimitFragment(SQLSelect $query, array &$parameters)
 {
     $nl = $this->getSeparator();
     // Ensure limit is given
     $limit = $query->getLimit();
     if (empty($limit)) {
         return '';
     }
     // For literal values return this as the limit SQL
     if (!is_array($limit)) {
         return "{$nl}LIMIT {$limit}";
     }
     // Assert that the array version provides the 'limit' key
     if (!array_key_exists('limit', $limit) || $limit['limit'] !== null && !is_numeric($limit['limit'])) {
         throw new InvalidArgumentException('SQLite3QueryBuilder::buildLimitSQL(): Wrong format for $limit: ' . var_export($limit, true));
     }
     $clause = "{$nl}";
     if ($limit['limit'] !== null) {
         $clause .= "LIMIT {$limit['limit']} ";
     } else {
         $clause .= "LIMIT -1 ";
     }
     if (isset($limit['start']) && is_numeric($limit['start']) && $limit['start'] !== 0) {
         $clause .= "OFFSET {$limit['start']}";
     }
     return $clause;
 }

  function LoadFromDB() {
    global $POSTS_PER_PAGE;

    $s = new SQLSelect();
    $s->AddTable("bbs_topics");
    $s->AddWhere("bbs_topics.id=".$this->id);
    $this->topic = SQLLib::SelectRow($s->GetQuery());
    if(!$this->topic) return false;

    $s = new SQLSelect();
    $s->AddField("count(*) as c");
    $s->AddTable("bbs_posts");
    $s->AddWhere("bbs_posts.topic=".$this->id);
    $this->postcount = SQLLib::SelectRow($s->GetQuery())->c;

    $s = new BM_Query();
    $s->AddTable("bbs_posts");
    $s->AddField("bbs_posts.id as id");
    $s->AddField("bbs_posts.post as post");
    $s->AddField("bbs_posts.added as added");
    $s->attach(array("bbs_posts"=>"author"),array("users as user"=>"id"));
    $s->AddWhere("bbs_posts.topic=".$this->id);
    //$s->SetLimit( $POSTS_PER_PAGE, (int)(($this->page - 1)*$POSTS_PER_PAGE) );

    $this->paginator = new PouetPaginator();
    $this->paginator->SetData( "topic.php?which=".$this->id, $this->postcount, $POSTS_PER_PAGE, $_GET["page"] );
    $this->paginator->SetLimitOnQuery( $s );

    $this->posts = $s->perform();

    $this->title = _html($this->topic->topic);
  }

 public function getMap()
 {
     $this->getDimensions();
     if (empty($this->map)) {
         $sel = new SQLSelect($this->table);
         $res = $sel->execute();
         $this->map = parallelToAssoc($res[strtoupper($this->idfields[0])], $res[strtoupper($this->idfields[1])]);
     }
     return $this->map;
 }

 public function getPossLanguages()
 {
     if (empty($this->posslanguages)) {
         $getl = new SQLSelect("translation_language");
         $getl->fetchas = "row";
         $res = $getl->execute();
         $this->posslanguages = $res;
     }
     return $this->posslanguages;
 }

 public function processFields()
 {
     if (!isset($this->fields["language_keycode"]["options"])) {
         $opsel = new SQLSelect("translation_language");
         $opsel->selectfields = array("id" => "keycode", "label" => "name_def");
         $opsel->fetchas = "row";
         $res = $opsel->execute();
         $options = $res;
         $this->fields["language_keycode"]["options"] = $options;
     }
 }

 /**
  * Extracts the limit and offset from the limit clause
  * 
  * @param SQLSelect $query
  * @return array Two item array with $limit and $offset as values
  * @throws InvalidArgumentException
  */
 protected function parseLimit(SQLSelect $query)
 {
     $limit = '';
     $offset = '0';
     if (is_array($query->getLimit())) {
         $limitArr = $query->getLimit();
         if (isset($limitArr['limit'])) {
             $limit = $limitArr['limit'];
         }
         if (isset($limitArr['start'])) {
             $offset = $limitArr['start'];
         }
     } else {
         if (preg_match('/^([0-9]+) offset ([0-9]+)$/i', trim($query->getLimit()), $matches)) {
             $limit = $matches[1];
             $offset = $matches[2];
         } else {
             //could be a comma delimited string
             $bits = explode(',', $query->getLimit());
             if (sizeof($bits) > 1) {
                 list($offset, $limit) = $bits;
             } else {
                 $limit = $bits[0];
             }
         }
     }
     return array($limit, $offset);
 }

 /**
  * Attempt to find an old/renamed page from some given the URL as an array
  *
  * @param array $params The array of URL, e.g. /foo/bar as array('foo', 'bar')
  * @param SiteTree $parent The current parent in the recursive flow
  * @param boolean $redirect Whether we've found an old page worthy of a redirect
  *
  * @return string|boolean False, or the new URL
  */
 public static function find_old_page($params, $parent = null, $redirect = false)
 {
     $parent = is_numeric($parent) ? SiteTree::get()->byId($parent) : $parent;
     $params = (array) $params;
     $URL = rawurlencode(array_shift($params));
     if (empty($URL)) {
         return false;
     }
     if ($parent) {
         $page = SiteTree::get()->filter(array('ParentID' => $parent->ID, 'URLSegment' => $URL))->First();
     } else {
         $page = SiteTree::get()->filter(array('URLSegment' => $URL))->First();
     }
     if (!$page) {
         // If we haven't found a candidate, lets resort to finding an old page with this URL segment
         $oldFilter = array('"SiteTree_versions"."URLSegment"' => $URL, '"SiteTree_versions"."WasPublished"' => true);
         if ($parent) {
             $oldFilter[] = array('"SiteTree_versions"."ParentID"' => $parent->ID);
         }
         $query = new SQLSelect('"RecordID"', '"SiteTree_versions"', $oldFilter, '"LastEdited" DESC', null, null, 1);
         $record = $query->execute()->first();
         if ($record) {
             $page = SiteTree::get()->byID($record['RecordID']);
             $redirect = true;
         }
     }
     if ($page && $page->canView()) {
         if (count($params)) {
             // We have to go deeper!
             $ret = self::find_old_page($params, $page, $redirect);
             if ($ret) {
                 // A valid child page was found! We can return it
                 return $ret;
             } else {
                 // No valid page found.
                 if ($redirect) {
                     // If we had some redirect to be done, lets do it. imagine /foo/action -> /bar/action, we still want this redirect to happen if action isn't a page
                     return $page->Link() . implode('/', $params);
                 }
             }
         } else {
             // We've found the final, end all, page.
             return $page->Link();
         }
     }
     return false;
 }

 /**
  * Update any requests to limit the results to the current site
  */
 public function augmentSQL(SQLSelect $query)
 {
     if (Subsite::$disable_subsite_filter) {
         return;
     }
     // If you're querying by ID, ignore the sub-site - this is a bit ugly... (but it was WAYYYYYYYYY worse)
     //@TODO I don't think excluding if SiteTree_ImageTracking is a good idea however because of the SS 3.0 api and ManyManyList::removeAll() changing the from table after this function is called there isn't much of a choice
     $from = $query->getFrom();
     if (isset($from['SiteTree_ImageTracking']) || $query->filtersOnID()) {
         return;
     }
     $subsiteID = (int) Subsite::currentSubsiteID();
     // The foreach is an ugly way of getting the first key :-)
     foreach ($query->getFrom() as $tableName => $info) {
         $where = "\"{$tableName}\".\"SubsiteID\" IN (0, {$subsiteID})";
         $query->addWhere($where);
         break;
     }
     $sect = array_values($query->getSelect());
     $isCounting = strpos($sect[0], 'COUNT') !== false;
     // Ordering when deleting or counting doesn't apply
     if (!$isCounting) {
         $query->addOrderBy("\"SubsiteID\"");
     }
 }

 public function build_query()
 {
     $setstring = "";
     // If insertIfAbsent is set, check for the row
     if ($this->insertIfAbsent) {
         $sel = new SQLSelect($this->table, $this->schema);
         $sel->selectfields[] = "1";
         $sel->wherearray = $this->selectors;
         $val = $sel->execute();
         if ($val == NULL || empty($val) || $val[1] == array()) {
             $ins = new SQLInsert($this->table, $this->values);
             $q = $ins->build_query();
             $this->query = $q;
             return $this->query;
         }
     }
     // Perform the UPDATE
     foreach ($this->values as $key => $value) {
         // Skip selectors
         if (isset($this->selectors[$key])) {
             continue;
         }
         // Build 'SET' string
         if ($setstring != "") {
             $setstring .= ",";
         }
         $setstring .= $key . "=" . dbize($value, $this->get_column_datatype($key));
     }
     if ($setstring != "") {
         $setstring = " SET " . $setstring;
     }
     // Add selectors to WHERE clause
     foreach ($this->selectors as $key => $value) {
         $this->wherefields[] = "{$key} = " . dbize($value, $this->get_column_datatype($key));
     }
     $wherestring = $this->wherefields_to_string();
     if ($setstring != "" && $wherestring != "") {
         $query = "UPDATE " . $this->table_with_schema() . $setstring . $wherestring;
         $this->query = $query;
     }
     return $this->query;
 }

  function LoadFromDB() {
    $s = new SQLSelect();

    $this->group = PouetGroup::Spawn($this->id);
    $this->addeduser = PouetUser::Spawn($this->group->addedUser);

    // not to boast or anything, but this is fucking beautiful.

    $sub = new SQLSelect();
    $sub->AddField("max(comments.addedDate) as maxDate");
    $sub->AddField("comments.which");
    $sub->AddTable("comments");
    $sub->AddJoin("left","prods","prods.id = comments.which");
    //$sub->AddOrder("comments.addedDate desc");
    $sub->AddGroup("comments.which");
    $sub->AddWhere(sprintf_esc("(prods.group1 = %d) or (prods.group2 = %d) or (prods.group3 = %d)",$this->id,$this->id,$this->id));

    $s = new BM_Query("prods");
    $s->AddField("cmts.addedDate as lastcomment");
    $s->AddField("cmts.rating as lastcommentrating");
    $s->AddJoin("left","(select comments.addedDate,comments.who,comments.which,comments.rating from (".$sub->GetQuery().") as dummy left join comments on dummy.maxDate = comments.addedDate and dummy.which = comments.which) as cmts","cmts.which=prods.id");
    $s->attach(array("cmts"=>"who"),array("users as user"=>"id"));
    $s->AddWhere(sprintf_esc("(prods.group1 = %d) or (prods.group2 = %d) or (prods.group3 = %d)",$this->id,$this->id,$this->id));

    $r = !!$_GET["reverse"];
    switch($_GET["order"])
    {
      case "type": $s->AddOrder("prods.type ".($r?"DESC":"ASC")); break;
      case "party": $s->AddOrder("prods_party.name ".($r?"DESC":"ASC")); $s->AddOrder("prods.party_year ".($r?"DESC":"ASC")); $s->AddOrder("prods.party_place ".($r?"DESC":"ASC")); break;
      case "release": $s->AddOrder("prods.releaseDate ".($r?"ASC":"DESC")); break;
      case "thumbup": $s->AddOrder("prods.voteup ".($r?"ASC":"DESC")); break;
      case "thumbpig": $s->AddOrder("prods.votepig ".($r?"ASC":"DESC")); break;
      case "thumbdown": $s->AddOrder("prods.votedown ".($r?"ASC":"DESC")); break;
      case "avg": $s->AddOrder("prods.voteavg ".($r?"ASC":"DESC")); break;
      case "views": $s->AddOrder("prods.views ".($r?"ASC":"DESC")); break;
      case "latestcomment": $s->AddOrder("lastcomment ".($r?"ASC":"DESC")); break;
      default: $s->AddOrder("prods.name ".($r?"DESC":"ASC")); break;
    }
    $this->prods = $s->perform();
    PouetCollectPlatforms($this->prods);
    PouetCollectAwards($this->prods);

    $s = new BM_Query("affiliatedboards");
    $s->attach(array("affiliatedboards"=>"board"),array("boards as board"=>"id"));
    $s->AddWhere(sprintf_esc("affiliatedboards.group=%d",$this->id));
    $this->affil = $s->perform();
  }

  function Load( $cached = false ) {
    $s = new SQLSelect();
    $s->AddTable("buttons");
    $s->AddOrder("rand()");
    $s->AddWhere("dead = 0");
    $s->SetLimit("1");
    $this->data = SQLLib::SelectRow($s->GetQuery());

    $this->title = $this->data->type;
  }

 /**
  * For each author, add an AuthorHelper
  */
 private function migrateAuthors()
 {
     /** @var SQLSelect $query */
     $query = SQLSelect::create();
     $query->setSelect('Author')->setFrom('News')->setDistinct(true);
     $authors = $query->execute();
     foreach ($authors as $author) {
         /** Create a new author if it doesn't exist */
         if (!($authorHelper = AuthorHelper::get()->filter(array('OriginalName' => trim($author['Author'])))->first())) {
             /** @var AuthorHelper $authorHelper */
             $authorHelper = AuthorHelper::create();
             $authorHelper->OriginalName = $author['Author'];
             $authorHelper->write();
         }
         $sql = "UPDATE `News` SET `AuthorHelperID` = '" . $authorHelper->ID . "' WHERE Author = '" . $author['Author'] . "'";
         DB::query($sql);
     }
 }