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()); }
public function apply(SQLQuery $query) { return $query->where(sprintf( "%s != '%s'", $this->getDbName(), Convert::raw2sql($this->getValue()) )); }
function apply(SQLQuery $query) { $query->where(sprintf( "%s >= '%s' AND %s < '%s'", $this->getDbName(), Convert::raw2sql($this->min), $this->getDbName(), Convert::raw2sql($this->max) )); }
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"; }
/** * Build a {@link SQLQuery} object to perform the given query. * * @param string $filter A filter to be inserted into the WHERE clause. * @param string|array $sort A sort expression to be inserted into the ORDER BY clause. If omitted, self::$default_sort will be used. * @param string|array $limit A limit expression to be inserted into the LIMIT clause. * @param string $join A single join clause. This can be used for filtering, only 1 instance of each DataObject will be returned. * @param boolean $restictClasses Restrict results to only objects of either this class of a subclass of this class * @param string $having A filter to be inserted into the HAVING clause. * * @return SQLQuery Query built. */ public function buildSQL($filter = "", $sort = "", $limit = "", $join = "", $restrictClasses = true, $having = "") { // Find a default sort if(!$sort) { $sort = $this->stat('default_sort'); } // Get the tables to join to $tableClasses = ClassInfo::dataClassesFor($this->class); if(!$tableClasses) { if(!ManifestBuilder::has_been_included()) { user_error("DataObjects have been requested before the manifest is loaded. Please ensure you are not querying the database in _config.php.", E_USER_ERROR); } else { user_error("DataObject::buildSQL: Can't find data classes (classes linked to tables) for $this->class. Please ensure you run dev/build after creating a new DataObject.", E_USER_ERROR); } } $baseClass = array_shift($tableClasses); $select = array("`$baseClass`.*"); // Build our intial query $query = new SQLQuery($select); $query->from("`$baseClass`"); $query->where($filter); $query->orderby($sort); $query->limit($limit); // Add SQL for multi-value fields on the base table $databaseFields = $this->databaseFields(); if($databaseFields) foreach($databaseFields as $k => $v) { if(!in_array($k, array('ClassName', 'LastEdited', 'Created'))) { if(ClassInfo::classImplements($v, 'CompositeDBField')) { $this->dbObject($k)->addToQuery($query); } } } // Join all the tables if($tableClasses && self::$subclass_access) { foreach($tableClasses as $tableClass) { $query->from[$tableClass] = "LEFT JOIN `$tableClass` ON `$tableClass`.ID = `$baseClass`.ID"; $query->select[] = "`$tableClass`.*"; // Add SQL for multi-value fields $SNG = singleton($tableClass); $databaseFields = $SNG->databaseFields(); if($databaseFields) foreach($databaseFields as $k => $v) { if(!in_array($k, array('ClassName', 'LastEdited', 'Created'))) { if(ClassInfo::classImplements($v, 'CompositeDBField')) { $SNG->dbObject($k)->addToQuery($query); } } } } } $query->select[] = "`$baseClass`.ID"; $query->select[] = "if(`$baseClass`.ClassName,`$baseClass`.ClassName,'$baseClass') AS RecordClassName"; // Get the ClassName values to filter to $classNames = ClassInfo::subclassesFor($this->class); if(!$classNames) { user_error("DataObject::get() Can't find data sub-classes for '$callerClass'"); } // If querying the base class, don't bother filtering on class name if($restrictClasses && $this->class != $baseClass) { // Get the ClassName values to filter to $classNames = ClassInfo::subclassesFor($this->class); if(!$classNames) { user_error("DataObject::get() Can't find data sub-classes for '$callerClass'"); } $query->where[] = "`$baseClass`.ClassName IN ('" . implode("','", $classNames) . "')"; } if($having) { $query->having[] = $having; } if($join) { $query->from[] = $join; $query->groupby[] = reset($query->from) . ".ID"; } return $query; }
public function apply(SQLQuery $query) { $query->where(sprintf("MATCH (%s AGAINST ('%s')", $this->getDbName(), Convert::raw2sql($this->getValue()))); return $query; }
/** * Delete this data object. * $this->onBeforeDelete() gets called. * Note that in Versioned objects, both Stage and Live will be deleted. * @uses DataExtension->augmentSQL() */ public function delete() { $this->brokenOnDelete = true; $this->onBeforeDelete(); if($this->brokenOnDelete) { user_error("$this->class has a broken onBeforeDelete() function. Make sure that you call parent::onBeforeDelete().", E_USER_ERROR); } // Deleting a record without an ID shouldn't do anything if(!$this->ID) throw new Exception("DataObject::delete() called on a DataObject without an ID"); // TODO: This is quite ugly. To improve: // - move the details of the delete code in the DataQuery system // - update the code to just delete the base table, and rely on cascading deletes in the DB to do the rest // obviously, that means getting requireTable() to configure cascading deletes ;-) $srcQuery = DataList::create($this->class, $this->model)->where("ID = $this->ID")->dataQuery()->query(); foreach($srcQuery->queriedTables() as $table) { $query = new SQLQuery("*", array('"'.$table.'"')); $query->where("\"ID\" = $this->ID"); $query->delete = true; $query->execute(); } // Remove this item out of any caches $this->flushCache(); $this->onAfterDelete(); $this->OldID = $this->ID; $this->ID = 0; }
public function apply(SQLQuery $query) { return $query->where(sprintf("LOCATE('%s', %s) != 0", Convert::raw2sql($this->getValue()), $this->getDbName())); }
/** * Remove all items from this many-many join that match the given filter * @deprecated this is experimental and will change. Don't use it in your projects. */ function removeByFilter($filter) { $query = new SQLQuery("*", array("\"{$this->joinTable}\"")); $query->delete = true; $query->where($filter); $query->execute(); }
/** * 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; }
/** * Delete the object. $version is either the version number, or "all" to completely delete the object and all versions. The two cases work * as follows: * - if a specified version is being deleted, we remove it from the versions table for the object, and invoke the recalculateStages handler which * will perform all reconcilation of the live and staged records for the object. * - if all versions are being deleted, we remove all versions from the versions table, and again invoke recalculateStages. * FIX: this works on the assumption that the moderated class extends directly from DataObject. */ public function deleteVersioned($version) { $baseTable = ClassInfo::baseDataClass($this->owner->class); $q = new SQLQuery(); $q->from("{$baseTable}_versions"); $q->where("RecordID = " . $this->owner->ID); if ($version != "all") { $q->where("Version=" . $version); } $q->delete = true; $q->execute(); $this->recalculateStages(); }
/** * Remove the given item from this list. * Note that for a ManyManyList, the item is never actually deleted, only the join table is affected * @param $itemID The item it */ function removeByID($itemID) { if(!is_numeric($itemID)) throw new InvalidArgumentException("ManyManyList::removeById() expecting an ID"); $query = new SQLQuery("*", array("\"$this->joinTable\"")); $query->delete = true; if($filter = $this->foreignIDFilter()) { $query->where($filter); } else { user_error("Can't call ManyManyList::remove() until a foreign ID is set", E_USER_WARNING); } $query->where("\"$this->localKey\" = {$itemID}"); $query->execute(); }
/** * 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; }