时间戳

首页 > 解决方案 > Laravel 查询生成器 - 在 WHERE 子句中使用选定的值而不重复代码

php - Laravel 查询生成器 - 在 WHERE 子句中使用选定的值而不重复代码

问题描述

我正在尝试将 RAW SQL 查询转换为 Laravel 查询生成器，但是我不知道如何在 WHERE 子句的选择语句中使用派生值。在 RAW SQL 中执行此操作时，我通过嵌套选择查询来执行此操作。

工作 RAW 查询

$query = DB::select("
        SELECT *
        FROM
          (SELECT DISTINCT a.postcode, b.id, b.name, d.product, e.distance product_distance,
                ROUND((SELECT 6371  * acos( cos( radians( $lat ) ) * cos( radians( a.latitude ) )
                                                         * cos( radians( $lng ) - radians(a.longitude) )
                                                         + sin( radians( $lat ) ) * sin( radians( a.latitude ) ) ) ),0) AS point_distance
                FROM postcodes_geo AS a
                JOIN businesses As b
                    ON b.postcode = a.postcode
                JOIN business_services As c
                    ON b.id = c.business_id
                JOIN business_subscriptions As d
                    ON b.id = d.business_id
                JOIN ref_business_products AS e
                    ON d.product = e.product
                WHERE b.deleted_at IS NULL
                AND c.service = '$service'
                AND c.deleted_at IS NULL) t
                WHERE t.point_distance <= t.product_distance
          ORDER BY point_distance
          ");

RAW SQL 嵌套查询

当前查询生成器查询

$query = DB::table('postcodes_geo')
            ->join('businesses', 'postcodes_geo.postcode', '=', 'businesses.postcode')
            ->join('business_services', 'businesses.id', '=', 'business_services.business_id')
            ->join('business_subscriptions', 'businesses.id', '=', 'business_subscriptions.business_id')
            ->join('ref_business_products', 'business_subscriptions.product', '=', 'ref_business_products.product')
            ->select(DB::raw("DISTINCT postcodes_geo.postcode, businesses.id, businesses.name, business_subscriptions.product, ref_business_products.distance,
                            ROUND((SELECT 6371  * acos( cos( radians( $lat ) ) * cos( radians( postcodes_geo.latitude ) )
                                                         * cos( radians( $lng ) - radians(postcodes_geo.longitude) )
                                                         + sin( radians( $lat ) ) * sin( radians( postcodes_geo.latitude ) ) ) ),0) AS point_distance"))
            ->whereNull('businesses.deleted_at')
            ->whereNull('business_services.deleted_at')
            ->when($services, function ($query, $services) {
                return $query->whereIn('business_services.service', explode(',', $services));
            })
            ->where(DB::raw("ROUND((SELECT 6371  * acos( cos( radians( $lat ) ) * cos( radians( postcodes_geo.latitude ) )
                                                         * cos( radians( $lng ) - radians(postcodes_geo.longitude) )
                                                         + sin( radians( $lat ) ) * sin( radians( postcodes_geo.latitude ) ) ) ),0)"), "<=", 'ref_business_products.distance')
            ->get();

如您所见，我不想重复查询的同一部分（尽管它确实有效）。 SQL 查询生成器 laravel

标签： phpsqllaravellaravel-query-builder

解决方案

您可以利用 MySQL 的重载HAVING子句，这将允许您引用point_distanceselect 子句中定义的别名：

$query = DB::table('postcodes_geo')
    ...
    ->having(DB::raw('point_distance <= ref_business_products.distance'))
    ->get();

注意：如果您使用的是早于 5.7.5 的 MySQL 版本，那么您需要关闭ONLY_FULL_GROUP_BY模式以使用重载HAVING，正如我在回答中所做的那样。

推荐阅读