PHP SQLSelect::addInnerJoin方法代码示例

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