If I have a query with groupBy
:
Table::select('a','b','c','d')->groupBy('a','b','c','d')->get();
How can I extract the unique values in each group?
For example if the dataset returned is:
Row1 = a1, b1, c1, d1
Row2 = a1, b1, c1, d2
Row3 = a2, b2, c2, d3
Row4 = a2, null, c3, d4
Then I want to extract the unique columns:
$col_a = [a1, a2]
$col_b = [b1, b2, null]
$col_c = [c1, c2, c3]
$col_d = [d1, d2, d3 ,d4]
CodePudding user response:
Okay so now as I understand your issue, please see the following solution: you do not need to use distinct in this situation since grouping will do the trick.
//Option 1 - Select only what you need
$query = Table::select('a', 'b', 'c', 'd')->get();
//Option 2 - if you need more data later
$query = Table::all();
$col_a = array_keys($query->groupby('a')->toArray());
$col_b = array_keys($query->groupby('b')->toArray());
$col_c = array_keys($query->groupby('c')->toArray());
$col_d = array_keys($query->groupby('d')->toArray());
Results:
$col_a = [a1, a2]
$col_b = [b1, b2, '']
$col_c = [c1, c2, c3]
$col_d = [d1, d2, d3 ,d4]
Group By::
The MySQL GROUP BY Statement groups rows that have the same values into summary rows, like "find the number of customers in each country". https://www.w3schools.com/mysql/mysql_groupby.asp