CODEWITHSUNDEEP
phpmysqllaraveljoin
stack
stack

Reputation: 10228

How can I make a relation between tables in Laravel?

I have three tables like these:

// users
+----+-----------+
| id | user_name |
+----+-----------+
| 1  | Jack      |
| 2  | Peter     |
| 3  | John      |
+----+-----------+

// skills
+----+------------+
| id | skill_name |
+----+------------+
| 1  | PHP        |
| 2  | HTML       |
| 3  | Laravel    |
| 4  | MySQL      |
| 5  | jQuery     |
+----+------------+

// skill_user
+----------+---------+
| skill_id | user_id |
+----------+---------+
| 2        | 1       |
| 5        | 1       |
| 1        | 2       |
| 2        | 2       |
| 4        | 2       |
+----------+---------+

Now I want to get all skills of one specific user. I can do that by using a join clause in a raw sql query:

SELECT s.skill_name
FROM skills s
JOIN skill_user su ON s.id = su.skill_id
WHERE su.user_id = :user_id

/* output (assuming :user_id is 1)
+------------+
| HTML       |
| jQuery     |
+------------+

Ok All fine. Now I want to know, how can I do that by adding a method in the model of Laravel framework? All I'm trying to do is using belongsTo(), hasOne(), hasMany() etc ..

Upvotes: 2

Views: 637

Answers (3)

geckob
geckob

Reputation: 8128

To do in Eloquent way, you need to add the relationship. In your case, it is many-to-many relationship so in User.php, add this function to declare the relationship:

public function skills() {
    $this->belongsToMany(\App\Skill::class, 'skill_user', 'user_id', 'skill_id');
}

Then,

Access it like this to eager load it:

$users = \App\User::with('skills')->get();

OR

Say you have a single user, 

$user = User::where('id', 1)->first(); // Get the user with id = 1

and to get the skill for that user:

$user->skills;

OR

If you don't want to do using Eloquent way, you can use Query Builder. Note that, using this method, you basically join it manually so you don;t need to add any method to the Model:

 \DB::table('users') 
    ->join('skill_user', 'skill_user.user_id','=', 'users.id') 
    ->join('skills', 'skill_user.skill_id', '=', 'skills.id')
    ->select('*')
    ->where('users.id', 1)
    ->get();

Upvotes: 3

omitobi
omitobi

Reputation: 7334

I assume you are talking about Many to Many relationship between Users and skills, then the example given in Laravel many-to-many speaks about it.

Upvotes: 1

Saravanan Sampathkumar
Saravanan Sampathkumar

Reputation: 3261

You have to use belongsToMany relationships. Assuming you will create SkillUser model for skill_user table. Add the following code to User Model

public function skills() {
    $this->belongsToMany('App\SkillUser', 'skill_user', 'user_id', 'skill_id');
}

Now you can access it by

$users->skills();

Upvotes: 2

Related Questions