Eloquent nested relations with where clause

You can simply do this

$books = Book::with(['author' => function($query) {
    $query->where('something', 'something');
}, 'author.contacts' => function($query) {
    $query->where('is_deleted', 'N');
}])->get();

Use whereHas (can be nested) :

$books = Book::whereHas('author', function($query) {
    $query->where('is_online', 'Y')->whereHas('contacts', function ($query) {
        $query->where('is_deleted', 'N');
    });
}])->get();

@pmall I guess he wants the author and the contacts as well here

Thanks @bobbybouwmann , I've tried this

$books = Book::with(['author' => function($query) {
    $query->where('is_deleted', 'N');
}, 'author.contacts' => function($query) {
    $query->where('is_deleted', 'N');
}])->get();

but still getting the deleted author in the result set. I think 'author.contacts' => function($query) { ... } overrides the result set returned by 'author' => function($query) { .... } .

So :

$cb = function ($query)
{
    $query->where('is_deleted', 'N');
};

$books = Book::with(['author.contact' => $cb)->whereHas('author', function($query) use ($cb) {

    $query->where('is_online', 'Y')->whereHas('contacts', $cb);

}])->get();

@pmall & @bobbybouwmann yeah, I want both of them (i.e. author and contact result set).

So check my second answer

@pmall I've tried your second answer and still getting the deleted user with no result set for auth_tokens table. Here is what i have tried

$notExpiredCheck = function($query) {
                                $query->notExpired();
                            };

return $query->with(['user.authToken' => $notExpiredCheck])
                        ->whereHas('user', function($query) use($select, $notExpiredCheck) {
                            User::select($select)
                            ->userGroup(UserGroup::$consumerGroupId)
                            ->notDeleted()
                            ->enabled()
                            ->active()
                            ->online()
                            ->having('distance', '<', User::$radius)
                            ->orderBy('distance', 'ASC')
                            ->whereHas('authToken', $notExpiredCheck);
                        })
                        ->get();

What ? This is a completely different query from what you asked at first

it just has some extra where / scope method calls but the scenario is same.

please fully explain what you want to do

OK. Here is DB schema https://drive.google.com/file/d/0B7xNrciTL1kyOFhseG5OOVlHTUU/view?usp=sharing. Lets say we have some services like Web Development. What i want is to get all users and their related auth token details who provide the web development service. Please don't forget, I want to use where clause for each table relation as you can see I have soft delete columns e.g. is_deleted .

Soft delete mechanism in laravel automatically add the where clause for not retrieving the soft deleted lines.

But how do i fetch all those relations in a single Eloquent query? @JeffreyWay Please help me.

Lol... We already gave you the code to that, I will just refer you to the documentation! It's all clear and full of examples: http://laravel.com/docs/5.1/eloquent-relationships#querying-relations

@bobbybouwmann please take a look at DB schema, I need to use (e.g.) $query->where('is_deleted', 'N') for all tables except auth_tokens table. I am just asking how to use where clauses for each relation. I read the documentation before posting question here and couldn't find any solution.

Just at the where statement to each relation?

$cb = function ($query) {
    $query->where('is_deleted', 'N');
};

$books = Book::with(['author' => $cb, 'author.contact' => $cb])->get();

As i already said, that doesn't work at all. When i try to run

$books = Book::with(['author' => $cb, 'author.contact' => $cb])->get();

the result set of authors table returned by first relation i.e. author is overridden by the second relation i.e. author.contact. Actually author.contact returns both result sets 1)- For authors table 2)- For contacts table

That is why I am not getting desired results.

@Manzoor-Bajwa I'm stuck on a similar issue. Did you find a solution?

@Manzoor-Bajwa @timrpeterson

I know this is old but this should work

        $books = Book::with('author.contacts')->whereHas( 'author.contacts', function($query) {
            $query->where('is_deleted', 'N');
        })->get();

Thanks man.

@manzoor-bajwa try this

$books  = Book::with(['author' => function ($query) {
		// auther condition
        $query->where('is_active', 0);
        $query->with(['contacts' => function ($q2) {
			// contacts condition
            $q2->where('is_active', 1);
        }]);
    }])->get();