Yii2 Select Query : Joins

Relation Model

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

joinWith()

Sample 1:

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

Sample 2:

$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 etc

SYNTAX

$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();				

Sample 1:

$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();	

OUTPUT Query

//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()

Sample 1:

$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();

OUTPUT Query

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