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";
}
/**
* Augment the the SQLQuery that is created by the DataQuery
* @todo Should this all go into VersionedDataQuery?
*/
function augmentSQL(SQLQuery &$query, DataQuery &$dataQuery)
{
$baseTable = ClassInfo::baseDataClass($dataQuery->dataClass());
switch ($dataQuery->getQueryParam('Versioned.mode')) {
// Noop
case '':
break;
// Reading a specific data from the archive
// Reading a specific data from the archive
case 'archive':
$date = $dataQuery->getQueryParam('Versioned.date');
foreach ($query->from as $table => $dummy) {
$query->renameTable($table, $table . '_versions');
$query->replaceText("\"{$table}\".\"ID\"", "\"{$table}\".\"RecordID\"");
// Add all <basetable>_versions columns
foreach (self::$db_for_versions_table as $name => $type) {
$query->select[] = sprintf('"%s_versions"."%s"', $baseTable, $name);
}
$query->select[] = sprintf('"%s_versions"."%s" AS "ID"', $baseTable, 'RecordID');
if ($table != $baseTable) {
$query->from[$table] .= " AND \"{$table}_versions\".\"Version\" = \"{$baseTable}_versions\".\"Version\"";
}
}
// Link to the version archived on that date
$archiveTable = $this->requireArchiveTempTable($baseTable, $date);
$query->from[$archiveTable] = "INNER JOIN \"{$archiveTable}\"\n\t\t\t\tON \"{$archiveTable}\".\"ID\" = \"{$baseTable}_versions\".\"RecordID\" \n\t\t\t\tAND \"{$archiveTable}\".\"Version\" = \"{$baseTable}_versions\".\"Version\"";
break;
// Reading a specific stage (Stage or Live)
// Reading a specific stage (Stage or Live)
case 'stage':
$stage = $dataQuery->getQueryParam('Versioned.stage');
if ($stage && $stage != $this->defaultStage) {
foreach ($query->from as $table => $dummy) {
// Only rewrite table names that are actually part of the subclass tree
// This helps prevent rewriting of other tables that get joined in, in
// particular, many_many tables
if (class_exists($table) && ($table == $this->owner->class || is_subclass_of($table, $this->owner->class) || is_subclass_of($this->owner->class, $table))) {
$query->renameTable($table, $table . '_' . $stage);
}
}
}
break;
// Return all version instances
// Return all version instances
case 'all_versions':
case 'latest_versions':
foreach ($query->from as $alias => $join) {
if ($alias != $baseTable) {
$query->setJoinFilter($alias, "\"{$alias}\".\"RecordID\" = \"{$baseTable}_versions\".\"RecordID\" AND \"{$alias}\".\"Version\" = \"{$baseTable}_versions\".\"Version\"");
}
$query->renameTable($alias, $alias . '_versions');
}
// Add all <basetable>_versions columns
foreach (self::$db_for_versions_table as $name => $type) {
$query->selectMore(sprintf('"%s_versions"."%s"', $baseTable, $name));
}
$query->selectMore(sprintf('"%s_versions"."%s" AS "ID"', $baseTable, 'RecordID'));
// latest_version has one more step
// Return latest version instances, regardless of whether they are on a particular stage
// This provides "show all, including deleted" functonality
if ($dataQuery->getQueryParam('Versioned.mode') == 'latest_versions') {
$archiveTable = self::requireArchiveTempTable($baseTable);
$query->innerJoin($archiveTable, "\"{$archiveTable}\".\"ID\" = \"{$baseTable}_versions\".\"RecordID\" AND \"{$archiveTable}\".\"Version\" = \"{$baseTable}_versions\".\"Version\"");
}
break;
default:
throw new InvalidArgumentException("Bad value for query parameter Versioned.mode: " . $dataQuery->getQueryParam('Versioned.mode'));
}
}
public function testInnerJoin()
{
$query = new SQLQuery();
$query->from('MyTable');
$query->innerJoin('MyOtherTable', 'MyOtherTable.ID = 2');
$query->leftJoin('MyLastTable', 'MyOtherTable.ID = MyLastTable.ID');
$this->assertEquals('SELECT * FROM MyTable ' . 'INNER JOIN "MyOtherTable" AS "MyOtherTable" ON MyOtherTable.ID = 2 ' . 'LEFT JOIN "MyLastTable" AS "MyLastTable" ON MyOtherTable.ID = MyLastTable.ID', $query->sql());
$query = new SQLQuery();
$query->from('MyTable');
$query->innerJoin('MyOtherTable', 'MyOtherTable.ID = 2', 'table1');
$query->leftJoin('MyLastTable', 'MyOtherTable.ID = MyLastTable.ID', 'table2');
$this->assertEquals('SELECT * FROM MyTable ' . 'INNER JOIN "MyOtherTable" AS "table1" ON MyOtherTable.ID = 2 ' . 'LEFT JOIN "MyLastTable" AS "table2" ON MyOtherTable.ID = MyLastTable.ID', $query->sql());
}
/**
* Traverse the relationship fields, and add the table
* mappings to the query object state.
*
* @todo try to make this implicitly triggered so it doesn't have to be manually called in child filters
* @param SQLQuery $query
* @return SQLQuery
*/
function applyRelation($query)
{
if (is_array($this->relation)) {
foreach ($this->relation as $rel) {
$model = singleton($this->model);
if ($component = $model->has_one($rel)) {
if (!$query->isJoinedTo($component)) {
$foreignKey = $model->getReverseAssociation($component);
$query->leftJoin($component, "`{$component}`.`ID` = `{$this->model}`.`{$foreignKey}ID`");
}
$this->model = $component;
} elseif ($component = $model->has_many($rel)) {
if (!$query->isJoinedTo($component)) {
$ancestry = $model->getClassAncestry();
$foreignKey = $model->getComponentJoinField($rel);
$query->leftJoin($component, "`{$component}`.`{$foreignKey}` = `{$ancestry[0]}`.`ID`");
}
$this->model = $component;
} elseif ($component = $model->many_many($rel)) {
list($parentClass, $componentClass, $parentField, $componentField, $relationTable) = $component;
$parentBaseClass = ClassInfo::baseDataClass($parentClass);
$componentBaseClass = ClassInfo::baseDataClass($componentClass);
$query->innerJoin($relationTable, "`{$relationTable}`.`{$parentField}` = `{$parentBaseClass}`.`ID`");
$query->leftJoin($componentClass, "`{$relationTable}`.`{$componentField}` = `{$componentClass}`.`ID`");
$this->model = $componentClass;
// Experimental support for user-defined relationships via a "(relName)Query" method
// This will likely be dropped in 2.4 for a system that makes use of Lazy Data Lists.
} elseif ($model->hasMethod($rel . 'Query')) {
// Get the query representing the join - it should have "$ID" in the filter
$newQuery = $model->{"{$rel}Query"}();
if ($newQuery) {
// Get the table to join to
$newModel = str_replace('`', '', array_shift($newQuery->from));
// Get the filter to use on the join
$ancestry = $model->getClassAncestry();
$newFilter = "(" . str_replace('$ID', "`{$ancestry[0]}`.`ID`", implode(") AND (", $newQuery->where)) . ")";
$query->leftJoin($newModel, $newFilter);
$this->model = $newModel;
} else {
$this->name = "NULL";
return;
}
}
}
}
return $query;
}
/**
* Traverse the relationship fields, and add the table
* mappings to the query object state. This has to be called
* in any overloaded {@link SearchFilter->apply()} methods manually.
*
* @param $relation The array/dot-syntax relation to follow
* @return The model class of the related item
*/
function applyRelation($relation) {
// NO-OP
if(!$relation) return $this->dataClass;
if(is_string($relation)) $relation = explode(".", $relation);
$modelClass = $this->dataClass;
foreach($relation as $rel) {
$model = singleton($modelClass);
if ($component = $model->has_one($rel)) {
if(!$this->query->isJoinedTo($component)) {
$foreignKey = $model->getReverseAssociation($component);
$this->query->leftJoin($component, "\"$component\".\"ID\" = \"{$modelClass}\".\"{$foreignKey}ID\"");
/**
* add join clause to the component's ancestry classes so that the search filter could search on its
* ancester fields.
*/
$ancestry = ClassInfo::ancestry($component, true);
if(!empty($ancestry)){
$ancestry = array_reverse($ancestry);
foreach($ancestry as $ancestor){
if($ancestor != $component){
$this->query->innerJoin($ancestor, "\"$component\".\"ID\" = \"$ancestor\".\"ID\"");
$component=$ancestor;
}
}
}
}
$modelClass = $component;
} elseif ($component = $model->has_many($rel)) {
if(!$this->query->isJoinedTo($component)) {
$ancestry = $model->getClassAncestry();
$foreignKey = $model->getRemoteJoinField($rel);
$this->query->leftJoin($component, "\"$component\".\"{$foreignKey}\" = \"{$ancestry[0]}\".\"ID\"");
/**
* add join clause to the component's ancestry classes so that the search filter could search on its
* ancestor fields.
*/
$ancestry = ClassInfo::ancestry($component, true);
if(!empty($ancestry)){
$ancestry = array_reverse($ancestry);
foreach($ancestry as $ancestor){
if($ancestor != $component){
$this->query->innerJoin($ancestor, "\"$component\".\"ID\" = \"$ancestor\".\"ID\"");
$component=$ancestor;
}
}
}
}
$modelClass = $component;
} elseif ($component = $model->many_many($rel)) {
list($parentClass, $componentClass, $parentField, $componentField, $relationTable) = $component;
$parentBaseClass = ClassInfo::baseDataClass($parentClass);
$componentBaseClass = ClassInfo::baseDataClass($componentClass);
$this->query->innerJoin($relationTable, "\"$relationTable\".\"$parentField\" = \"$parentBaseClass\".\"ID\"");
$this->query->leftJoin($componentBaseClass, "\"$relationTable\".\"$componentField\" = \"$componentBaseClass\".\"ID\"");
if(ClassInfo::hasTable($componentClass)) {
$this->query->leftJoin($componentClass, "\"$relationTable\".\"$componentField\" = \"$componentClass\".\"ID\"");
}
$modelClass = $componentClass;
}
}
return $modelClass;
}
/**
* Traverse the relationship fields, and add the table
* mappings to the query object state. This has to be called
* in any overloaded {@link SearchFilter->apply()} methods manually.
*
* @todo try to make this implicitly triggered so it doesn't have to be manually called in child filters
* @param SQLQuery $query
* @return SQLQuery
*/
function applyRelation($query)
{
if (is_array($this->relation)) {
foreach ($this->relation as $rel) {
$model = singleton($this->model);
if ($component = $model->has_one($rel)) {
if (!$query->isJoinedTo($component)) {
$foreignKey = $model->getReverseAssociation($component);
$query->leftJoin($component, "\"{$component}\".\"ID\" = \"{$this->model}\".\"{$foreignKey}ID\"");
/**
* add join clause to the component's ancestry classes so that the search filter could search on its
* ancester fields.
*/
$ancestry = ClassInfo::ancestry($component, true);
if (!empty($ancestry)) {
$ancestry = array_reverse($ancestry);
foreach ($ancestry as $ancestor) {
if ($ancestor != $component) {
$query->innerJoin($ancestor, "\"{$component}\".\"ID\" = \"{$ancestor}\".\"ID\"");
$component = $ancestor;
}
}
}
}
$this->model = $component;
} elseif ($component = $model->has_many($rel)) {
if (!$query->isJoinedTo($component)) {
$ancestry = $model->getClassAncestry();
$foreignKey = $model->getRemoteJoinField($rel);
$query->leftJoin($component, "\"{$component}\".\"{$foreignKey}\" = \"{$ancestry[0]}\".\"ID\"");
/**
* add join clause to the component's ancestry classes so that the search filter could search on its
* ancestor fields.
*/
$ancestry = ClassInfo::ancestry($component, true);
if (!empty($ancestry)) {
$ancestry = array_reverse($ancestry);
foreach ($ancestry as $ancestor) {
if ($ancestor != $component) {
$query->innerJoin($ancestor, "\"{$component}\".\"ID\" = \"{$ancestor}\".\"ID\"");
$component = $ancestor;
}
}
}
}
$this->model = $component;
} elseif ($component = $model->many_many($rel)) {
list($parentClass, $componentClass, $parentField, $componentField, $relationTable) = $component;
$parentBaseClass = ClassInfo::baseDataClass($parentClass);
$componentBaseClass = ClassInfo::baseDataClass($componentClass);
$query->innerJoin($relationTable, "\"{$relationTable}\".\"{$parentField}\" = \"{$parentBaseClass}\".\"ID\"");
$query->leftJoin($componentBaseClass, "\"{$relationTable}\".\"{$componentField}\" = \"{$componentBaseClass}\".\"ID\"");
if (ClassInfo::hasTable($componentClass)) {
$query->leftJoin($componentClass, "\"{$relationTable}\".\"{$componentField}\" = \"{$componentClass}\".\"ID\"");
}
$this->model = $componentClass;
// Experimental support for user-defined relationships via a "(relName)Query" method
// This will likely be dropped in 2.4 for a system that makes use of Lazy Data Lists.
} elseif ($model->hasMethod($rel . 'Query')) {
// Get the query representing the join - it should have "$ID" in the filter
$newQuery = $model->{"{$rel}Query"}();
if ($newQuery) {
// Get the table to join to
//DATABASE ABSTRACTION: I don't think we need this line anymore:
$newModel = str_replace('`', '', array_shift($newQuery->from));
// Get the filter to use on the join
$ancestry = $model->getClassAncestry();
$newFilter = "(" . str_replace('$ID', "\"{$ancestry[0]}\".\"ID\"", implode(") AND (", $newQuery->where)) . ")";
$query->leftJoin($newModel, $newFilter);
$this->model = $newModel;
} else {
$this->name = "NULL";
return;
}
}
}
}
return $query;
}
