Group by results as a multidimensional array

Question

I have a requirement to get the data grouped by created_date, and then again to grop this data from the result set on affiliate_ad. I use this

 return DB::table($this->table)
      ->whereRaw($where['rawQuery'], isset($where['bindParams']) ? $where['bindParams'] : array())
      ->select('id', 'created_date','affiliate_ad', DB::raw('count(*) as total,count(affiliate_ad=1) as affiliate_ad_count,SUBSTRING(`created_date`, 1, 10) AS c_date'))
      ->groupBy('affiliate_ad','c_date')
      ->orderBy('c_date', 'desc')
      ->get();

It gives me a result like this

 Collection {#385
   #items: array:18 [
    0 => {#386
      +"id": 354766
      +"created_date": "2018-01-10 10:16:27"
      +"affiliate_ad": 1
      +"total": 2
      +"affiliate_ad_count": 1
      +"c_date": "2018-01-10"
    }
    1 => {#384
      +"id": 354730
      +"created_date": "2018-01-10 10:10:39"
      +"affiliate_ad": 0
      +"total": 3
      +"affiliate_ad_count": 4
      +"c_date": "2018-01-10"
    }
    2 => {#387
      +"id": 338263
      +"created_date": "2018-01-08 10:10:52"
      +"affiliate_ad": 0
      +"total": 83
      +"affiliate_ad_count": 83
      +"c_date": "2018-01-08"
    }
  ]
}

Here, if you check, in the first two indexes the created date will be the same. So I want to group them into one index of an array with an index 0thas multidimensional arraygrouped by affiliate_ad. The actual request is created as

SELECT id
     , created_date
     , affiliate_ad
     , COUNT(*) total
     , COUNT(affiliate_ad = 1) affiliate_ad_count
     , SUBSTRING(created_date,1,10) c_date 
  FROM facebook_ad 
 WHERE facebook_id = 12345 
   AND reward_status = 0 
   AND (first_seen BETWEEN 0 AND 99999999) 
 GROUP 
    BY affiliate_ad
     , c_date 
 ORDER 
    BY c_date desc

I need a conclusion like this

Collection {#385
   #items: array:18 [
    0 => [
        0 => {#386
          +"id": 354766
          +"created_date": "2018-01-10 10:16:27"
          +"affiliate_ad": 1
          +"total": 2
          +"affiliate_ad_count": 1
          +"c_date": "2018-01-10"
       }
       1 => {#384
          +"id": 354730
          +"created_date": "2018-01-10 10:10:39"
          +"affiliate_ad": 0
          +"total": 3
          +"affiliate_ad_count": 4
          +"c_date": "2018-01-10"
        }
    ]
    1 => [
        0 => {#387
          +"id": 338263
          +"created_date": "2018-01-08 10:10:52"
          +"affiliate_ad": 0
          +"total": 83
          +"affiliate_ad_count": 83
          +"c_date": "2018-01-08"
        }
   ]
  ]
}

I have this data in mysql

Answer 1

To get the image below

, . . ->groupBy('c_date')->values() , . ->values(), .

Answer 2

count(affiliate_ad=1)

SUM(affiliate_ad=1)

"" , affiliate_ad - 1.

COUNT , parens, . , NOT NULL. COUNT(DISTINCT col) .

SUM(expression) . expression (, affiliate_ad=1), TRUE 1, FALSE 0. , "", , , .

, NULLs //.

Answer 3

, - ...

SELECT * FROM ints;
+---+
| i |
+---+
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
+---+

SELECT COUNT(*) FROM ints;
+----------+
| COUNT(*) |
+----------+
|       10 |
+----------+

SELECT COUNT(i=1) FROM ints;
+------------+
| COUNT(i=1) |
+------------+
|         10 |
+------------+

Answer 4

, id , . , , , GROUP_CONCAT(id).

. : https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_group-concat

, , , .

Answer 5

return DB::table($this->table)
      ->whereRaw($where['rawQuery'], isset($where['bindParams']) ? $where['bindParams'] : array())
      ->select('id', 'created_date','affiliate_ad', DB::raw('count(*) as total,count(affiliate_ad=1) as affiliate_ad_count,SUBSTRING(`created_date`, 1, 10) AS c_date'))
      ->groupBy('affiliate_ad','c_date')
      ->orderBy('c_date', 'desc')
      ->get()->groupBy('affiliate_ad');

, config/database.php

strict => false,

Answer 6

The way you want to use a group only works with aggregates in MySQL, for example SUMor MAXetc.

You can do something like this, although I have not tested it when you retrieve the results from the database, follow these steps:

$results->groupBy('affiliate_ad');

foreach ($results as $i => $ad_group) {
    foreach ($ad_group as $items) {
        $results[$i] = (new Collection($items))->groupBy('c_date');  
    }
}

NOTE: depending on the version, Laravel DBreturns an array or object Collection, if it returns an array in your version, put it in a collection object like thisnew \Illuminate\Support\Collection($results)