Zapytanie do Laravela

Witam,
Jak zapissac taki zapytanie do llaravela wykorzystujac modele?

SELECT 
	o.* 
FROM orders o 
WHERE o.last_status_id = 42 
	AND (
		   (select count(*) from orders_invoices oi where oi.order_id = o.id) = 0 
		OR (select count(*) from orders_invoices oi where oi.order_id = o.id AND oi.type_invoice_id != 5) = 0
	)

https://laravel.com/docs/5.7/eloquent-relationships -> Querying Relationship Absence

2 modele - *zamówienie *oraz *faktura *i potem metoda *whereDoesntHave *.

napisałem takie zapytanie lecz mam problem ze sprawdzeniem tego wyniku podzapytan = 0. Czy moze ktos pomoc?

Order::where('last_status_id', 42)
                        ->whereHas('invoices', function ($query)
                            {
                                $query->select(DB::raw('count(*) as count'))
                                    ->where('orders_invoices.order_id', 'orders.id')
                                    ->orWhere(function($q){
                                        $q->select(DB::raw('count(*) as count'))
                                          ->where('orders_invoices.order_id', 'orders.id')
                                          ->where('orders_invoices.type_invoice_id', '!=', 5);
                                    });
                            })->get();

Czytałeś link, który Ci wyżej wkleiłem? Bo tam masz odpowiedź.

$orders = Order::where('last_status_id', 42)
                ->select(DB::raw('count(*) as count'))
                            ->whereDoesntHave('invoices', function ($query) {
                                $query->where('orders_invoices.order_id', 'orders.id');
                            })->orWhere(function($q){
                                $q->select(DB::raw('count(*) as count'))
                                  ->where('orders_invoices.order_id', 'orders.id')
                                  ->where('orders_invoices.type_invoice_id', '!=', 5);
                            })->get(); 

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'orders_invoices.order_id' in 'where clause' (SQL: select count(*) as count from `orders` where (`last_status_id` = 42 and not exists......

Po pierwsze, to możesz uprościć swoje zapytanie do:

SELECT o.*
FROM orders o
WHERE o.last_status_id = 42
	  AND (
		  select count(*)
		  from orders_invoices oi
		  where oi.order_id = o.id AND oi.type_invoice_id != 5
	  ) = 0;

Jeżeli masz warunek, że nie istnieją faktury lub nie istnieją faktury z typem różnym od 5, to ponieważ łączysz to lub, to pierwszy warunek nie jest Ci potrzebny.

Sam SQL można jeszcze zmodyfikować używająć NOT EXISTS zamiast (SELECT ... ) = 0

SELECT o.*
FROM orders o
WHERE o.last_status_id = 42
	  AND NOT EXISTS(
		  select count(*)
		  from orders_invoices oi
		  where oi.order_id = o.id AND oi.type_invoice_id != 5
	  );

W samym laravelu można to zapisać tak:

Orders::where('last_status_id', '!=', 42)
	->whereDoesntHave('orders_invoices', function ($query) {
		$query->where('type_invoice_id', '!=', 5);
	})
	->get();