How to group rows together all having a common title in Laravel

Is it possible to group rows together where all the rows are actually outputted but in a very organized JSON format with one SQL query?

For example, if I have a table with columns: lastname, firstname, address, and phone number and I wanted to Group the rows together based on the first letters of their last name so that I have something.

{ { letter: ‘A’, rows: { { lastname: ‘Anfar’, firstname: ‘Peter’, address: ‘Street city state country’, phone_number: ‘phone number’ },

          {             lastname: 'Akon',             firstname: 'Clide',             address: 'Street city state country',             phone_number: 'user_phone_number'           },           {             lastname: 'Abel',             firstname: 'Nigel',             address: 'Street city state country',             phone_number: 'phone number'           },     } },  {    letter: 'B',       rows: {           {             lastname: 'Best',             firstname: 'Peter',             address: 'Street city state country',             phone_number: 'phone number'           },            {             lastname: 'Bide',             firstname: 'Clide',             address: 'Street city state country',             phone_number: 'user_phone_number'           },           {             lastname: 'Biden',             firstname: 'Nigel',             address: 'Street city state country',             phone_number: 'phone number'           },     } } 

}

I’m using laravel and I want to know if there’s a way to achieve something like this

Asked on September 1, 2020 in Sql.
Add Comment
1 Answer(s)

You can use the groupBy()function of the collection.

Add a letter column to your query:

$q = User::query()       ->selectRaw('LEFT(lastname,1) AS letter, lastname')       ->get(); 

and then use groupBy('letter') to group the list

$q->groupBy('letter') 
Answered on September 1, 2020.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.