Yii2 使用 Joins 查询

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