Yii 2.0 joinWith()
In this article I want to talk about joining two tables in Yii 2.0. How to join two tables on a one-to-one relation?
In this post I want to show you, an easy way of joining two tables using relations in Yii 2.0 As an example, we have two tables. One is called 'Person' and the other is called 'User'. 'Person' attributes are, 'pid, name, type' and our primary key is 'pid'. 'User' attributes are 'pid, username, password' and our primary key is 'pid' which is also a foreign key to 'Person'. Now we want to join these two tables. Our standard SQL Query would be,
"Select * From 'Person' LEFT JOIN 'User' on 'Person.pid'='User.pid'"
As you know, since Yii is an MVC framework we have some Classes which are called Models. These Models are Classes built based on our tables, so in these Models we have defined our table and its attributes as well as its relations with other tables. Get one record of 'Person' as an example. Each record of this table can have a related record in 'User' with the same pid. To be more clear since 'User' is 'Person''s child (using inheritance when creating our tables), if our record's type in 'Person' is user, then we have a related record in 'User'. Now the relation defined in 'Person' Model which indicates that a record in 'Person' may have only one record in 'User' is as follows:
public function getUser()
{
return $this->hasOne(User::className(), ['pid' => 'pid']);
}
The name after get is the name of our relation, which is started with an uppercase letter. Since this function is written in 'Person' Model, $this is pointing to a record in 'Person'. hasOne shows that a record in 'Person' may have only one record in our related table. (User::className(), ['pid' => 'pid']) shows that the Model of our related table is 'User' and that they are joined on their pids. The first pid is 'User''s pid and the second one is 'Person''s pid.
Now, how can we call this function? When we are writing our query using Yii's functions, there is a function called joinWith(). This function takes our relation's name as the argument. So our query changes to:
\app\models\Person::find()->joinWith('user')->all();
This query brings all records in 'Person' joined with 'User' using LEFT JOIN on their pids.
We will talk about hasMany, Gii and other good features of Yii in our next post.