如何在Laravel中对Users表自连接实现销售报表查询

场景说明 #

项目中所有角色（店铺、卖家、管理员）都存储在Users表，字段包括id、name、store。Contract表记录销售数据，需要生成包含卖家/管理员对应店铺名称的销售报表：

• 卖家的store字段关联对应店铺的id
• 店铺的store字段为null
• 管理员关联多个店铺，需保留其数据并展示对应店铺名称

原代码问题 #

原查询仅关联了卖家的用户信息，未获取对应的店铺名称，也未处理管理员关联多店铺的场景。

解决方案 #

1. 基础版：卖家关联单个店铺，保留管理员数据

使用leftJoin自连接Users表作为店铺表，避免过滤掉无直接店铺关联的管理员数据：

$ventas = Contract::select(
        'contracts.*',
        'vendedores.name as vendedor_name',
        'vendedores.store as vendedor_store_id',
        'tiendas.name as tienda_name'
    )
    ->join('users as vendedores', 'contracts.vendedor', '=', 'vendedores.id')
    ->leftJoin('users as tiendas', function($join) {
        $join->on('vendedores.store', '=', 'tiendas.id');
        // 允许管理员无直接店铺关联，保留数据
    })
    ->orderBy('contracts.id', 'DESC')
    ->get();

• 给关联表起别名（vendedores、tiendas）避免字段冲突
• leftJoin确保管理员数据不会因为无店铺关联被过滤

2. 管理员关联多店铺（store字段为逗号分隔ID）

如果管理员的store字段存储多个店铺ID（如1,2,3），使用FIND_IN_SET匹配并合并店铺名称：

$ventas = Contract::select(
        'contracts.*',
        'vendedores.name as vendedor_name',
        DB::raw('GROUP_CONCAT(tiendas.name SEPARATOR ", ") as tiendas_names')
    )
    ->join('users as vendedores', 'contracts.vendedor', '=', 'vendedores.id')
    ->leftJoin('users as tiendas', function($join) {
        $join->whereRaw('FIND_IN_SET(tiendas.id, vendedores.store)');
    })
    ->groupBy('contracts.id')
    ->orderBy('contracts.id', 'DESC')
    ->get();

• GROUP_CONCAT将多个店铺名称合并为逗号分隔的字符串
• groupBy确保每个合同仅返回一条记录

3. 推荐方案：多对多关联（管理员与店铺）

如果管理员和店铺是多对多关系，建议创建中间表user_store（字段user_id、store_id），通过中间表关联查询：

$ventas = Contract::select(
        'contracts.*',
        'vendedores.name as vendedor_name',
        DB::raw('GROUP_CONCAT(tiendas.name SEPARATOR ", ") as tiendas_names')
    )
    ->join('users as vendedores', 'contracts.vendedor', '=', 'vendedores.id')
    ->leftJoin('user_store', 'vendedores.id', '=', 'user_store.user_id')
    ->leftJoin('users as tiendas', 'user_store.store_id', '=', 'tiendas.id')
    ->groupBy('contracts.id')
    ->orderBy('contracts.id', 'DESC')
    ->get();

这种设计符合数据库规范，性能更优，也便于后续扩展。

内容的提问来源于stack exchange，提问作者joseph mora rojas