CODEWITHSUNDEEP
laraveleloquentlaravel-6
SteveLeg
SteveLeg

Reputation: 89

Eloquent with query on relations with nested WHERE

I'm struggling with Eloquent with query on relation.

For example, I'm looking for only the client John who doesn't have transaction.

How can I do this with Eloquent?

Client model relation

public function transactions()
{
    return $this->hasMany(Transaction::class);
}

$results = Client::whereDoesntHave('transactions', function ($query) use ($inputFirst, $period) {
    $query->where('transactions.period_id', '=', $period->id)
          ->where('firstname', '=', $inputFirst);
    })
    ->orderBy('id', 'desc')
    ->get();

A little help would be great. Thanks

Upvotes: 0

Views: 233

Answers (1)

adolfotcar
adolfotcar

Reputation: 565

The issue with your code is that you are nesting the statements. The way you are doing Laravel is generating a SQL like this:

select * from `clients` where not exists 
(select * from `transactions` 
where `clients`.`id` = `transactions`.`client_id` 
and `name` = John)

But the actual SQL code you're looking for is:

select * from `clients` where not exists 
(select * from `transactions` 
where `clients`.`id` = `transactions`.`client_id`) 
and `name` = John)

For that your code should be:

$results = Client::whereDoesntHave('transactions')
                   ->where('firstname', '=', $inputFirst)
                   ->orderBy('id', 'desc')
                   ->get();

*I didn't include the transactions.period_id, coz I wasn't sure if where you're looking to have it. But if it's meant to be inside the second select, leave in the nested statement, if not leave outside.

Upvotes: 2

Related Questions