how to get all the rows of data + joins data from the database ?

Published 2 months ago by wardaddy

I want to show all the rows of data from the database plus data from joins function

Channels Table

| id | user_id | name |

|----|----------|--------|

| 1 | 1 | channel #1 |

| 2 | 2 | channel #2 |

| 3 | NULL | channel #2 |

| 4 | NULL | channel #2 |

Users Table

| id | name |

|----|--------|

| 1 | John |

| 2 | Jane |

here my code

dashboardController.php

public function index()
{
    $channels = Channel::join('users', 'channels.user_id', '=', 'users.id')
                     ->select('channels.*', 'users.name as dj_name')
                     ->orderBy('updated_at', 'desc')
                     ->get();

    return view('dashboard.master', compact('channels'));
}

master.blade.php

@foreach ($channels->take(4) as $channel)
    <div class="col s12 m4 l3">
        <div class="card grey lighten-4 z-depth-0">
            <div class="card-content">
                <h5 class="truncate">{{ $channel->name }}</h5>
                <div class="d-flex justify-content-between align-items-end">
                    <div>
                        @if ($channel->user_id == NULL)
                            <p class="lead mt-1">No DJ here!</p>
                        @else
                            <p class="lead mt-1">DJ : {{ $channel->dj_name }}</p>
                        @endif
                        <small>ID Channel : {{ $channel->id }}</small>
                    </div>
                </div>
            </div>
        </div>
    </div>
@endforeach

if I run this code, it only shows channels that have user_id and does not display channels with user_id = "NULL"

what should I do to display a channel that has users_id along with a channel with user_id = "NULL"?

Best Answer (As Selected By wardaddy)
Stratos

https://laravel.com/docs/5.5/eloquent-relationships

Following documentation:

In your Channel model.

class Channel extends Model
{
    public function user()
    {
        return $this->belongsTo('App\User');
    }
}

Then in your controller you can do

$channels = Channel::all();

and in your blade, retrieve the DJ name as

$channel->user->name
ftrillo

try a leftJoin instead of a join.

Also, it's much easier to get data from related models if you take advantage of Eloquent relationships.

wardaddy

@ftrillo thanks again, you really helped me

get data from related models if you take advantage of Eloquent relationships.

I can't understand what you mean now, the code I have is enough for me now, maybe someday I can learn a more efficient way to do the things I want, but thanks for telling me

Stratos

https://laravel.com/docs/5.5/eloquent-relationships

Following documentation:

In your Channel model.

class Channel extends Model
{
    public function user()
    {
        return $this->belongsTo('App\User');
    }
}

Then in your controller you can do

$channels = Channel::all();

and in your blade, retrieve the DJ name as

$channel->user->name
wardaddy

wow it's so much more efficient than my code, I still have much to learn

thanks @Stratos

ftrillo

@wardaddy If you're goint to do it the eloquent way, you should know about eager loading.

https://laravel.com/docs/5.5/eloquent-relationships#eager-loading

Basically

// If you retreive the channels normally like this
$channels = Channel::all();

// Then you'll be making an extra query for each channel's user you want to access.  (This is know as the N+1 query problem)

// If you retreive the channels, eager loading the users at the same time...
$channels = Channel::with('user')->all(); // like this

// Then you're retreiving the users beforhand, you're only running 2 queries. Instead of N + 1

Please sign in or create an account to participate in this conversation.