Background

We have this table lead_activity in our mysql database, with following fields

 1. id
 2. lead_id
 3. activity

example of rows:

In Laravel, I have got following query:

$this->data['lead_activities'] = LeadActivity::select(DB::Raw('count(*) as total'), 'activity')->groupBy('activity')->get();

With this result:

[
            0 => [
                'total' => 1,
                'activity' => 'Called'
            ],
            1 => [
                'total' => 1,
                'activity' => 'Selled'
            ],
            2 => [
                'total' => 2,
                'activity' => 'Contacted'
            ],
        ]

Request How can I build this query (whether Eloquent or raw SQL) so have something similar to these results within just one Query, without any for each after:

[
            0 => [
                'total' => 1,
                'activity' => 'Called',
                'lead_ids' => [5]
            ],
            1 => [
                'total' => 1,
                'activity' => 'Selled'
                'lead_ids' => [5],
            ],
            2 => [
                'total' => 2,
                'activity' => 'Contacted',
                'lead_ids' => [6,9]
            ],
        ]

CodePudding user response：

Have a look at the MySQL GROUP_CONCAT() function.

I think that should solve your problem. Its not returning you an array of lead_ids but a concatenation (string) of all lead_ids, you can work with (transform to array eventually) afterwards.

$list = LeadActivity::select(DB::Raw('count(*) as total'), 'activity','GROUP_CONCAT(lead_id) as lead_id_aggr')
        ->groupBy('activity')
        ->get();

reference: https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html#function_group-concat

CodePudding user response：

You can try this:

LeadActivity::query()
    ->select(
        'activity',
        DB::raw('GROUP_CONCAT(lead_id) as leads'),
        DB::raw('COUNT(*) as activities_count'),
    )->groupBy('activity')
    ->get();

which would produce this result:

Illuminate\Database\Eloquent\Collection {#2057
  all: [
    App\Models\LeadActivity {#2059
      activity: "Called",
      leads: "5",
      activities_count: 1,
    },
    App\Models\LeadActivity {#2060
      activity: "Contacted",
      leads: "6,9",
      activities_count: 2,
    },
    App\Models\LeadActivity {#2061
      activity: "Sold",
      leads: "5",
      activities_count: 1,
    },
  ],
}