关联 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