Home > Net >  How to extract unique values of each group of groupBy query?
How to extract unique values of each group of groupBy query?

Time:12-26

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

  • Related