Laravel - Get records from one table that doesn't exist in another

Hello,

I have 3 tables - orders, items, bills. In the items table I have an 'order_id' column that connects item with a specific order. Each item from the items table can be linked to the bills table, where there is a column 'item_id'. Now I would like to retrieve all orders that have an item in the items table, but only if that item's id does not exist in the bills table. I tried something like this to start with :

$orders = Order::where('id', '>', 0) ->select('orders.*') ->join('items', 'orders.id', 'items.order_id') ->groupBy('orders.id')

The question of how to modify this query to retrieve only items that are not in the 'bills' table?

neilstee

5 years ago

Level 34

@dsadsadsa can you try:

$orders = Order::where('id', '>', 0)
	->select('orders.*')
	->join('items', 'orders.id', 'items.order_id')
	->where(function ($query) {
    	$query->select('id')
        	->from('bills')
        	->where('items.id', 'bills.item_id')
        	->doesntExist()
	)
	->groupBy('orders.id')

I didn't test it so you might get an error but you should get an idea.

neilstee

5 years ago

Level 34

actually, I think you can omit the doesntExist() function 🤔

MostafaGamal

5 years ago

Level 10

Try this:

$oreders = Order::whereHas('items', function (Builder $query) {
    $query->doesntHave('bills');
})->get();

Please or to participate in this conversation.