Menu
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
388
The total number of elements that can be stored in a multidimensional array can be calculated by multiplying the size of all the dimensions. For example: The array int x[10][20] can store total (10*20) = 200 elements. Similarly array int x[5][10][20] can store total (5*10*20) = 1000 elements.
You can create a multidimensional array by creating a 2-D matrix first, and then extending it. For example, first define a 3-by-3 matrix as the first page in a 3-D array. Now add a second page. To do this, assign another 3-by-3 matrix to the index value 2 in the third dimension.
A multidimensional array associates each element in the array with multiple indexes. The most commonly used multidimensional array is the two-dimensional array, also known as a table or matrix. A two-dimensional array associates each of its elements with two indexes.
The multidimensional array is also known as rectangular arrays in C++. It can be two dimensional or three dimensional. The data is stored in tabular form (row ∗ column) which is also known as matrix.
To get similar to image below
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.
173
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
