如何为Eloquent的Project与Developer模型建立关联以实现指定SQL查询？

Hey there! Let's get your Eloquent relationships set up between the Project and Developer models, then replicate that SQL query you need using Laravel's ORM tools.

First, let's clarify the relationship: one Developer has many Projects (since the project table uses dev_id to link to the id column in the developer table). We'll add the relationship methods to both models:

Update the Developer Model #

Add a projects() method to define the one-to-many relationship:

<?php
namespace App;
use Illuminate\Database\Eloquent\Model;

class Developer extends Model {
    protected $table = 'developer';
    protected $primaryKey = 'id';

    // One Developer has many Projects
    public function projects()
    {
        // Parameters: related model, foreign key on project table, primary key on developer table
        return $this->hasMany(Project::class, 'dev_id', 'id');
    }
}

Update the Project Model #

Add a developer() method to define the inverse many-to-one relationship:

namespace App;
use Illuminate\Database\Eloquent\Model;

class Project extends Model {
    protected $table = 'project';
    protected $primaryKey = 'project_id';

    // Many Projects belong to one Developer
    public function developer()
    {
        // Parameters: related model, foreign key on current project table, primary key on developer table
        return $this->belongsTo(Developer::class, 'dev_id', 'id');
    }
}

Your goal is to get each developer's name and the count of projects they're linked to. Here are two clean ways to do this:

Option 1: Use Eloquent's withCount() (Simplest Approach) #

This is the most "Laravel-native" way, and it handles the count logic automatically:

use App\Developer;

// Get each developer with their project count
$developerProjectCounts = Developer::select('developer_name')
    ->withCount('projects') // Adds a `projects_count` attribute to each Developer object
    ->get();

If you want a custom name for the count field instead of projects_count, you can define it explicitly:

$developerProjectCounts = Developer::select('developer_name')
    ->withCount(['projects as project_count'])
    ->get();

Option 2: Explicit JOIN (Matches Your Original SQL) #

If you prefer a query that mirrors your raw SQL structure, use this approach:

use App\Developer;
use Illuminate\Support\Facades\DB;

$developerProjectCounts = Developer::select(
        'developer.developer_name',
        DB::raw('count(project.dev_id) as project_count')
    )
    ->join('project', 'developer.id', '=', 'project.dev_id')
    ->groupBy('developer.id', 'developer.developer_name') // Required for strict SQL modes
    ->get();

If you ever need to count only specific projects (e.g., completed ones), you can add a constraint to the count:

$completedProjectCounts = Developer::select('developer_name')
    ->withCount(['projects as completed_projects' => function ($query) {
        $query->where('status', 'completed'); // Assume `project` has a `status` column
    }])
    ->get();

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