Laravel嵌套关联查询实现产品跨关联门店最小距离计算与排序

合并两类关联门店的距离计算实现 #

你需要将产品直接关联的门店和产品关联连锁下的所有门店合并去重后，统一计算最小距离，修改后的代码如下：

$productList = $productList->withCount(['stores as distance' => function($query) use ($centerPoint, $distance) {
    // 合并两类关联门店的ID，Union自带去重能力
    $relatedStoresSub = DB::table('product_stores')
        ->whereColumn('product_stores.product_id', 'products.id')
        ->select('product_stores.store_id')
        ->union(
            DB::table('product_chains')
                ->join('stores', 'stores.chain_id', '=', 'product_chains.chain_id')
                ->whereColumn('product_chains.product_id', 'products.id')
                ->select('stores.id as store_id')
        );

    // 基于合并后的门店计算最小距离，使用参数绑定避免SQL注入
    $query->select(DB::raw("COALESCE(MIN( GLength( LineStringFromWKB( LineString( location, GeomFromText(?) ) ) ) ), 999999)"))
        ->joinSub($relatedStoresSub, 'related_stores', 'related_stores.store_id', '=', 'stores.id')
        ->setBindings([$centerPoint]);
}])
->whereRaw("MATCH (name) AGAINST (? IN BOOLEAN MODE)", [$yourSearchKeyword]) // 补充你需要的全文检索条件
->having('distance', '<', $distance)
->orderBy('distance', 'asc');

注意：原代码直接拼接$centerPoint存在SQL注入风险，修改后使用参数绑定更安全，你需要自行替换代码中的$yourSearchKeyword为你的实际检索关键词。

性能优化方案 #

• 空间索引优化：给Stores表的location字段添加空间索引，查询时先通过MBR范围过滤掉超出距离阈值的门店，再做精确距离计算，可大幅减少计算量。示例过滤条件：where MBRContains(ST_GeomFromText(CONCAT('POLYGON((', $minLon, ' ', $minLat, ',', $maxLon, ' ', $minLat, ',', $maxLon, ' ', $maxLat, ',', $minLon, ' ', $maxLat, ',', $minLon, ' ', $minLat, '))')), location)。
• 关联字段索引：给中间表product_stores的product_id、store_id，product_chains的product_id、chain_id，stores表的chain_id字段添加普通索引，加速关联查询效率。
• 冗余存储优化：如果产品关联门店、关联连锁的更新频率较低，可以单独创建product_all_stores中间表，后台同步所有产品对应的关联门店ID，查询时直接关联该表即可，避免每次查询都执行Union关联逻辑。
• 全文检索优化：给Products表的name字段添加全文索引，保证MATCH AGAINST检索不会触发全表扫描。
• 距离计算简化：如果对距离精度要求不高，可以将空间类型的location拆分为单独的lon（经度）、lat（纬度）字段，使用Haversine公式计算经纬度距离，计算效率高于空间函数。

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