Laravel新手求助：如何从非直接关联的表中获取数据？

我是Laravel新手，对Eloquent及其查询语法不太了解。我想在ProposalController中获取tb_product表的product_name字段，表间关联关系如下：
我了解到若目标表存在关联关系可使用whereHas，但该场景下是否适用？

tb_proposal
-----------
id // primary key
customer_name
grand_total

tb_detail_proposal
------------------
id // primary key
proposal_id // foreign key to tb_proposal.id
product_id // foreign key to tb_product.id
qty
price

tb_product
----------
id // primary key
product_name
stock
price

当前ProposalController.php中的代码：

class ProposalController extends Controller
{
    public function index()
    {
        $data = Proposal::latest();
        if (request('search')) {
            $data->where('customer_name', 'like', '%'.request('search').'%')
                ->orWhereHas('tb_product', function (Builder $query) {
                    $query->where('product_name', 'like', '%'.request('search').'%')
                });
        }

        $view_data = [
            'page_title' => 'Browse Proposals',
            'active' => 'proposal',
            'data' => $data->get()
        ];
        return view('proposal.index', $view_data);
    }
}

解决方案 #

你的场景里whereHas完全适用，但不能直接关联到Product——因为Proposal和Product是通过中间表tb_detail_proposal间接关联的，必须先在模型里定义好正确的关联关系，才能用whereHas做嵌套查询。

步骤1：定义Eloquent模型关联 #

先确保你有三个对应表的模型：Proposal、DetailProposal、Product，然后在模型里配置关联：

Proposal模型（对应tb_proposal）

namespace App\Models;

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

class Proposal extends Model
{
    protected $table = 'tb_proposal'; // 表名不符合Laravel默认规范，需手动指定

    // 一个Proposal对应多个明细
    public function detailProposals(): HasMany
    {
        return $this->hasMany(DetailProposal::class, 'proposal_id', 'id');
    }

    // 间接关联Product：通过明细表关联
    public function products(): HasManyThrough
    {
        return $this->hasManyThrough(
            Product::class,
            DetailProposal::class,
            'proposal_id', // 明细表里关联Proposal的外键
            'id', // Product的主键
            'id', // Proposal的主键
            'product_id' // 明细表里关联Product的外键
        );
    }
}

DetailProposal模型（对应tb_detail_proposal）

namespace App\Models;

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

class DetailProposal extends Model
{
    protected $table = 'tb_detail_proposal';

    // 归属到某个Proposal
    public function proposal(): BelongsTo
    {
        return $this->belongsTo(Proposal::class, 'proposal_id');
    }

    // 归属到某个Product
    public function product(): BelongsTo
    {
        return $this->belongsTo(Product::class, 'product_id');
    }
}

Product模型（对应tb_product）

namespace App\Models;

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

class Product extends Model
{
    protected $table = 'tb_product';

    // 一个Product对应多个明细
    public function detailProposals(): HasMany
    {
        return $this->hasMany(DetailProposal::class, 'product_id');
    }
}

步骤2：修改Controller的查询逻辑 #

现在可以用两种方式实现搜索，选一种就行：

方式1：通过中间明细关联查询

use Illuminate\Database\Eloquent\Builder;

class ProposalController extends Controller
{
    public function index()
    {
        $data = Proposal::latest();

        if (request('search')) {
            $searchTerm = '%' . request('search') . '%';
            // 用闭包包裹where和orWhereHas，避免逻辑混乱
            $data->where(function (Builder $query) use ($searchTerm) {
                // 搜索客户名称
                $query->where('customer_name', 'like', $searchTerm)
                // 搜索关联的产品名称：通过明细关联到Product
                ->orWhereHas('detailProposals', function (Builder $subQuery) use ($searchTerm) {
                    $subQuery->whereHas('product', function (Builder $productQuery) use ($searchTerm) {
                        $productQuery->where('product_name', 'like', $searchTerm);
                    });
                });
            });
        }

        // 预加载关联，避免N+1查询（如果要在视图显示产品名称的话）
        $view_data = [
            'page_title' => 'Browse Proposals',
            'active' => 'proposal',
            'data' => $data->with('detailProposals.product')->get()
        ];

        return view('proposal.index', $view_data);
    }
}

方式2：直接用hasManyThrough关联查询

use Illuminate\Database\Eloquent\Builder;

class ProposalController extends Controller
{
    public function index()
    {
        $data = Proposal::latest();

        if (request('search')) {
            $searchTerm = '%' . request('search') . '%';
            $data->where(function (Builder $query) use ($searchTerm) {
                $query->where('customer_name', 'like', $searchTerm)
                // 直接用定义好的products关联
                ->orWhereHas('products', function (Builder $productQuery) use ($searchTerm) {
                    $productQuery->where('product_name', 'like', $searchTerm);
                });
            });
        }

        $view_data = [
            'page_title' => 'Browse Proposals',
            'active' => 'proposal',
            'data' => $data->with('products')->get()
        ];

        return view('proposal.index', $view_data);
    }
}

额外提示 #

• 必须用where(function($query) { ... })包裹查询条件，否则orWhereHas会和外层的查询逻辑冲突，导致不符合预期的结果。
• 如果要在视图里显示产品名称，比如遍历Proposal的明细和对应产品，记得用with()预加载关联，避免执行大量重复查询。

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