Laravel中如何从多表筛选显示区域允许的轮毂类型？

嘿，我仔细看了你的代码和需求——要只显示指定区域允许的轮毂类型，咱们来把这个查询捋得更高效、逻辑更清晰点。

首先说下原代码里的几个小问题：

• 你注释里写的“join users table”明显是笔误啦，应该是要关联zones和wheels，判断轮毂类型是否在区域允许的范围内；
• 用一堆orOn做关联条件容易产生不必要的笛卡尔积，而且逻辑上有点绕，不如直接用筛选条件更直观；
• 你已经通过zone_wheels关联了区域，这里可以把区域相关的条件整合得更紧凑。

方案一：先获取允许的类型ID，再筛选 #

这个方案逻辑清晰，适合需要复用允许类型ID的场景：

<?php
// 先拿到当前区域允许的所有轮毂类型ID
$allowedWheelTypes = DB::table('zones')
    ->where('id', $this->id)
    ->first([
        'wheels_type_1', 'wheels_type_2', 'wheels_type_3', 'wheels_type_4',
        'wheels_type_5', 'wheels_type_6', 'wheels_type_7', 'wheels_type_8',
        'wheels_type_9', 'wheels_type_10', 'wheels_type_11', 'wheels_type_12',
        'wheels_type_13', 'wheels_type_14', 'wheels_type_15', 'wheels_type_16',
        'wheels_type_17'
    ]);

// 过滤掉空的类型字段（比如有些区域只启用了部分类型）
$allowedTypeIds = array_filter($allowedWheelTypes ? (array)$allowedWheelTypes : []);

$wheels = DB::table('wheels')
    ->join('users', 'users.id', '=', 'wheels.user_id')
    ->join('wheels_types', 'wheels_types.id', '=', 'wheels.wheels_type_id')
    ->join('zone_wheels', function ($join) {
        $join->on('zone_wheels.wheels_id', '=', 'wheels.id')
             ->where('zone_wheels.zone_id', $this->id)
             ->where('zone_wheels.is_approved', true)
             ->whereNull('zone_wheels.deleted_at');
    })
    // 筛选出符合区域允许类型的轮毂
    ->whereIn('wheels.wheels_type_id', $allowedTypeIds)
    ->select(
        'wheels.id AS wheels_id',
        'wheels.user_id AS user_id',
        'wheels.name AS name',
        'wheels.price AS price',
        'users.email AS contact',
        'wheels.city AS city',
        'wheels.model AS model',
        'users.fb_certified AS fb_certified',
        'users.paid_certified AS paid_certified',
        'wheels_types.name_en AS type',
        'wheels.status AS status',
        'wheels.zipcode AS zipcode',
        'wheels.base_city AS base_city',
        'wheels.description AS description'
    )
    ->orderBy('wheels.updated_at', 'desc')
    ->paginate(12);
?>

这个方案的优点： #

• 逻辑拆分清晰，先确定允许的类型，再筛选轮毂，可读性更强；
• array_filter自动去掉空字段，避免无效的筛选条件；
• 把zone_wheels的条件移到join闭包里，让关联逻辑更紧凑。

方案二：用子查询直接筛选（无需单独查询区域类型） #

如果不需要复用允许类型ID，直接在主查询里用子查询搞定：

<?php
$wheels = DB::table('wheels')
    ->join('users', 'users.id', '=', 'wheels.user_id')
    ->join('wheels_types', 'wheels_types.id', '=', 'wheels.wheels_type_id')
    ->join('zone_wheels', function ($join) {
        $join->on('zone_wheels.wheels_id', '=', 'wheels.id')
             ->where('zone_wheels.zone_id', $this->id)
             ->where('zone_wheels.is_approved', true)
             ->whereNull('zone_wheels.deleted_at');
    })
    // 用子查询判断轮毂类型是否在当前区域允许的范围内
    ->whereRaw('wheels.wheels_type_id IN (
        SELECT wheels_type_1, wheels_type_2, wheels_type_3, wheels_type_4,
               wheels_type_5, wheels_type_6, wheels_type_7, wheels_type_8,
               wheels_type_9, wheels_type_10, wheels_type_11, wheels_type_12,
               wheels_type_13, wheels_type_14, wheels_type_15, wheels_type_16,
               wheels_type_17
        FROM zones WHERE id = ?
    )', [$this->id])
    ->select(
        'wheels.id AS wheels_id',
        'wheels.user_id AS user_id',
        'wheels.name AS name',
        'wheels.price AS price',
        'users.email AS contact',
        'wheels.city AS city',
        'wheels.model AS model',
        'users.fb_certified AS fb_certified',
        'users.paid_certified AS paid_certified',
        'wheels_types.name_en AS type',
        'wheels.status AS status',
        'wheels.zipcode AS zipcode',
        'wheels.base_city AS base_city',
        'wheels.description AS description'
    )
    ->orderBy('wheels.updated_at', 'desc')
    ->paginate(12);
?>

这个方案的优点： #

• 不需要额外的查询步骤，一次搞定；
• 同样会自动忽略空的类型字段，不会影响筛选结果。

长期优化建议：调整数据库结构 #

当前zones表用17个字段存储允许的轮毂类型，这种结构扩展性很差（以后要加新类型就得改表结构）。建议改成关联表结构：

创建zone_allowed_wheel_types表，字段包括：

• id（主键）
• zone_id（关联zones表的ID）
• wheel_type_id（关联wheels_types表的ID）

这样每个区域允许的轮毂类型就是一条记录，查询会变得更简洁：

<?php
$wheels = DB::table('wheels')
    ->join('users', 'users.id', '=', 'wheels.user_id')
    ->join('wheels_types', 'wheels_types.id', '=', 'wheels.wheels_type_id')
    ->join('zone_wheels', function ($join) {
        $join->on('zone_wheels.wheels_id', '=', 'wheels.id')
             ->where('zone_wheels.zone_id', $this->id)
             ->where('zone_wheels.is_approved', true)
             ->whereNull('zone_wheels.deleted_at');
    })
    // 直接关联新的允许类型表
    ->join('zone_allowed_wheel_types', function ($join) {
        $join->on('zone_allowed_wheel_types.wheel_type_id', '=', 'wheels.wheels_type_id')
             ->where('zone_allowed_wheel_types.zone_id', $this->id);
    })
    ->select(
        'wheels.id AS wheels_id',
        'wheels.user_id AS user_id',
        'wheels.name AS name',
        'wheels.price AS price',
        'users.email AS contact',
        'wheels.city AS city',
        'wheels.model AS model',
        'users.fb_certified AS fb_certified',
        'users.paid_certified AS paid_certified',
        'wheels_types.name_en AS type',
        'wheels.status AS status',
        'wheels.zipcode AS zipcode',
        'wheels.base_city AS base_city',
        'wheels.description AS description'
    )
    ->orderBy('wheels.updated_at', 'desc')
    ->paginate(12);
?>

这种结构不仅扩展性强，查询逻辑也更符合数据库设计的最佳实践，后期维护起来更省心。

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