get total count in each ids from different table in laravel

Question

i want to get each id's total count from another table named assign job. i saved user data into my users table and i add 3 jobs into assign job table with user id.

but now i want to get total added jobs from assign job table where user ids in my users table at once.

I wrote this function

$assignjob = DB::table('users')
->join('assign_jobs', 'users.user_id', '=', 'assign_jobs.user_id')
->get()
->count();

but this shows me total count in my assign job table. but i want to get different count for each user ids.

i want to assign different color rows with this assign job total count values.

can any one help me.

User Table:

|---------------------|------------------|
|      user_id        |     name         |
|---------------------|------------------|
|          123        |     john         |
|---------------------|------------------|
|          234        |     peter        |
|---------------------|------------------|

assign job table:

|---------------------|------------------|
|      id             |     user_id      |
|---------------------|------------------|
|          1          |     123          |
|---------------------|------------------|
|          2          |     123          |
|---------------------|------------------|
|          3          |     234          |
|---------------------|------------------|
|          4          |     234          |
|---------------------|------------------|

score 2 · Accepted Answer · answered Feb 11 '19 at 07:58

2

You need to use GROUP BY clause for your user's id column and add COUNT() to your select. Try:

DB::table('users')
    ->select('users.user_id', DB::raw('COUNT(assign_jobs.user_id) AS jobs_count'))
    ->join('assign_jobs', 'users.user_id', '=', 'assign_jobs.user_id')
    ->groupBy('users.user_id')
    ->get();

answered Feb 11 '19 at 07:58

d3jn

1,392
3
13
21

thank you. it works. how can i show different colors for this array counts in my view table – MJ DEV Feb 11 '19 at 08:06
how to show other details like name, email in users table with this query – MJ DEV Feb 11 '19 at 08:54
@KalanaMihiranga tweak `->select(...)` method. If you want to select all columns from `users` table then switch `'users.user_id'` to `'users.*'`. [Check out the documentation](https://laravel.com/docs/5.7/queries#selects) on query builder for more info. – d3jn Feb 11 '19 at 09:10
Syntax error or access violation: 1055 'jobnetuva.users.id' isn't in GROUP BY – MJ DEV Feb 11 '19 at 13:37
@Kalana are you sure we are talking about MySQL here? Because by default MySQL allows partial `group by` clauses for non-aggregated columns and should not produce any errors. [Check answer to this question](https://stackoverflow.com/questions/25800411/mysql-isnt-in-group-by). So to solve this restriction you will have to `groupBy(...)` all the columns you want to select from `users` (not just `user_id`). – d3jn Feb 11 '19 at 14:56
yes sure i write this with Mysql. i tried different ways. but i can't find how to show all details – MJ DEV Feb 11 '19 at 15:15
if these tables want to make relationships – MJ DEV Feb 11 '19 at 15:16
if you wnat to select all columns. @KalanaMihiranga `$query = DB::table('users') ->join('assign_jobs', 'users.user_id', '=', 'assign_jobs.user_id') ->select('users.*, assign_jobs.*') ->groupBy('users.user_id','users.driver_name') ->select(DB::raw('COUNT(assign_jobs.user_id) AS shipments_count'),'name','anther_column') ->get();` – Abdel Rahman Kamhawy Sep 11 '21 at 14:16

score 0 · Answer 2 · answered Feb 11 '19 at 07:55

0

Select distinct usesr ID to loop into it to have the count for each users

answered Feb 11 '19 at 07:55

r0ulito

487
2
13

score 0 · Answer 3 · answered Feb 11 '19 at 08:08

0

try this:

add below relation to User Model:

public function assignJobs()
{
    return $this->hasMany(AssignJob::class, 'user_id', 'user_id');
}

and in controller:

User::withCount('assignJobs')->get();

answered Feb 11 '19 at 08:08

Jinal Somaiya

1,931
15
29

get total count in each ids from different table in laravel

3 Answers3