How to join 2 tables and group them by parent table id in laravel?

Question

I have 2 tables: t1: hotels t2: people

There are 5 hotels and in every hotel a different number of people. I'd like to get hotel name and people's names assigned to that hotel. With the query I have it duplicated the hotel name as many times as there are people in it.

$hotels=DB::table('hotels')
->select('hotels.*','people.name')
->leftjoin('people','people.hotel_id','=','hotel.id')
->get();

return view('hotels', ['hotels' => $hotels]);

And in my view I have

@foreach($hotels as $h)
$h->hotel_name
@endforeach

hotel_name now shows up 4 times which is the same number of people assigned to the hotel_id. I've tried using group_concat but with that I could only get the names of the people.

Is there a group by or some other method which could help me to get to my goal?

So... you want the Hotel name followed by an array of names assigned to that Hotel, am I right ? — J. Alderete, Apr 25 '18 at 19:33
Are you suggesting that you know how to do it in MySQL, but Laravel is getting in the way? — Rick James, Apr 26 '18 at 02:24
@Studio, can you mark one as correct so people know where to look? — parker_codes, May 02 '18 at 17:46

score 3 · Answer 1 · edited Apr 27 '18 at 10:12

In your HotelModel

public function peoples()
{
    return $this->hasMany('App\People','hotel_id');
}

In your Controller
(use ->with('peoples') to solve the n+1 problem.

$hotels = Hotel::with('peoples')->get();    
return view('hotels', ['hotels' => $hotels]);

In your View

@foreach($hotels as $h)
    $h->hotel_name

    @foreach($h->peoples as $people)
        $people->name
    @endforeach

@endforeach

Amr Aly · Answer 2 · 2018-04-25T20:25:29.970

Actually you can achieve this using group_concat and group_by like so :

select hotels.* ,group_concat(people.name) as people_names
from hotels 
  left join people on hotels.id = people.hotel_id 
group by hotel_name;

using the query builder :

$hotels = DB::table('hotels')
   ->select('hotels.*', DB::raw('group_concat(people.name) as people_names'))
   ->leftjoin('people','people.hotel_id','=','hotels.id')
   ->groupBy('hotel_name')
   ->get();

score 0 · Answer 3 · answered Apr 23 '18 at 15:10

0

Add to your Hotel class

public function peoples()
{
    return $this->hasMany(People::class);
}

In your view:

@foreach ($hotel->people as $people)
    {{ $people->name }}
@endforeach

answered Apr 23 '18 at 15:10

J. Doe

1,682
11
22

score 0 · Answer 4 · answered Apr 23 '18 at 15:11

0

Try to use Collection's groupBy function:

$hotels=DB::table('hotels')
->select('hotels.*','people.name')
->leftjoin('people','people.hotel_id','=','hotel.id')
->get();
$grouped = $hotels->groupBy('hotel_name');

return view('hotels', ['hotels' => $grouped]);

And then, print it like this:

@foreach($hotels as $key => $h)
    Hotel name: {{ $key }}
    @foreach ($h as $p)
        Person name: {{ $p }}
    @endforeach 
@endforeach

answered Apr 23 '18 at 15:11

Laerte

7,013
3
32
50

Hotel name: {{ $key }} displays nothing. Also in this case how can I get hotel_name, hotel_location and so on? – Studio DnB Apr 23 '18 at 15:19
Using this, you will need to select them. Try to `dd($grouped);` to see how the information is organized. It will clarify it. – Laerte Apr 23 '18 at 17:56

score 0 · Answer 5 · answered Apr 25 '18 at 21:57

0

Make a Hotel model and define the relationship:

public function people()
{
    return $this->hasMany(People::class);
}

I'm your controller:

$hotels = Hotel::all():
return view('hotels')->with('hotels', $hotels);

In your view:

@foreach($hotels as $hotel)

    {{ $hotel->name }}

    @foreach($hotel->people as $person)
        {{ $person->name }}
    @endforeach

@endforeach

answered Apr 25 '18 at 21:57

parker_codes

3,267
1
19
27

I'm recommend you to read about [n-query problem](https://laravel.com/docs/5.6/eloquent-relationships#eager-loading). Your example could propagate a bad habit. – Wojciech Mleczek Apr 26 '18 at 10:17

How to join 2 tables and group them by parent table id in laravel?

5 Answers5