function testSelectWithChainedFilterParameters() { $query = new SQLQuery(); $query->select(array("Name", "Meta"))->from("MyTable"); $query->where("Name = 'Name'")->where("Meta", "Test")->where("Beta", "!=", "Gamma"); $this->assertEquals("SELECT Name, Meta FROM MyTable WHERE (Name = 'Name') AND (Meta = 'Test') AND (Beta != 'Gamma')", $query->sql()); }
/** * Selects record based on WHERE Clause. * * @param array $where Parameters to use as WHERE clause condition * @param array [$fields = array('*')] fields to select from the query * * @return array of calling class data type */ public static function selectWhere($where, $fields = array('*')) { //Initialize parameters static::initialize(); //Run query $result = SQLQuery::select(static::$tableName, static::$classNS, static::$DBH, $fields, $where); //Return result return $result; }
function get_user($user_id) { $DB = new SQLQuery(); $DB->chooseTable(DB_USERS_TABLE); $user = $DB->select($user_id); if (empty($user)) { return NULL; } else { return $user[0]["users"]; } }
function testSelectWithPredicateFilters() { $query = new SQLQuery(); $query->select(array("Name"))->from("SQLQueryTest_DO"); $match = new ExactMatchFilter("Name", "Value"); $match->setModel('SQLQueryTest_DO'); $match->apply($query); $match = new PartialMatchFilter("Meta", "Value"); $match->setModel('SQLQueryTest_DO'); $match->apply($query); $this->assertEquals("SELECT Name FROM SQLQueryTest_DO WHERE (`SQLQueryTest_DO`.`Name` = 'Value') AND (`SQLQueryTest_DO`.`Meta` LIKE '%Value%')", $query->sql()); }
protected function purgeUnconfirmedRegistrations() { $query = new SQLQuery(); $conn = DB::getConn(); $query->select('"EventRegistration"."ID"'); $query->from('"EventRegistration"'); $query->innerJoin('CalendarDateTime', '"TimeID" = "DateTime"."ID"', 'DateTime'); $query->innerJoin('CalendarEvent', '"DateTime"."EventID" = "Event"."ID"', 'Event'); $query->innerJoin('RegisterableEvent', '"Event"."ID" = "Registerable"."ID"', 'Registerable'); $query->where('"Registerable"."ConfirmTimeLimit" > 0'); $query->where('"Status"', 'Unconfirmed'); $created = $conn->formattedDatetimeClause('"EventRegistration"."Created"', '%U'); $query->where(sprintf('%s < %s', $created . ' + "Registerable"."ConfirmTimeLimit"', time())); if ($ids = $query->execute()->column()) { $count = count($ids); DB::query(sprintf('UPDATE "EventRegistration" SET "Status" = \'Canceled\' WHERE "ID" IN (%s)', implode(', ', $ids))); } else { $count = 0; } echo "{$count} unconfirmed registrations were canceled.\n"; }
function testDbDatetimeDifference() { if ($this->supportDbDatetime) { $clause = $this->adapter->datetimeDifferenceClause('1974-10-14 10:30:00', '1973-10-14 10:30:00'); $result = DB::query('SELECT ' . $clause)->value(); $this->matchesRoughly($result / 86400, 365, '1974 - 1973 = 365 * 86400 sec'); $clause = $this->adapter->datetimeDifferenceClause(date('Y-m-d H:i:s', strtotime('-15 seconds')), 'now'); $result = DB::query('SELECT ' . $clause)->value(); $this->matchesRoughly($result, -15, '15 seconds ago - now'); $clause = $this->adapter->datetimeDifferenceClause('now', $this->adapter->datetimeIntervalClause('now', '+45 Minutes')); $result = DB::query('SELECT ' . $clause)->value(); $this->matchesRoughly($result, -45 * 60, 'now - 45 minutes ahead'); $query = new SQLQuery(); $query->select($this->adapter->datetimeDifferenceClause('"LastEdited"', '"Created"') . ' AS "test"')->from('"DbDateTimeTest_Team"')->limit(1); $result = $query->execute()->value(); $lastedited = Dataobject::get_one('DbDateTimeTest_Team')->LastEdited; $created = Dataobject::get_one('DbDateTimeTest_Team')->Created; $this->matchesRoughly($result, strtotime($lastedited) - strtotime($created), 'age of HomePage record in seconds since unix epoc'); } }
/** * Return the number of rows in this query if the limit were removed. Useful in paged data sets. * @return int */ public function unlimitedRowCount($column = null) { // we can't clear the select if we're relying on its output by a HAVING clause if (count($this->having)) { $records = $this->execute(); return $records->numRecords(); } $clone = clone $this; $clone->limit = null; $clone->orderby = null; // Choose a default column if ($column == null) { if ($this->groupby) { $countQuery = new SQLQuery(); $countQuery->select("count(*)"); $countQuery->from = array('(' . $clone->sql() . ') all_distinct'); return $countQuery->execute()->value(); } else { $clone->setSelect(array("count(*)")); } } else { $clone->setSelect(array("count({$column})")); } $clone->setGroupBy(array()); return $clone->execute()->value(); }
/** * Get the latest members * * @param int $limit Number of members to return * @return DataObjectSet */ function getLatestMembers($limit = 1) { $filter = ''; if ($forumGroup = DataObject::get_one('Group', "\"Code\" = 'forum-members'")) { $filter = "\"GroupID\" = '" . $forumGroup->ID . "' "; } if ($adminGroup = DataObject::get_one('Group', "(\"Code\" = 'administrators' OR \"Code\" = 'Administrators')")) { $filter .= $filter ? "OR \"GroupID\" = '" . $adminGroup->ID . "'" : "\"GroupID\" = '" . $adminGroup->ID . "'"; } // do a lookup on the specific Group_Members table for the latest member ID if ($filter) { $limit = (int) $limit; $query = new SQLQuery(); $query->select('"MemberID"')->from('"Group_Members"')->where($filter)->orderby('"ID" DESC')->limit($limit); $latestMemberIDs = $query->execute()->column(); if ($latestMemberIDs) { $members = new DataObjectSet(); foreach ($latestMemberIDs as $key => $id) { $members->push(DataObject::get_by_id('Member', $id)); } return $members; } } return DataObject::get("Member", "", "ID DESC", "", $limit); }
/** * Returns the number of tickets available for an event time. * * @param RegisterableDateTime $time * @param int $excludeId A registration ID to exclude from calculations. * @return array */ public function getAvailableForDateTime(RegisterableDateTime $time, $excludeId = null) { if ($this->StartType == 'Date') { $start = strtotime($this->StartDate); } else { $start = $time->getStartTimestamp(); $start = sfTime::subtract($start, $this->StartDays, sfTime::DAY); $start = sfTime::subtract($start, $this->StartHours, sfTime::HOUR); $start = sfTime::subtract($start, $this->StartMins, sfTime::MINUTE); } if ($start >= time()) { return array('available' => false, 'reason' => 'Tickets are not yet available.', 'available_at' => $start); } if ($this->EndType == 'Date') { $end = strtotime($this->EndDate); } else { $end = $time->getStartTimestamp(); $end = sfTime::subtract($end, $this->EndDays, sfTime::DAY); $end = sfTime::subtract($end, $this->EndHours, sfTime::HOUR); $end = sfTime::subtract($end, $this->EndMins, sfTime::MINUTE); } if (time() >= $end) { return array('available' => false, 'reason' => 'Tickets are no longer available.'); } if (!($quantity = $this->Available)) { return array('available' => true); } $booked = new SQLQuery(); $booked->select('SUM("Quantity")'); $booked->from('"EventRegistration_Tickets"'); $booked->leftJoin('EventRegistration', '"EventRegistration"."ID" = "EventRegistrationID"'); if ($excludeId) { $booked->where('"EventRegistration"."ID"', '<>', $excludeId); } $booked->where('"Status"', '<>', 'Canceled'); $booked->where('"EventTicketID"', $this->ID); $booked->where('"EventRegistration"."TimeID"', $time->ID); $booked = $booked->execute()->value(); if ($booked < $quantity) { return array('available' => $quantity - $booked); } else { return array('available' => false, 'reason' => 'All tickets have been booked.'); } }
/** * Retun an array of maps containing the keys, 'ID' and 'ParentID' for each page to be displayed * in the search. * * @return Array */ function pagesIncluded() { $ids = array(); $q = new SQLQuery(); $q->select(array('"ID"','"ParentID"')) ->from('"SiteTree"'); $where = array(); $SQL_params = Convert::raw2sql($this->params); foreach($SQL_params as $name => $val) { switch($name) { // Match against URLSegment, Title, MenuTitle & Content case 'Term': if($val) $where[] = "\"URLSegment\" LIKE '%$val%' OR \"Title\" LIKE '%$val%' OR \"MenuTitle\" LIKE '%$val%' OR \"Content\" LIKE '%$val%'"; break; // Match against date case 'LastEditedFrom': if($val) $where[] = "\"LastEdited\" >= '$val'"; break; case 'LastEditedTo': if($val) $where[] = "\"LastEdited\" <= '$val'"; break; // Match against exact ClassName case 'ClassName': if($val && $val != 'All') { $where[] = "\"ClassName\" = '$val'"; } break; default: // Partial string match against a variety of fields if(!empty($val) && singleton("SiteTree")->hasDatabaseField($name)) { $where[] = "\"$name\" LIKE '%$val%'"; } } } $q->where(empty($where) ? '' : '(' . implode(') AND (',$where) . ')'); foreach($q->execute() as $row) { $ids[] = array('ID'=>$row['ID'],'ParentID'=>$row['ParentID']); } return $ids; }
/** * Returns the overall number of places remaining at this event, TRUE if * there are unlimited places or FALSE if they are all taken. * * @param int $excludeId A registration ID to exclude from calculations. * @return int|bool */ public function getRemainingCapacity($excludeId = null) { if (!$this->Capacity) { return true; } $taken = new SQLQuery(); $taken->select('SUM("Quantity")'); $taken->from('EventRegistration_Tickets'); $taken->leftJoin('EventRegistration', '"EventRegistration"."ID" = "EventRegistrationID"'); if ($excludeId) { $taken->where('"EventRegistration"."ID"', '<>', $excludeId); } $taken->where('"Status"', '<>', 'Canceled'); $taken->where('"EventRegistration"."TimeID"', $this->ID); $taken = $taken->execute()->value(); return $this->Capacity >= $taken ? $this->Capacity - $taken : false; }