CODEWITHSUNDEEP
laravel
aarush magar
aarush magar

Reputation: 21

How to groupBy on one column in laravel?

I have a section table and class Table

class table is designed in this way (id,class_name,section_id)

one class has many sections like

--------------------------------------------
|    SN   |   ClassName   |   Section_id   | 
--------------------------------------------
|       1 |    ClassOne   |              1 |
|       2 |    ClassOne   |              2 |
|       3 |    ClassOne   |              3 |
|       4 |    ClassOne   |              4 |
--------------------------------------------

Now i want to groupBy Only ClassName and display all the sections of that class

 $data['classes'] = SectionClass::groupBy('class_name')->paginate(10);

i have groupby like this but it only gives me one section id

Upvotes: 0

Views: 1421

Answers (2)

antonio_mg
antonio_mg

Reputation: 492

You are getting just one row because that is what GROUP BY does, groups a set of rows into a set of summary rows and returns one row for each group. In standard SQL, a query that includes a GROUP BY clause cannot refer to nonaggregated columns in the select list that are not named in the GROUP BY clause. For example, in SQL Server if you try the next clause

SELECT * FROM [Class] GROUP BY [ClassName]

You'll get the next error

"Column 'SN' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause"

Think about it, you are grouping by ClassName, and following your sample data, this will return just one row. Your SELECT clause includes column ClassName, which is easy to get because is the same in every single row, but when you are selecting another, which one should be return if only one has to be selected?

Now, things change a little bit in MySQL. MySQL extends the standard SQL use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. This means that the preceding query is legal in MySQL. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are nondeterministic. You can find a complete explanation about this topic here https://dev.mysql.com/doc/refman/5.6/en/group-by-handling.html

If you are expecting a result in one row, you can use GROUP_CONCAT() function to get something like

--------------------------------
|   ClassName   |   Sections   | 
--------------------------------
|   ClassOne    |    1,2,3,4   |
--------------------------------

Your query must be something like:

select `ClassName`, group_concat(Section_id) from `class` group by `ClassName`

You can get this with a raw query in laravel or its up to you to find a way to get the same result using query builder ;)

Upvotes: 0

Gabrielle-M
Gabrielle-M

Reputation: 1077

Try this way...

$things = SectionClass::paginate(10); 

$data['classes']= $things->groupBy('class_name');

Upvotes: 1

Related Questions