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;
}
