Yii2 使用 Join 查询

翻译 · forecho · 于 6年前 发布 · 23416 次阅读

原文:Yii2 Select Query : Joins

关联 Model

$model = User::find()
        ->with('comments')
        ->all();
    foreach ($model as $user) {
        // get data from relation model
        $comments = $user->comments;
        ......
        foreach($comments as $comment){
            ........
        }
    }

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  
本帖已被设为精华帖!
共收到 1 条回复 Bsourcecode
linfeng#14年前 0 个赞

请问 多个where查询 怎么写的

添加回复 (需要登录)
需要 登录 后方可回复, 如果你还没有账号请点击这里 注册