How can I do this join-table with Eloquent Relationships?

Question

Purpose

I would like to get the total of mutual interests between all users referring to a single user, using Eloquent Relationships instead of Query Builder directly.

Table structure

users

+----+--------+
| id |  name  |
+----+--------+
|  1 | John   |
|  2 | George |
|  3 | Paul   |
+----+--------+

interests

+----+-----------+
| id |   name    |
+----+-----------+
|  1 | apple     |
|  2 | banana    |
|  3 | pineapple |
+----+-----------+

users_interests

+--------+------------+
| userId | interestId |
+--------+------------+
|      1 |          1 |
|      1 |          2 |
|      1 |          3 |
|      2 |          1 |
|      2 |          2 |
|      3 |          1 |
+--------+------------+

Example

Something like that...

[
    {
        "id": 2,
        "name": "George",
        "mutualInterests": 2,
    },
    {
        "id": 3,
        "name": "Paul",
        "mutualInterests": 1,
    }
]

Geordy James · Answer 1 · 2017-03-04T04:11:08.350

In your User model

public function interests()
{
    return $this->belongsToMany('App\Interest');
}

In your Interest model

public function users() 
{
    return $this->belongsToMany('App\User');
}

Now in your controller function

$users = User::all();

Now in your view to get the count of all number of interest in your each users.

@foreach($users as $user)
         {{ $user->interests()->count() }}
         <!-- if you need to extract all interests then -->
         @foreach($user->interests as $interest)
                  {{ $interest->name }}
         @endforeach
    @endforeach

An update based on your comment to see mutual interest count too

@foreach($users as $user)
         {{ $user->interests()->count() }}
         <!-- if you need to extract all interests then -->
         <?php $mutualInterest= 0; ?> 
         @foreach($user->interests as $interest)
                  <?php
                        $check = checkfunction($interest->id);
                        if($check == TRUE){
                              $mutualInterest = $mutualInterest+1;
                        }

                    ?>
                  {{ $interest->name }}
         @endforeach
         <?php echo "Mutual Intersts".$mutualInterest; ?>
@endforeach

Now in check function at bottom of your view

<?php
function checkfunction($id){
    $interest = App\Interest::find($id);

    if($interest->users()->count()>1){
        return true;
    }else{
        return false;
    }

}
?>

Thanks for your answer. In your example, I would get the total number of interests of each user separately. But my proposal was to get the number of interests in common with a single user. For example, George has two interests in common with John. I'd like to get George's details and the total number of interests they have in common. — Isaac Ferreira, Mar 03 '17 at 18:47

How can I do this join-table with Eloquent Relationships?

Purpose

Table structure

Example

1 Answers1