Laravel：如何通过Eloquent模型实现客户与贷款表多表查询

Hey there! Let's walk through how to implement your query using Laravel's Eloquent ORM. First, we need to make sure our models are set up with the correct relationships since we're working across two tables: clients and loans.

Step 1: Define Model Relationships #

First, let's set up the relationships between the Client and Loan models. Typically, a client has multiple loans, and each loan belongs to one client.

Client Model (app/Models/Client.php) #

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\HasMany;

class Client extends Model
{
    // If your table name follows Laravel's plural convention, you can omit this line
    protected $table = 'clients';

    // A client has many loans
    public function loans(): HasMany
    {
        // If your foreign key isn't `client_id`, add it as the second parameter:
        // return $this->hasMany(Loan::class, 'custom_client_foreign_key');
        return $this->hasMany(Loan::class);
    }
}

Loan Model (app/Models/Loan.php) #

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsTo;

class Loan extends Model
{
    protected $table = 'loans';

    // A loan belongs to one client
    public function client(): BelongsTo
    {
        // Again, specify custom foreign key if needed:
        // return $this->belongsTo(Client::class, 'custom_client_foreign_key');
        return $this->belongsTo(Client::class);
    }
}

Step 2: Implement Query Functions #

Now we can build different query functions based on your specific needs. Here are common scenarios:

1. Get All Clients with Their Loans (Eager Loading) #

Use eager loading with with() to avoid the N+1 query problem, which is crucial for performance:

// Example in a controller or service class
public function getAllClientsWithLoans()
{
    // Fetch all clients including all their loans
    $clients = Client::with('loans')->get();

    // Optional: Filter loans (e.g., only active loans)
    $clientsWithActiveLoans = Client::with(['loans' => function ($query) {
        $query->where('status', '=', 'active');
    }])->get();

    return $clients; // or return $clientsWithActiveLoans
}

2. Get Loans for a Specific Client #

You can fetch loans directly from a client instance, or query the Loan model with client details:

public function getLoansForClient($clientId)
{
    // Option 1: Find the client first, then get their loans
    $client = Client::findOrFail($clientId);
    $loans = $client->loans;

    // Option 2: Query loans directly and include client info
    $loansWithClient = Loan::where('client_id', $clientId)->with('client')->get();

    return $loans; // or return $loansWithClient
}

3. Filter Clients Based on Loan Criteria #

Use whereHas() to filter clients that meet specific loan conditions:

public function getClientsWithLargeLoans()
{
    // Get clients who have at least one loan over $5000
    $clients = Client::whereHas('loans', function ($query) {
        $query->where('amount', '>', 5000);
    })->with('loans')->get();

    // Alternatively, fetch the large loans with client details
    $largeLoans = Loan::where('amount', '>', 5000)->with('client')->get();

    return $clients; // or return $largeLoans
}

4. Select Specific Fields Only #

If you don't need all columns from the tables, specify the fields to optimize performance:

public function getClientAndLoanSummary()
{
    $clients = Client::with(['loans' => function ($query) {
        $query->select('id', 'client_id', 'amount', 'issue_date');
    }])->select('id', 'name', 'email')->get();

    return $clients;
}

Key Notes #

• Foreign Keys: Ensure your loans table has a foreign key column (default is client_id) referencing the id column in the clients table.
• Table Names: If your tables don't follow Laravel's plural naming convention, explicitly set the $table property in your models.
• Eager Loading: Always use with() when accessing related data to prevent inefficient N+1 queries.

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