Yii Query

Yii Framework 2.0 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		
  • Dario Trbović

    This is very helpful 🙂

  • Lowal

    yawa

  • Naushil Jain

    Very Usefull. 🙂

  • Thanks for this useful post.

  • how about fetching limited fields from related tables using joinWith.

    e.g.:

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

    Above code will return all fields of User and all fields of comments in proper format. But what if we want to fetch only limited fields of comments tables with the same format?

    • Almir Bolduan

      I have the same question…

  • Bye Webster

    how do i join 3 table.. ?

  • Nur Chalik Azhar

    I have case. And I have two tables that relate to each other one to one that is Husband and Wife. I’ve made my value dropdownlist and to get the data from the table Husband. I want when its value had been chosen will not appear on his dropdownlist. I’ve tried but not successfully. For my code can see this bellow.
    https://uploads.disquscdn.com/images/14b819c0f4cb51c556110859fcfafd42c1ea28c92e0d4486d191bcd4147c5aec.jpg

  • Basher Morshed

    This is very useful post. Just add if use for bulk database process going with nolock . here is example:
    $rows = (new yiidbQuery())
    ->select([‘ s.* ‘, ‘p.*’ ])
    ->from(‘table1 s ‘)
    ->join(‘sites’, ‘table2 p with (nolock)’, ‘s.id = p.parentid)
    ->where([ s.sitename => ‘Dallas’])
    ->all();

  • Mohammad Yusuf

    I don’t like new website style. I Love previous design.