CODEWITHSUNDEEP
phplaraveleloquent
Matthijn
Matthijn

Reputation: 3234

Laravel: Fetch with where filter on "with"

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.

Upvotes: 0

Views: 339

Answers (3)

Marcin Nabiałek
Marcin Nabiałek

Reputation: 111899

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.

Upvotes: 2

Needpoule
Needpoule

Reputation: 4576

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

Upvotes: 2

Adrenaxus
Adrenaxus

Reputation: 1613

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();

Upvotes: 1

Related Questions