I want to select all the users that have the type of *student* and count all the countries that are connected to them. The order should be based on the number of countries linked to them.

**Users table:**

```
id    name
1     user1
2     user2
3     user3
4     user4
5     user5

```

**Countries table:**

```
id     country_name
1      America
2      Australia
3      Argentina
4      Afghanistan
5      India

```

**pivot\_countries\_user table:**

```
id     user_id     country_id
1      1           1
2      1           2
3      2           1
4      3           1
5      4           2
6      5           2
7      4           3
8      1           4

```

**user\_type table:** 

```
id    type       user_id
1     student    1
2     student    2
3     teacher    3
4     lawyer     4
5     teacher    5

```

**Here's the laravel query that I tried:** 

```
DB::table('users')
->leftjoin('pivot_countries_user','pivot_countries_user.user_id','=','users.id')
->leftjoin('countries','countries.id','=','pivot_countries_user.id')
->leftjoin('user_type','user_type.user_id','=','users.id')
->select('users.name','users.type',
  DB::raw('count(pivot_countries_user.country_id)')) // should be per user but I don't know how

```

**Expected output:**

```
name      type       total_countries
user1     student    3
user2     student    1

```