Laravel count with where on Query Builder with joins

Question

Good day all, I am trying to count all records in a table but only if the table does not contain data in a specific column (deleted_at). It is a join table the table names are companies and employees. I am currently counting the records with a DB::raw but it should only count it if the deleted_at column is null. Please understand that i am a beginner.

public function index() { $user = Auth::user()->id; $companies = DB::table('companies AS c') ->select([ 'c.id', 'c.logo', 'c.company_name', 'c.created_at', 'c.sector', 'c.deleted_at', DB::raw('COUNT(e.id) AS employee_count') ]) ->leftJoin('employees AS e', 'e.company_id', '=', 'c.id' ) ->leftJoin('company_user AS cu', 'cu.company_id', '=', 'c.id') ->where('cu.user_id', '=', $user) ->where('c.deleted_at', '=', null) ->groupBy('c.id') ->get(); return view('account.companies.index') ->with('companies', $companies); }

M Khalid Junaid · Accepted Answer · 2018-06-20 16:35:08Z

If you are using Mysql then you could use conditional aggregation

$companies = DB::table('companies AS c') ->select([ 'c.id', 'c.logo', 'c.company_name', 'c.created_at', 'c.sector', 'c.deleted_at', DB::raw('SUM(c.deleted_at IS NULL) AS employee_count') ]) ->leftJoin('employees AS e', 'e.company_id', '=', 'c.id' ) ->leftJoin('company_user AS cu', 'cu.company_id', '=', 'c.id') ->where('cu.user_id', '=', $user) ->groupBy('c.id') ->get();

In mysql when an expression is used inside sum(a= b) it will result as a boolean 0/1 so you can get your conditional count using above

Or you could use whereNull() method in your query

->whereNull('c.deleted_at')

Thank you so much, both methods works. just changed from c.deleted_at to e.deleted_at. Have a wonderfull day :)

Sapna · Accepted Answer · 2021-10-29 08:30:36Z

Use this code:

$employeeCount = DB::table('employees') ->select('companies.name', DB::raw('count(employees.id) as employee_count')) ->join('companies', 'employees.company','=','companies.id') ->groupBy('companies.id') ->get();

Your answer does not address the user's question, and contains no explanation. As well, the question is 3 years old and has an accepted answer already.

Collectives™ on Stack Overflow

Laravel count with where on Query Builder with joins

2 Answers 2

1 Comment

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Related