/**
* Join table via many_many relationship
*
* @param string $parentClass
* @param string $componentClass
* @param string $parentField
* @param string $componentField
* @param string $relationTable Name of relation table
*/
protected function joinManyManyRelationship($parentClass, $componentClass, $parentField, $componentField, $relationTable)
{
$parentBaseClass = ClassInfo::baseDataClass($parentClass);
$componentBaseClass = ClassInfo::baseDataClass($componentClass);
$this->query->addLeftJoin($relationTable, "\"{$relationTable}\".\"{$parentField}\" = \"{$parentBaseClass}\".\"ID\"");
$this->query->addLeftJoin($componentBaseClass, "\"{$relationTable}\".\"{$componentField}\" = \"{$componentBaseClass}\".\"ID\"");
/**
* add join clause to the component's ancestry classes so that the search filter could search on
* its ancestor fields.
*/
$ancestry = ClassInfo::ancestry($componentClass, true);
$ancestry = array_reverse($ancestry);
foreach ($ancestry as $ancestor) {
if ($ancestor != $componentBaseClass) {
$this->query->addInnerJoin($ancestor, "\"{$componentBaseClass}\".\"ID\" = \"{$ancestor}\".\"ID\"");
}
}
}
public function testParameterisedInnerJoins()
{
$query = new SQLSelect();
$query->setSelect(array('"SQLSelectTest_DO"."Name"', '"SubSelect"."Count"'));
$query->setFrom('"SQLSelectTest_DO"');
$query->addInnerJoin('(SELECT "Title", COUNT(*) AS "Count" FROM "SQLSelectTestBase" GROUP BY "Title" HAVING "Title" NOT LIKE ?)', '"SQLSelectTest_DO"."Name" = "SubSelect"."Title"', 'SubSelect', 20, array('%MyName%'));
$query->addWhere(array('"SQLSelectTest_DO"."Date" > ?' => '2012-08-08 12:00'));
$this->assertSQLEquals('SELECT "SQLSelectTest_DO"."Name", "SubSelect"."Count"
FROM "SQLSelectTest_DO" INNER JOIN (SELECT "Title", COUNT(*) AS "Count" FROM "SQLSelectTestBase"
GROUP BY "Title" HAVING "Title" NOT LIKE ?) AS "SubSelect" ON "SQLSelectTest_DO"."Name" =
"SubSelect"."Title"
WHERE ("SQLSelectTest_DO"."Date" > ?)', $query->sql($parameters));
$this->assertEquals(array('%MyName%', '2012-08-08 12:00'), $parameters);
$query->execute();
}
/**
* 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 String|array $relation The array/dot-syntax relation to follow
* @return The model class of the related item
*/
public 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->addLeftJoin($component, "\"{$component}\".\"ID\" = \"{$modelClass}\".\"{$foreignKey}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->addInnerJoin($ancestor, "\"{$component}\".\"ID\" = \"{$ancestor}\".\"ID\"");
}
}
}
}
$modelClass = $component;
} elseif ($component = $model->has_many($rel)) {
if (!$this->query->isJoinedTo($component)) {
$ancestry = $model->getClassAncestry();
$foreignKey = $model->getRemoteJoinField($rel);
$this->query->addLeftJoin($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->addInnerJoin($ancestor, "\"{$component}\".\"ID\" = \"{$ancestor}\".\"ID\"");
}
}
}
}
$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->addInnerJoin($relationTable, "\"{$relationTable}\".\"{$parentField}\" = \"{$parentBaseClass}\".\"ID\"");
$this->query->addLeftJoin($componentBaseClass, "\"{$relationTable}\".\"{$componentField}\" = \"{$componentBaseClass}\".\"ID\"");
if (ClassInfo::hasTable($componentClass)) {
$this->query->addLeftJoin($componentClass, "\"{$relationTable}\".\"{$componentField}\" = \"{$componentClass}\".\"ID\"");
}
$modelClass = $componentClass;
}
}
return $modelClass;
}
public function testInnerJoin()
{
$query = new SQLSelect();
$query->setFrom('MyTable');
$query->addInnerJoin('MyOtherTable', 'MyOtherTable.ID = 2');
$query->addLeftJoin('MyLastTable', 'MyOtherTable.ID = MyLastTable.ID');
$this->assertSQLEquals('SELECT * FROM MyTable ' . 'INNER JOIN "MyOtherTable" ON MyOtherTable.ID = 2 ' . 'LEFT JOIN "MyLastTable" ON MyOtherTable.ID = MyLastTable.ID', $query->sql($parameters));
$query = new SQLSelect();
$query->setFrom('MyTable');
$query->addInnerJoin('MyOtherTable', 'MyOtherTable.ID = 2', 'table1');
$query->addLeftJoin('MyLastTable', 'MyOtherTable.ID = MyLastTable.ID', 'table2');
$this->assertSQLEquals('SELECT * FROM MyTable ' . 'INNER JOIN "MyOtherTable" AS "table1" ON MyOtherTable.ID = 2 ' . 'LEFT JOIN "MyLastTable" AS "table2" ON MyOtherTable.ID = MyLastTable.ID', $query->sql($parameters));
}
/**
* Are new posts available?
*
* @param int $id
* @param array $data Optional: If an array is passed, the timestamp of
* the last created post and it's ID will be stored in
* it (keys: 'last_id', 'last_created')
* @param int $lastVisit Unix timestamp of the last visit (GMT)
* @param int $lastPostID ID of the last read post
* @param int $thread ID of the relevant topic (set to NULL for all
* topics)
* @return bool Returns TRUE if there are new posts available, otherwise
* FALSE.
*/
public static function new_posts_available($id, &$data = array(), $lastVisit = null, $lastPostID = null, $forumID = null, $threadID = null)
{
// last post viewed
$query = new SQLSelect(array('LastID' => 'MAX("Post"."ID")', 'LastCreated' => 'MAX("Post"."Created")'), '"Post"', array('"ForumPage"."ParentID" = ?' => $id));
$query->addInnerJoin(ForumHolder::baseForumTable(), '"Post"."ForumID" = "ForumPage"."ID"', 'ForumPage');
// Filter by parameters specified
if ($lastPostID) {
$query->addWhere(array('"Post"."ID" > ?' => $lastPostID));
}
if ($lastVisit) {
$query->addWhere(array('"Post"."Created" > ?' => $lastVisit));
}
if ($forumID) {
$query->addWhere(array('"Post"."ForumID" = ?' => $forumID));
}
if ($threadID) {
$query->addWhere(array('"Post"."ThreadID" = ?' => $threadID));
}
// Run
$version = $query->execute()->first();
if (!$version) {
return false;
}
if ($data) {
$data['last_id'] = (int) $version['LastID'];
$data['last_created'] = strtotime($version['LastCreated']);
}
$lastVisit = (int) $lastVisit;
if ($lastVisit <= 0) {
$lastVisit = false;
}
$lastPostID = (int) $lastPostID;
if ($lastPostID <= 0) {
$lastPostID = false;
}
if (!$lastVisit && !$lastPostID) {
return true;
}
if ($lastVisit && strtotime($version['LastCreated']) > $lastVisit) {
return true;
}
if ($lastPostID && (int) $version['LastID'] > $lastPostID) {
return true;
}
return false;
}
