CODEWITHSUNDEEP
phpmysqldatabaselaraveljoin
Matt
Matt

Reputation: 7

Laravel - use join to select all from one table and a value from the other

I've got two tables 'emails' and 'users' and I want to be able to use one query to get all information about the email and information which user is assigned to this email (with option that no one is assigned to this email).

so what I did is:

$userInfo = Emails::leftjoin('Users', 'Emails.email', '=', 'Users.email')
  ->select('Emails.*', 'Users.username')
  ->where([
    ['Emails.owner_id', '=', Auth::id()],
  ])
  ->get();

as far as I can see this is almost working BUT above solution is also selecting deleted users (users with a value in deleted_at) so the simplest solution was to add 

['Users.deleted_at', '=', null],

this helped with deleted users but with above condition I'm no longer getting emails that are not assigned to no one.

How I can select everything from email table and get username from users table only if 'Emails.email', '=', 'Users.email'.

Upvotes: 0

Views: 1746

Answers (1)

Kiran Ajudiya
Kiran Ajudiya

Reputation: 22

In your Email model add this user relationship with your user model name

public function users(){
    return $this->hasMany(Users::class, 'email', 'email')->whereNull('deleted_at');
}

In your controller you can get not deleted users like this 

$userInfo=Emails::with('users')->where('owner_id', Auth::id())->get();

You can get user name like this with all emails data.

$userInfo->users->username

Upvotes: 0

Related Questions