Advanced Query Help

Need some help writing the following mysql working query. Works perfectly in as Normal SQL syntax in mysql workbench into Laravel DB syntax. I am banging my head trying to get this to work using Laravel DB.

SELECT id, company_name, company_contact_name, 
(SELECT COUNT(id)
  FROM riders
  WHERE company_id = companies.id) as ridercount
FROM companies

So I query companies table and get what I need, then using that company id, I pass that company id in the second Inner select statement where clause and using Count function, I get the number of riders for that company. here is a brief test example of what is returns

id name company_contact_name ridercount

75 Laravel LLC Taylor Otwell 11

any help would be greatly appreciated.

laracasts9924

5 years ago

Level 5

Well it seems everytime I finally break down and post to Laracasts forum. I end up finding my own solution.

So I kept trying to use DB::raw without success, but wrapping it in a DB::select does the job.

Here is my working query for Laravel DB syntax

DB::select(DB::raw('SELECT id,company_name, company_code, company_contact_name, company_contact_email, company_contact_phone, (SELECT COUNT(id) FROM riders WHERE company_id = companies.id) as ridercount FROM companies'));

PedroCPinto

5 years ago

Best Answer

Level 4

If you would like an approach based on the query builder you could go with something like this:

$riders = DB::table('riders') ->groupBy('company_id') ->select( DB::raw('COUNT(id) as riders'), 'company_id');

   return DB::table('companies as C')
        ->leftJoinSub($riders, 'R', 'R.company_id','=','C.id')
        ->select('C.id',
        'C.company_name',
        'C.company_contact_name',
        'R.riders')
        ->get();
}

laracasts9924

5 years ago

Level 5

thanks @pedrocpinto . your solution worked, much cleaner and easier to work with.

Appreciate the assist!

PedroCPinto

5 years ago

Level 4

No problem man, glad it helped :)

Please or to participate in this conversation.