PHP Gdn::sql方法代码示例

 /**
  * List all update checks.
  *
  * @param bool|false $Offset
  * @param string $SortField
  */
 public function index($Offset = false, $SortField = '')
 {
     $this->permission('Garden.Settings.Manage');
     $this->addSideMenu('updates');
     $this->addJsFile('jquery.gardenmorepager.js');
     $this->title('Remote Updates');
     $this->Form->Method = 'get';
     $Limit = 30;
     $SortField = $SortField == 'CountComments' ? 'c.CountComments' : 'c.DateInserted';
     // Input Validation
     $Offset = is_numeric($Offset) ? $Offset : 0;
     // What the actual model in my controller, guy?
     $this->UpdateData = Gdn::sql()->query("\n            select s.Location, s.RemoteIp, c.DateInserted, c.CountUsers, c.CountDiscussions, c.CountComments\n            from GDN_UpdateCheckSource s\n            join (select SourceID, max(UpdateCheckID) as UpdateCheckID from GDN_UpdateCheck group by SourceID) mc\n                on s.SourceID = mc.SourceID\n            join GDN_UpdateCheck c\n                on mc.UpdateCheckID = c.UpdateCheckID\n            order by {$SortField} desc\n            limit {$Offset}, {$Limit}");
     $TotalRecords = Gdn::sql()->select('SourceID', 'count', 'CountSources')->from('UpdateCheckSource')->get()->firstRow()->CountSources;
     // Build a pager
     $PagerFactory = new Gdn_PagerFactory();
     $this->Pager = $PagerFactory->getPager('MorePager', $this);
     $this->Pager->MoreCode = 'More';
     $this->Pager->LessCode = 'Previous';
     $this->Pager->ClientID = 'Pager';
     $this->Pager->Wrapper = '<tr %1$s><td colspan="6">%2$s</td></tr>';
     $this->Pager->configure($Offset, $Limit, $TotalRecords, 'updates/index/%1$s/' . urlencode($SortField));
     // Deliver json data if necessary
     if ($this->_DeliveryType != DELIVERY_TYPE_ALL) {
         $this->setJson('LessRow', $this->Pager->toString('less'));
         $this->setJson('MoreRow', $this->Pager->toString('more'));
     }
     $this->render();
 }

 /**
  *
  *
  * @param $ForeignType
  * @param array $ForeignIDs
  * @return Gdn_DataSet
  */
 public function getAll($ForeignType, $ForeignIDs = array())
 {
     if (count($ForeignIDs) == 0) {
         return new Gdn_DataSet(array());
     }
     return Gdn::sql()->select('*')->from('Regarding')->where('ForeignType', $ForeignType)->whereIn('ForeignID', $ForeignIDs)->get();
 }

 public function pluginController_quoteMention_create($sender, $discussionID, $commentID, $username)
 {
     $sender->deliveryMethod(DELIVERY_METHOD_JSON);
     $user = Gdn::userModel()->getByUsername($username);
     $discussionModel = new DiscussionModel();
     $discussion = $discussionModel->getID($discussionID);
     if (!$user || !$discussion) {
         throw notFoundException();
     }
     // Make sure this endpoint can't be used to snoop around.
     $sender->permission('Vanilla.Discussions.View', true, 'Category', $discussion->PermissionCategoryID);
     // Find the previous comment of the mentioned user in this discussion.
     $item = Gdn::sql()->getWhere('Comment', ['DiscussionID' => $discussion->DiscussionID, 'InsertUserID' => $user->UserID, 'CommentID <' => $commentID], 'CommentID', 'desc', 1)->firstRow();
     // The items ID in the DOM used for highlighting.
     if ($item) {
         $target = '#Comment_' . $item->CommentID;
         // The mentioned user might be the discussion creator.
     } elseif ($discussion->InsertUserID == $user->UserID) {
         $item = $discussion;
         $target = '#Discussion_' . $item->DiscussionID;
     }
     if (!$item) {
         // A success response code always means that a comment was found.
         $sender->statusCode(404);
     }
     $sender->renderData($item ? ['html' => nl2br(sliceString(Gdn_Format::plainText($item->Body, $item->Format), c('QuoteMention.MaxLength', 400))), 'target' => $target] : []);
 }

 public function structure()
 {
     // Get a user for operations.
     $UserID = Gdn::sql()->GetWhere('User', array('Name' => 'Akismet', 'Admin' => 2))->Value('UserID');
     if (!$UserID) {
         $UserID = Gdn::sql()->Insert('User', array('Name' => 'Akismet', 'Password' => RandomString('20'), 'HashMethod' => 'Random', 'Email' => 'akismet@domain.com', 'DateInserted' => Gdn_Format::toDateTime(), 'Admin' => '2'));
     }
     saveToConfig('Plugins.Akismet.UserID', $UserID);
 }

 /**
  * Initialize a new instance of the {@link CategoryCollection} class.
  *
  * @param Gdn_SQLDriver|null $sql The database layer dependency.
  * @param Gdn_Cache|null $cache The cache layer dependency.
  */
 public function __construct(Gdn_SQLDriver $sql = null, Gdn_Cache $cache = null)
 {
     if ($sql === null) {
         $sql = Gdn::sql();
     }
     $this->sql = $sql;
     if ($cache === null) {
         $cache = Gdn::cache();
     }
     $this->cache = $cache;
 }

 protected function getCount($Table)
 {
     // Try and get the count from the cache.
     $Key = "{$Table}.CountRows";
     $Count = Gdn::cache()->get($Key);
     if ($Count !== Gdn_Cache::CACHEOP_FAILURE) {
         return $Count;
     }
     // The count wasn't in the cache so grab it from the table.
     $Count = Gdn::sql()->select($Table . 'ID', 'count', 'CountRows')->from($Table)->get()->value('CountRows');
     // Save the value to the cache.
     Gdn::cache()->store($Key, $Count, array(Gdn_Cache::FEATURE_EXPIRY => 5 * 60 + mt_rand(0, 30)));
     return $Count;
 }

 /**
  * Initialize a new instance of the {@link CategoryCollection} class.
  *
  * @param Gdn_SQLDriver|null $sql The database layer dependency.
  * @param Gdn_Cache|null $cache The cache layer dependency.
  */
 public function __construct(Gdn_SQLDriver $sql = null, Gdn_Cache $cache = null)
 {
     if ($sql === null) {
         $sql = Gdn::sql();
     }
     $this->sql = $sql;
     if ($cache === null) {
         $cache = Gdn::cache();
     }
     $this->cache = $cache;
     $this->setStaticCalculator([$this, 'defaultCalculator']);
     $this->setUserCalculator(function (&$category) {
         // do nothing
     });
 }

 public function getData()
 {
     if (Gdn::session()->isValid()) {
         $BookmarkIDs = Gdn::sql()->select('DiscussionID')->from('UserDiscussion')->where('UserID', Gdn::session()->UserID)->where('Bookmarked', 1)->get()->resultArray();
         $BookmarkIDs = consolidateArrayValuesByKey($BookmarkIDs, 'DiscussionID');
         if (count($BookmarkIDs)) {
             $DiscussionModel = new DiscussionModel();
             DiscussionModel::CategoryPermissions();
             $DiscussionModel->SQL->whereIn('d.DiscussionID', $BookmarkIDs);
             $Bookmarks = $DiscussionModel->get(0, $this->Limit, array('w.Bookmarked' => '1'));
             $this->setData('Bookmarks', $Bookmarks);
         } else {
             $this->setData('Bookmarks', new Gdn_DataSet());
         }
     }
 }

 /**
  * Add new filters to the discussion model
  *
  * @param DiscussionModel $sender Sending controller instance.
  * @param array $args Event arguments.
  */
 public function discussionModel_initStatic_handler($sender, $args)
 {
     DiscussionModel::addFilterSet('prefix', 'Prefixes');
     DiscussionModel::addFilter('has-prefix', 'Has prefix', ['d.Prefix IS NOT NULL' => null], 'base-filter', 'prefix');
     DiscussionModel::addFilter('no-prefix', 'No prefix', ['d.Prefix IS NULL' => null], 'base-filter', 'prefix');
     $currentPrefixes = PrefixDiscussionPlugin::getPrefixes();
     unset($currentPrefixes['-']);
     $usedPrefixesResult = Gdn::sql()->select('Prefix')->from('Discussion')->where('Prefix IS NOT NULL')->get()->resultArray();
     foreach ($usedPrefixesResult as $row) {
         $prefix = $row['Prefix'];
         if (!isset($currentPrefixes[$prefix])) {
             $currentPrefixes[$prefix] = $prefix;
         }
     }
     natsort($currentPrefixes);
     foreach ($currentPrefixes as $prefix) {
         DiscussionModel::addFilter('prefix-' . $this->stringToSlug($prefix), $prefix, ['d.Prefix' => $prefix], 'prefix-filter', 'prefix');
     }
 }

 /**
  * Delete all of the Vanilla related information for a specific user.
  *
  * @since 2.1
  *
  * @param int $userID The ID of the user to delete.
  * @param array $options An array of options:
  *  - DeleteMethod: One of delete, wipe, or NULL
  */
 public function deleteUserData($userID, $options = array(), &$data = null)
 {
     $sql = Gdn::sql();
     // Remove discussion watch records and drafts.
     $sql->delete('UserDiscussion', array('UserID' => $userID));
     Gdn::userModel()->getDelete('Draft', array('InsertUserID' => $userID), $data);
     // Comment deletion depends on method selected
     $deleteMethod = val('DeleteMethod', $options, 'delete');
     if ($deleteMethod == 'delete') {
         // Get a list of category IDs that has this user as the most recent poster.
         $discussionCats = $sql->select('cat.CategoryID')->from('Category cat')->join('Discussion d', 'd.DiscussionID = cat.LastDiscussionID')->where('d.InsertUserID', $userID)->get()->resultArray();
         $commentCats = $sql->select('cat.CategoryID')->from('Category cat')->join('Comment c', 'c.CommentID = cat.LastCommentID')->where('c.InsertUserID', $userID)->get()->resultArray();
         $categoryIDs = array_unique(array_merge(array_column($discussionCats, 'CategoryID'), array_column($commentCats, 'CategoryID')));
         // Grab all of the discussions that the user has engaged in.
         $discussionIDs = $sql->select('DiscussionID')->from('Comment')->where('InsertUserID', $userID)->groupBy('DiscussionID')->get()->resultArray();
         $discussionIDs = array_column($discussionIDs, 'DiscussionID');
         Gdn::userModel()->getDelete('Comment', array('InsertUserID' => $userID), $data);
         // Update the comment counts.
         $commentCounts = $sql->select('DiscussionID')->select('CommentID', 'count', 'CountComments')->select('CommentID', 'max', 'LastCommentID')->whereIn('DiscussionID', $discussionIDs)->groupBy('DiscussionID')->get('Comment')->resultArray();
         foreach ($commentCounts as $row) {
             $sql->put('Discussion', array('CountComments' => $row['CountComments'] + 1, 'LastCommentID' => $row['LastCommentID']), array('DiscussionID' => $row['DiscussionID']));
         }
         // Update the last user IDs.
         $sql->update('Discussion d')->join('Comment c', 'd.LastCommentID = c.CommentID', 'left')->set('d.LastCommentUserID', 'c.InsertUserID', false, false)->set('d.DateLastComment', 'coalesce(c.DateInserted, d.DateInserted)', false, false)->whereIn('d.DiscussionID', $discussionIDs)->put();
         // Update the last posts.
         $discussions = $sql->whereIn('DiscussionID', $discussionIDs)->where('LastCommentUserID', $userID)->get('Discussion');
         // Delete the user's discussions.
         Gdn::userModel()->getDelete('Discussion', array('InsertUserID' => $userID), $data);
         // Update the appropriate recent posts in the categories.
         $categoryModel = new CategoryModel();
         foreach ($categoryIDs as $categoryID) {
             $categoryModel->setRecentPost($categoryID);
         }
     } elseif ($deleteMethod == 'wipe') {
         // Erase the user's discussions.
         $sql->update('Discussion')->set('Body', t('The user and all related content has been deleted.'))->set('Format', 'Deleted')->where('InsertUserID', $userID)->put();
         $sql->update('Comment')->set('Body', t('The user and all related content has been deleted.'))->set('Format', 'Deleted')->where('InsertUserID', $userID)->put();
     } else {
         // Leave comments
     }
     // Remove the user's profile information related to this application
     $sql->update('User')->set(array('CountDiscussions' => 0, 'CountUnreadDiscussions' => 0, 'CountComments' => 0, 'CountDrafts' => 0, 'CountBookmarks' => 0))->where('UserID', $userID)->put();
 }

 /**
  * Delete all of the Vanilla related information for a specific user.
  *
  * @since 2.1
  *
  * @param int $UserID The ID of the user to delete.
  * @param array $Options An array of options:
  *  - DeleteMethod: One of delete, wipe, or NULL
  */
 public function deleteUserData($UserID, $Options = array(), &$Data = null)
 {
     $SQL = Gdn::sql();
     // Remove discussion watch records and drafts.
     $SQL->delete('UserDiscussion', array('UserID' => $UserID));
     Gdn::userModel()->GetDelete('Draft', array('InsertUserID' => $UserID), $Data);
     // Comment deletion depends on method selected
     $DeleteMethod = val('DeleteMethod', $Options, 'delete');
     if ($DeleteMethod == 'delete') {
         // Clear out the last posts to the categories.
         $SQL->update('Category c')->join('Discussion d', 'd.DiscussionID = c.LastDiscussionID')->where('d.InsertUserID', $UserID)->set('c.LastDiscussionID', null)->set('c.LastCommentID', null)->put();
         $SQL->update('Category c')->join('Comment d', 'd.CommentID = c.LastCommentID')->where('d.InsertUserID', $UserID)->set('c.LastDiscussionID', null)->set('c.LastCommentID', null)->put();
         // Grab all of the discussions that the user has engaged in.
         $DiscussionIDs = $SQL->select('DiscussionID')->from('Comment')->where('InsertUserID', $UserID)->groupBy('DiscussionID')->get()->resultArray();
         $DiscussionIDs = consolidateArrayValuesByKey($DiscussionIDs, 'DiscussionID');
         Gdn::userModel()->GetDelete('Comment', array('InsertUserID' => $UserID), $Data);
         // Update the comment counts.
         $CommentCounts = $SQL->select('DiscussionID')->select('CommentID', 'count', 'CountComments')->select('CommentID', 'max', 'LastCommentID')->whereIn('DiscussionID', $DiscussionIDs)->groupBy('DiscussionID')->get('Comment')->resultArray();
         foreach ($CommentCounts as $Row) {
             $SQL->put('Discussion', array('CountComments' => $Row['CountComments'] + 1, 'LastCommentID' => $Row['LastCommentID']), array('DiscussionID' => $Row['DiscussionID']));
         }
         // Update the last user IDs.
         $SQL->update('Discussion d')->join('Comment c', 'd.LastCommentID = c.CommentID', 'left')->set('d.LastCommentUserID', 'c.InsertUserID', false, false)->set('d.DateLastComment', 'c.DateInserted', false, false)->whereIn('d.DiscussionID', $DiscussionIDs)->put();
         // Update the last posts.
         $Discussions = $SQL->whereIn('DiscussionID', $DiscussionIDs)->where('LastCommentUserID', $UserID)->get('Discussion');
         // Delete the user's dicussions
         Gdn::userModel()->GetDelete('Discussion', array('InsertUserID' => $UserID), $Data);
         // Update the appropriat recent posts in the categories.
         $CategoryModel = new CategoryModel();
         $Categories = $CategoryModel->getWhere(array('LastDiscussionID' => null))->resultArray();
         foreach ($Categories as $Category) {
             $CategoryModel->SetRecentPost($Category['CategoryID']);
         }
     } elseif ($DeleteMethod == 'wipe') {
         // Erase the user's dicussions
         $SQL->update('Discussion')->set('Body', t('The user and all related content has been deleted.'))->set('Format', 'Deleted')->where('InsertUserID', $UserID)->put();
         $SQL->update('Comment')->set('Body', t('The user and all related content has been deleted.'))->set('Format', 'Deleted')->where('InsertUserID', $UserID)->put();
     } else {
         // Leave comments
     }
     // Remove the user's profile information related to this application
     $SQL->update('User')->set(array('CountDiscussions' => 0, 'CountUnreadDiscussions' => 0, 'CountComments' => 0, 'CountDrafts' => 0, 'CountBookmarks' => 0))->where('UserID', $UserID)->put();
 }

 protected function getBirthdays()
 {
     $birthdays = json_decode(Gdn::get('BirthdayModule.Birthdays'));
     $token = date('y-m-d/H');
     if ($birthdays && $birthdays[0] == $token) {
         return $birthdays[1];
     }
     $date = new DateTime();
     if ($guestTimeZone = C('Garden.GuestTimeZone')) {
         try {
             $timeZone = new DateTimeZone($guestTimeZone);
             $offset = $timeZone->getOffset(new DateTime('now', new DateTimeZone('UTC')));
             $offset = -floor($offset / 3600);
             $date->modify("{$offset} hours");
         } catch (Exception $e) {
         }
     }
     $birthdays = Gdn::sql()->select('UserID')->from('User')->where("DATE_FORMAT(DateOfBirth, '%m-%d')", $date->format("'m-d'"), false, false)->get()->resultArray();
     $birthdays = array_column($birthdays, 'UserID');
     Gdn::set('BirthdayModule.Birthdays', json_encode([$token, $birthdays]));
     return $birthdays;
 }

 /**
  * Takes a set of discussion identifiers and returns their comment counts in the same order.
  */
 public function getCommentCounts()
 {
     $this->AllowJSONP(true);
     $vanilla_identifier = val('vanilla_identifier', $_GET);
     if (!is_array($vanilla_identifier)) {
         $vanilla_identifier = array($vanilla_identifier);
     }
     $vanilla_identifier = array_unique($vanilla_identifier);
     $FinalData = array_fill_keys($vanilla_identifier, 0);
     $Misses = array();
     $CacheKey = 'embed.comments.count.%s';
     $OriginalIDs = array();
     foreach ($vanilla_identifier as $ForeignID) {
         $HashedForeignID = ForeignIDHash($ForeignID);
         // Keep record of non-hashed identifiers for the reply
         $OriginalIDs[$HashedForeignID] = $ForeignID;
         $RealCacheKey = sprintf($CacheKey, $HashedForeignID);
         $Comments = Gdn::cache()->get($RealCacheKey);
         if ($Comments !== Gdn_Cache::CACHEOP_FAILURE) {
             $FinalData[$ForeignID] = $Comments;
         } else {
             $Misses[] = $HashedForeignID;
         }
     }
     if (sizeof($Misses)) {
         $CountData = Gdn::sql()->select('ForeignID, CountComments')->from('Discussion')->where('Type', 'page')->whereIn('ForeignID', $Misses)->get()->resultArray();
         foreach ($CountData as $Row) {
             // Get original identifier to send back
             $ForeignID = $OriginalIDs[$Row['ForeignID']];
             $FinalData[$ForeignID] = $Row['CountComments'];
             // Cache using the hashed identifier
             $RealCacheKey = sprintf($CacheKey, $Row['ForeignID']);
             Gdn::cache()->store($RealCacheKey, $Row['CountComments'], array(Gdn_Cache::FEATURE_EXPIRY => 60));
         }
     }
     $this->setData('CountData', $FinalData);
     $this->DeliveryMethod = DELIVERY_METHOD_JSON;
     $this->DeliveryType = DELIVERY_TYPE_DATA;
     $this->render();
 }

 public static function getViewsFallback($DiscussionID)
 {
     // Not found. Check main table.
     $Views = val('CountViews', Gdn::sql()->select('CountViews')->from('Discussion')->where('DiscussionID', $DiscussionID)->get()->firstRow(DATASET_TYPE_ARRAY), null);
     // Found. Insert into denormalized table and return.
     if (!is_null($Views)) {
         return $Views;
     }
     return null;
 }

 /**
  * Delete cached data for user.
  *
  * @param int|null $UserID The user to clear the cache for.
  * @return bool Returns **true** if the cache was cleared or **false** otherwise.
  */
 public function clearCache($UserID, $CacheTypesToClear = null)
 {
     if (is_null($UserID) || !$UserID) {
         return false;
     }
     if (is_null($CacheTypesToClear)) {
         $CacheTypesToClear = ['user', 'roles', 'permissions'];
     }
     if (in_array('user', $CacheTypesToClear)) {
         $UserKey = formatString(self::USERID_KEY, ['UserID' => $UserID]);
         Gdn::cache()->remove($UserKey);
     }
     if (in_array('roles', $CacheTypesToClear)) {
         $UserRolesKey = formatString(self::USERROLES_KEY, ['UserID' => $UserID]);
         Gdn::cache()->remove($UserRolesKey);
     }
     if (in_array('permissions', $CacheTypesToClear)) {
         Gdn::sql()->put('User', ['Permissions' => ''], ['UserID' => $UserID]);
         $PermissionsIncrement = $this->getPermissionsIncrement();
         $UserPermissionsKey = formatString(self::USERPERMISSIONS_KEY, ['UserID' => $UserID, 'PermissionsIncrement' => $PermissionsIncrement]);
         Gdn::cache()->remove($UserPermissionsKey);
     }
     return true;
 }