Consolidate/concatinate rows of data with laravel eloquent

Question

I'm retreiving data from severel tables in the following way:

$data = Accommodation::join("hotels", "accommodations.hotel_id", "=", "hotels.id")
        ->join("room_types", "accommodations.room_type", "=", "room_types.id")
        ->join("country", "accommodations.country_id", "=", "country.id")
        ->join("accommodation_accreditation", "accommodations.id", "=", "accommodation_accreditation.accommodation_id")
        ->join("accreditation", 'accreditation.id', "=", "accommodation_accreditation.accreditation_id")
        ->select('hotels.name as Hotel' ,'room_types.type as Room', 'price_per_night as Price per Night', 'check_in as Check In', 'check_out as Check Out', 'nights as Nights', 'sub_total as Sub Total', 'country.name as Country', 'accreditation.full_name as Occupants')
        ->get()->toArray();

And I receive a row for each occupant in a room.

          Room           Price per Night    Check In    Check Out    Nights   Sub Total   Country       Occupants      
 ---------------------- ----------------- ------------ ------------ -------- ----------- --------- ------------------- 
  Twin/Double standart               160   2019-04-10   2019-04-15        5         800   UKR       Vlad Timochenko  
  Twin/Double standart               160   2019-04-10   2019-04-15        5         800   UKR       Selena Viachenko

I need to concatinate names of occupants and get them in one row like this:

          Room           Price per Night    Check In    Check Out    Nights   Sub Total   Country               Occupants               
 ---------------------- ----------------- ------------ ------------ -------- ----------- --------- ------------------------------------ 
  Twin/Double standart               160   2019-04-10   2019-04-15        5         800   UKR       Vlada Nikolchenko, Olena Diachenko

Here is my schema for the tables in question

Hope it's clear. Will be greatfull if you could help.

This doesn't require any Laravel specific features, you can use MySQL's `GROUP_CONCAT`. — sam, Mar 10 '19 at 17:05
Why people are afraid of using Eloquent relationships? Is there any specific reason why you haven't go with it? — Benjamin Beganović, Mar 10 '19 at 19:42
@Benjamin Truth is I'm not very comfortable with it and I had to finish with this faster so I decided to use something more familiar. — Vasko, Mar 11 '19 at 13:37

score 0 · Accepted Answer · answered Mar 11 '19 at 14:24

To be honest I found the complete solution for my case from different places.

As sam sugested I used GROUP_CONCAT. And my code looked like:

$data = Accommodation::join("hotels", "accommodations.hotel_id", "=", "hotels.id")
        ->join("room_types", "accommodations.room_type", "=", "room_types.id")
        ->join("country", "accommodations.country_id", "=", "country.id")
        ->join("accommodation_accreditation", "accommodations.id", "=", "accommodation_accreditation.accommodation_id")
        ->join("accreditation", 'accreditation.id', "=", "accommodation_accreditation.accreditation_id")
        ->select('hotels.name as Hotel' ,'room_types.type as Room', 'price_per_night as Price per Night', 'check_in as Check In', 'check_out as Check Out', 'nights as Nights', 'sub_total as Sub Total', 'country.name as Country', DB:raw("GROUP_CONCAT('accreditation.full_name SEPRATOR ',') as `Occupants`"))
        ->groupBy('accommodation.id')
        ->get()->toArray();

The other thing I had to be cearful was using back quotes for the Occupants label otherwise the SEPARATOR was not working properly.

There are other similar threads so this is a duplicate.

Group_concat - laravel eloquent how to use GROUP_CONCAT in laravel

Consolidate/concatinate rows of data with laravel eloquent

1 Answers1