CODEWITHSUNDEEP
phplaravel
mafortis
mafortis

Reputation: 7138

Laravel query count total rows

I have this query:

$counts = DB::table('projects')
    ->join('prject_links', 'prject_links.project_id', '=', 'projects.id')
    ->join('links', 'links.id', '=', 'prject_links.link_id')
    ->join('segments', 'segments.id', '=', 'links.segment_id')
    ->join('hthree_regions', 'hthree_regions.id', '=', 'segments.hthree_id')
    ->join('areas', 'areas.id', '=', 'hthree_regions.area_id')
    ->join('zone_regions', 'zone_regions.id', '=', 'areas.zone_id')
    ->select(
        'zone_regions.id as regions',
        'areas.id as provinces',
        'hthree_regions.id as cities',
        'segments.id as segments'
    )
    ->get();

what I'm looking to have as result is something like:

counts => [
  regions => 20,
  provinces => 10,
  cities => 7,
  segments => 5
];

What do i get currently is confusing result like:

one

any idea about how to fix my query?

Upvotes: 2

Views: 135

Answers (2)

TsaiKoga
TsaiKoga

Reputation: 13404

Use groupBy and count(distinct field) to get the field's count in each project:

->selectRaw(
        "CONCAT('project ', projects.id) as project, 
         COUNT(DISTINCT zone_regions.id) as regions,
         COUNT(DISTINCT areas.id) as provinces,
         COUNT(DISTINCT hthree_regions.id) as cities,
         COUNT(DISTINCT segments.id) as segments"
    )
->groupBy('projects.id')
->get();

Upvotes: 2

Akhzar Javed
Akhzar Javed

Reputation: 626

This might be your solution. Run it and let me know if that helps

$counts = DB::table('projects')
    ->join('prject_links', 'prject_links.project_id', '=', 'projects.id')
    ->join('links', 'links.id', '=', 'prject_links.link_id')
    ->join('segments', 'segments.id', '=', 'links.segment_id')
    ->join('hthree_regions', 'hthree_regions.id', '=', 'segments.hthree_id')
    ->join('areas', 'areas.id', '=', 'hthree_regions.area_id')
    ->join('zone_regions', 'zone_regions.id', '=', 'areas.zone_id')
    ->groupBy('projects.id')
    ->selectRaw(
        'count(zone_regions.id) as regions',
        'count(areas.id) as provinces',
        'count(hthree_regions.id) as cities',
        'count(segments.id) as segments'
    )
    ->get();

Upvotes: 0

Related Questions