mardi 2 août 2016

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.

I did the bellow:

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')
            ->select('first_name', 'last_name', 'user_translations.user_id',
                DB::raw('count(things.id) as thing_post'),
                DB::raw('count(about.id) as aboutg_post')
            )
            ->groupBy('first_name')
            ->get();

I stopped here because thing_post and aboutg_post column got not expected results. They count things and about and then multiply them. I don't understand why.

Any help is appreciated.



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire