php - 将多个php代码条件转换为SQL命令

Question

我正在开发一个 php 项目，其中需要一个动态列作为 sql 查询结果的一部分，我有一个 trips 表，其中每个人可能有很多 旅行，行程状态始终根据以下几个条件动态计算:

public static function getTripStatus($item)
    {
        $status = 'Unknown';
        if ($item->is_canceled == true) {
            $status = 'canceled';
        } elseif ($item->travels->count() == $item->travels->where('status', 'open')->count()) {
            $status = 'open';
        } else if ($item->travels->count() > 0 && $item->travels->count() == $item->travels->where('status', 'finished')->count()) {
            $status = 'finished';
        } elseif ($item->travels->where('status', 'started')->count() > 0) {
            $status = 'started';
        }

        return $status;
    }

我需要将下面的函数转换为 SQL 函数，以便将 status 列动态附加到查询结果中。

Answer 1

您可以添加原始选择，这样可以保持代码的性能和 PHP 方面的干净。

您唯一需要注意的是对用于条件的字段建立索引。

SELECT
    trips.id,
    trips.name,
    CASE
        WHEN `trips`.`is_canceled` THEN "canceled"
        WHEN NOT EXISTS (SELECT * FROM `travels` WHERE trips.id = travels.trip_id) THEN "no_travels"
        WHEN (SELECT count(*) FROM `travels` WHERE trips.id = travels.trip_id) = (SELECT count(*) FROM `travels` WHERE `status` = "open" and trips.id = travels.trip_id) THEN "open"
        WHEN (SELECT count(*) FROM `travels` WHERE trips.id = travels.trip_id) = (SELECT count(*) FROM `travels` WHERE `status` = "finished" and trips.id = travels.trip_id) THEN "finished"
        WHEN (SELECT count(*) FROM `travels` WHERE trips.id = travels.trip_id) = (SELECT count(*) FROM `travels` WHERE `status` = "started" and trips.id = travels.trip_id) THEN "started"
        ELSE "Unknown"
    END as `status`
FROM
    `trips`;

在 Laravel 中，上面的查询的简单等价形式可以这样编写:

$countSubQuery = "SELECT count(*) FROM `travels` WHERE trips.id = travels.trip_id";

    $trips = Trip::select([
        'id',
        \DB::raw("
            CASE
                WHEN `trips`.`is_canceled` THEN 'canceled'
                WHEN NOT EXISTS (SELECT * FROM `travels` WHERE trips.id = travels.trip_id) THEN 'no_travels'
                WHEN ({$countSubQuery}) = ({$countSubQuery} and `status` = 'open') THEN 'open'
                WHEN ({$countSubQuery}) = ({$countSubQuery} and `status` = 'finished') THEN 'finished'
                WHEN ({$countSubQuery}) = ({$countSubQuery} and `status` = 'started') THEN 'started'
                ELSE 'Unknown'
            END as `status`
        "),
    ])->get();

    dd($trips);

然后，如果您打算经常使用它，您可以将其提取到模型内的范围。

/**
 * Query scope withStatus.
 *
 * @param  \Illuminate\Database\Eloquent\Builder
 * @return \Illuminate\Database\Eloquent\Builder
 */
public function scopeWithStatus($query)
{
    $countSubQuery = "SELECT count(*) FROM `travels` WHERE trips.id = travels.trip_id";

    return $query->addSelect(
        \DB::raw("
            CASE
                WHEN `trips`.`is_canceled` THEN 'canceled'
                WHEN NOT EXISTS (SELECT * FROM `travels` WHERE trips.id = travels.trip_id) THEN 'no_travels'
                WHEN ({$countSubQuery}) = ({$countSubQuery} and `status` = 'open') THEN 'open'
                WHEN ({$countSubQuery}) = ({$countSubQuery} and `status` = 'finished') THEN 'finished'
                WHEN ({$countSubQuery}) = ({$countSubQuery} and `status` = 'started') THEN 'started'
                ELSE 'Unknown'
            END as `status`
        "),
    );
}

上面的代码可以让你轻松地在任何你想要的地方运行选择，但它有一个问题。

您需要指定您想要从数据库中获取的字段，因为我们在范围内使用了 addSelect 方法，它假定我们不想获取 * 并且只获取状态。为了防止这种情况，你可以简单地说:

Trip::select('*')->withStatus()->get();