时间戳

首页 > 解决方案 > 将多个php代码条件转换为SQL命令

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

问题描述

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

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列动态附加到查询结果中。

标签： phpsqllaravel

解决方案

您可以添加一个原始选择，这样您就可以保持代码的性能和 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它假定我们不想获取*并且只获取的范围内使用该方法status。为了防止这种情况，您可以简单地说：

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

推荐阅读