Yii2 使用 Joins 查询

关联 Model

$model = User::find()        ->with('comments')        ->all();    foreach ($model as $user) {        // get data from relation model        $comments = $user->comments;        ......        foreach($comments as $comment){            ........        }    }
 // //实例化查询类        $query=new \yii\db\Query();        $query->select(['topiczk1.t_id','topiczk1.title','topiczk1.content','topiczk1.file','topiczk1.add_time'])        ->from('topiczk1')        ->leftJoin('loginzk1','topiczk1.lid=loginzk1.l_id');         $command = $query->createCommand();         $data = $command->queryAll();
 

joinWith()

方法一

$model = User::find()    ->joinWith('comments')    ->all();

方法二

$model = User::find()    ->joinWith('comments')    ->orderBy('tbl_comments_id.id, tbl_user.id')    ->all();

innerJoinWith()

$model = User::find()    ->innerJoinWith('comments', false)    ->all();// equivalent to the above$model = User::find()    ->joinWith('comments', false, 'INNER JOIN')    ->all();

Join()

JOIN_TYPE = INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN 等等

语法

$query = new Query;$query  ->select(['SELECT COLUMNS'])          ->from('TABLE_NAME_1')        ->join( 'JOIN_TYPE',                 'TABLE_NAME_2',                'TABLE_NAME_2.COLUMN =TABLE_NAME_1.COLUMN'            ); $command = $query->createCommand();$data = $command->queryAll();

示例一

$query = new Query;$query  ->select([        'tbl_user.username AS name',         'tbl_category.categoryname as  Category',        'tbl_document.documentname']        )      ->from('tbl_user')    ->join('LEFT OUTER JOIN', 'tbl_category',                'tbl_category.createdby =tbl_user.userid')          ->join('LEFT OUTER JOIN', 'tbl_document',                 'tbl_category.cid =tbl_document.did')    ->LIMIT(5)  ;         $command = $query->createCommand();$data = $command->queryAll();   

输出语句

SELECT `tbl_user`.`username` AS `name`, `tbl_category`.`categoryname` AS `Category` FROM `tbl_user` LEFT OUTER JOIN `tbl_category` ON tbl_category.createdby =tbl_user.userid LEFT OUTER JOIN `tbl_document` ON tbl_category.cid =tbl_document.did LIMIT 5 

leftJoin()

示例一

$query = new Query;$query  ->select(['tbl_user.username AS name', 'tbl_category.type as Category'])          ->from('tbl_user')        ->leftJoin('tbl_category', 'tbl_category.createdby = tbl_user.userid')        ->limit(2); $command = $query->createCommand();$data = $command->queryAll();

输出语句

SELECT `tbl_user`.`username` AS `name`, `tbl_category`.`type` AS `Category`    FROM `tbl_user` LEFT JOIN `tbl_category`     ON tbl_category.createdby = tbl_user.useridd     LIMIT 2