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 tableand thing articles tothings 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