Panagiotis Koursaris Panagiotis Koursaris - 3 months ago 14
MySQL Question

Count and sum multiple columns from different tables

I have the bellow tables:

Table users

| id | profile_img |
--------------------
| 21| name.img|
| 22| name.img|
| 33| name.img|


Table user_translations

|id | user_id |first_name |
--------------------------
| 1| 21 | panais |
| 2| 22 | andreas|
| 3| 33 | minas |


Table about

| id | user_id |
----------------
| 1| 33 |
| 2| 22 |
| 3| 21 |


Table things

| id | user_id |
----------------
| 1| 33 |
| 2| 22 |
| 3| 21 |
| 4| 21 |
| 5| 33 |


Each user can write


about articles to
about table
and thing articles to
things table



I want to get all users with the total numbers of articles. In other words I want to get that minas from above
user_translations
table has wrote total 3 articles.

Any help is appreciated.

Updated



I've posted an answer

Answer

Without laravel @George Pant has a good answer.

Laravel query builder to answer the question:

DB::table('users')
            ->join('about', 'users.id', '=', 'about.user_id')
            ->join('things', 'users.id', '=', 'things.user_id')
            ->join('user_translations', 'users.id', '=', 'user_translations.user_id')
            ->where('user_translations.locale', '=', 'en')
            ->select('first_name', 'last_name', 'user_translations.user_id',
                DB::raw('count(DISTINCT(things.id)) + count(DISTINCT(about.id)) as articles')
            )
            ->groupBy('first_name', 'users.id')
            ->get();