如何在Laravel 8中使用EXCEPT语句实现指定的差集查询

Got it, let's walk through translating your native SQL EXCEPT query into Laravel 8's query builder syntax. Your goal is to pull customer IDs and full names for those who don't have any associated orders—let's make that happen with the framework's built-in methods.

First, let's break down your original SQL into two parts that we can replicate with the query builder:

1. The base query: Select all customers' IDs and full names
2. The exclusion query: Select customers who do have orders (so we can exclude them via EXCEPT)

Step 1: Build the base customer query #

This is straightforward—we just select the required columns from the Customers table:

$allCustomers = DB::table('Customers')
    ->select('id', 'fullname');

Step 2: Build the "customers with orders" query #

Next, we need the subset of customers that have orders. This requires joining the orders table to Customers, selecting the same columns, and using distinct() to avoid duplicate entries (just like your native SQL):

$customersWithOrders = DB::table('orders')
    ->join('Customers', 'Customers.id', '=', 'orders.Customers_id')
    ->select('Customers.id', 'Customers.fullname')
    ->distinct();

Step 3: Combine with EXCEPT #

Laravel 8's query builder has a dedicated except() method that mirrors the native SQL EXCEPT clause. We just call this on our base query, passing in the exclusion query, then fetch the results:

$customersWithoutOrders = $allCustomers->except($customersWithOrders)->get();

Using Eloquent (if you have a Customer model) #

If you're using Eloquent (which is common in Laravel), you can adapt this to use your Customer model instead of the query builder directly:

use App\Models\Customer;

$customersWithOrders = DB::table('orders')
    ->join('Customers', 'Customers.id', '=', 'orders.Customers_id')
    ->select('Customers.id', 'Customers.fullname')
    ->distinct();

$customersWithoutOrders = Customer::select('id', 'fullname')
    ->except($customersWithOrders)
    ->get();

Alternative: Using whereDoesntHave (more Laravel-idiomatic) #

While you asked specifically for EXCEPT, it's worth noting that Laravel has a more intuitive way to get models without related records using whereDoesntHave()—this achieves the same result and is often easier to read:

$customersWithoutOrders = Customer::select('id', 'fullname')
    ->whereDoesntHave('orders')
    ->get();

(Note: This assumes you've set up a orders relationship in your Customer model, like public function orders() { return $this->hasMany(Order::class, 'Customers_id'); })

内容的提问来源于stack exchange，提问作者Akina