Laravel 4.2 subquery using Query Builder

Question

How to make this MySQL query in Query Builder?

SELECT traction_instances.*, 
           (SELECT COUNT(station_id) 
              FROM traction_stations 
             WHERE traction_instances.instance_id = traction_stations.monitoring_instance) as `count stations`
FROM traction_instances

What I've tried up to now is the following:

$instances = DB::connection('monitors')->table(DB::raw('traction_instances as ti, traction_stations as ts'))
            ->select(array('ti.instance_id', 'ti.status', 'ti.cpu_usage', DB::raw('count(ts.station_id) as station_count')))
            ->where(DB::raw('ti.instance_id'), '=', DB::raw('ts.monitoring_instance'))
            ->get();

With this, I get the single 'Instance' row (and there are 4 of them) with count of 'Stations' the instance is monitoring, but I need all the instances, and if there are no stations that that instance is monitoring, the station_count should be 0.

Possible duplicate of How to select from subquery using Laravel Query Builder? — Bogdan
– Bogdan, Commented Nov 2, 2015 at 15:50
I'm not sure the issue is the same. I've also tried this $instances = DB::connection('monitors')->table('traction_instances') ->select('traction_instances.*', function($query){ $query->selectRaw('count(*) FROM traction_stations WHERE traction_stations.monitoring_instance = traction_instances.instance_id'); }) ->get(); but this gives me strtolower() expects parameter 1 to be string, object given. — Marko Mijailovic
– Marko Mijailovic, Commented Nov 2, 2015 at 15:55

Marko Mijailovic · Accepted Answer · 2015-11-02 16:09:36Z

1

Found a solution:

$instances = DB::connection('monitors')->table('traction_instances as ti')
            ->where('ti.instance_type', 'Audio Stream Processor')
            ->select(DB::raw('ti.*, (SELECT COUNT(*) FROM traction_stations AS ts WHERE ti.instance_id = ts.monitoring_instance) AS station_count'))
            ->get();

answered Nov 2, 2015 at 16:09

Marko Mijailovic

4311 gold badge4 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Laravel 4.2 subquery using Query Builder

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related