CODEWITHSUNDEEP
phpmysqllaravellaravel-5group-by
RAUSHAN KUMAR
RAUSHAN KUMAR

Reputation: 6006

Group by the results as multidimensional array

I have requirement to get data grouped by created_date and then again groped this result set data on affiliate_ad. I am using 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's giving me 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 index the created date is same. So i want to group them in one array index at 0th index as multidimensional array grouped on affiliate_ad. The actual query is build 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 the output 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 these data in mysql MySql Data Screenshot

Upvotes: 6

Views: 2588

Answers (6)

Sasa Blagojevic
Sasa Blagojevic

Reputation: 2200

The way you want to use group by works only on aggregates in MySQL, like SUM or MAX, etc.

You can do something like this, though I haven't tested it, when you fetch your results from the database do the following: 

$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 of the version of Laravel DB returns an array or a Collection object, if it returns an array in your version put it in a collection object like this new \Illuminate\Support\Collection($results)

Upvotes: 2

Hamelraj
Hamelraj

Reputation: 4826

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');

This will work for you if you get error in your config/database.php change

strict => false,

Upvotes: 2

Ersoy
Ersoy

Reputation: 9586

To get similar to image below

enter image description here

You may consider to format it after you get your results from your query. It already returned you a collection. you may add ->groupBy('c_date')->values() to collection to retrieve the given result. If you remove ->values() part it will preserve grouped dates as key.

Upvotes: 4

TCooper
TCooper

Reputation: 1900

Seeing as id is unique and you're selecting it individually, it won't be grouped in your results. If you need the individual ID's as well as the other data you're returning, I'd suggest using GROUP_CONCAT(id).

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

Not sure if this fully answers your question, but hope it helps point you in the right direction.

Upvotes: 2

Rick James
Rick James

Reputation: 142208

Change

count(affiliate_ad=1)

to

SUM(affiliate_ad=1)

if you want to 'count' the number of rows where affiliate_ad is 1.

That is, COUNT ignores what you put in the parens unless it is a column. In that case it counts how many rows are have that column NOT NULL. COUNT(DISTINCT col) does a different thing.

SUM(expression) evaluates the expression and adds up the values. If expression is Boolean (eg, affiliate_ad=1), then TRUE is treated as 1 and FALSE is treated as 0. Hence, it gives you the 'count' you probably want.

Beware of how NULLs are counted/summed/averaged.

Upvotes: 3

Strawberry
Strawberry

Reputation: 33935

Too long for a comment, but something quite important to consider...

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 |
+------------+

Upvotes: 2

Related Questions