CODEWITHSUNDEEP
phpsqllaraveleloquent
Daniel
Daniel

Reputation: 205

Eloquent or SQL way on how to check if child table has certain multiple values

What is the Eloquent or SQL way of checking if a table has a value based on the ID given?

Sample customer's table:

id   name    created_at
1   test_1   01/01/2019
2   test_2   01/24/2019
3   test_3   01/25/2019

Sample purchases table:

id   customer_id   status      created_at
1         2        paid        02/01/2019
2         2        paid        02/02/2019
3         2        unpaid      02/03/2019
4         2        cancelled   02/03/2019
5         3        paid        02/03/2019
6         1        paid        02/03/2019

what I want to achieve is to check if there's a customer with unpaid and cancelled status

currently my code is like this:

Customers::with('purchases')->get();

but I want it to only get the customers with unpaid and cancelled status from purchases table

Upvotes: 0

Views: 188

Answers (3)

user14063792468
user14063792468

Reputation: 956

select name
from customers c
inner join purchases p on c.id = p.id
where c.id = CustomerId
and status in (cancelled, unpaid)
group by customer.id

Upvotes: 0

forpas
forpas

Reputation: 164089

With SQL you need to group by customer_id and the condition count(distinct status) = 2 in the having clause will get you the customers you want:

select customer_id
from purchases
where status in ('unpaid', 'cancelled')
group by customer_id
having count(distinct status) = 2

If you want customers with only 'unpaid' and 'cancelled' status and no other status:

select customer_id
from purchases
group by customer_id
having 
  count(distinct status) = 2
  and 
  sum(case when status not in ('unpaid', 'cancelled') then 1 else 0 end) = 0

Upvotes: 1

Daniel
Daniel

Reputation: 205

Solved it by doing

doesntHave()

of Laravel Eloquent.

in the customer's model, I have put:

public function unpaid_and_cancelled_purchases() {
   return $this->hasMany('App\Purchase', 'customer_id', 'id')
               ->whereIn('status', ['unpaid', 'cancelled']);
}

in my controller, I have put it like this:

$customers = Customers::doesntHave('unpaid_and_cancelled_purchases')->get();

Upvotes: 0

Related Questions