Laravel: Fetch with where filter on "with"

Question

In Laravel you can fetch a related database entry in one query with the with method like this:

$customer = Customer::with('user')->where([
    'id' =>'1'
])->first();

Which will return a customer and it's related user object. Is it possible to apply a filter to this with item? I thought that this would be possible:

$customer = Customer::with('user')->where([
    'id' =>'1',
    'users.status' => 'activated'
])->first();

But that does not seem to work, it tries to find a column "users.status" instead of a column status in the users table.

Needpoule · Accepted Answer · 2014-10-16 15:33:34Z

2

You can use Eager loading constraints. It allows you to perform queries on related models.

Like this:

$customer = Customer::with(['user' => function($query){
    $query->where('status', 'activated'); //in the user table
}])->where('id', '1')->first(); //in the customer table

You can read the eager load constaints part for more informations:

http://laravel.com/docs/4.2/eloquent#eager-loading

edited Oct 16, 2014 at 15:33

answered Oct 16, 2014 at 15:24

Needpoule

4,58626 silver badges34 bronze badges

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

Comments

Marcin Nabiałek · Accepted Answer · 2014-10-16 15:37:54Z

2

You could write your query this way:

$customer = Customer::with(['user' => function($q){
    $q->whereStatus('activated'); 
}])->find(1);

Assuming your primary key is 1, you don't need to use where('id','=','1')->get(), you can simply use find(1) and for selecting only some records for related table you use construction as with passing also closure.

answered Oct 16, 2014 at 15:37

Marcin Nabiałek

112k43 gold badges271 silver badges299 bronze badges

Comments

Adrenaxus · Accepted Answer · 2014-10-16 15:33:08Z

I'll throw in some of my code in addition to MrShibby's answer. This is how I do it, maybe you find that useful.

$activities = Activity::with('user')
     ->with(array('images'=>function($query){
         $query->orderBy('sort','ASC');
     }))
     ->with(array('images.titles'=>function($query){
        $query->orderBy('language_id','ASC');
    }))                
     ->with(array('contents'=>function($query){
         $query->orderBy('language_id','ASC');
     }))
     ->with(array('titles'=>function($query){
         $query->orderBy('language_id','ASC');
     }))             
     ->where('user_id',$user_id)
     ->whereRaw('DATE(NOW()) between online_from and online_until')
     ->orderBy('sort', 'ASC')
     ->get();

Collectives™ on Stack Overflow

Laravel: Fetch with where filter on "with"

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related