Laravel query with joins and multiple where

Question

I am trying to execute this query:

select
    *
FROM
    users
    left join emails on users.id = emails.user_id
WHERE
    users.username = 'firstuser'
    and emails.emailType = 'Primary';

This is what I have but it gives me an error:

$user = User::where('username','firstuser')->with('emails')
                                          ->where('emailType','Primary')
                                          ->get();

The message indicates that the join is not being made:

Column not found: 1054 Unknown column 'emailType' in 'where clause' (SQL: select * from `users` where `username` = firstuser and `emailType` = Primary)

Also, in my User.php model file in have a function emails():

public function emails()
   {
        return $this->hasMany('Email');
   }

In my Email.php model I have a user() function

public function user()
{
  return $this->belongsTo('User');
}

I've tried a bunch of combinations but can't get both where conditions to work together. I'd like to do this as one query and not two queries (get userid and then query email with the user_id) Thanks in advance for any and all help!

You need either join or combo of whereHas and with. What result you expect exactly? Btw with doesn't join tables, that's why you get the error. — Jarek Tkaczyk
– Jarek Tkaczyk, Commented Aug 12, 2014 at 17:35

c-griffin · Accepted Answer · 2014-08-13 17:00:01Z

2

You need to specify the where for the emails relationship in a callback to with(), otherwise, Eloquent will think you're trying to look for the emailType field in the users table.

$user = User::with(['emails' => function($query){
    $query->where('emailType','Primary');    
}])->where('username','first user')
->get();

edited Aug 13, 2014 at 17:00

answered Aug 12, 2014 at 17:09

c-griffin

3,0561 gold badge20 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ScottM Over a year ago

I tried this and it didn't work. with() will not accept a closure as a parameter and errors with an expecting string but getting an object.

c-griffin Over a year ago

Try passing an array to with() key being the relationship, val being the callback.

ScottM Over a year ago

This does work as you say... Sorry that it took so long to get back to choosing it as the correct answer.

ScottM · Accepted Answer · 2014-08-13 13:36:45Z

0

I got this to work based on the comments above. But, it isn't a very elegant solution as I defined the relationships in the model but the join kind of bypasses all of that to force the join. Here is what worked...

$user = User::join('emails', 'users.id', '=', 'emails.user_id')
                    ->where('emailType','=','Primary')
                    ->where('username',$username)
                    ->get();

What is the point of defining the relationships in your model if they don't get used to join the tables when you want to query the model? Perhaps I'm missing something.

answered Aug 13, 2014 at 13:36

ScottM

881 silver badge11 bronze badges

Collectives™ on Stack Overflow

Laravel query with joins and multiple where

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related