Convert multiple php code conditions to an SQL command

I am working on a php project where I need a dynamic column to be part of the sql query result, I have a trips table where each one might have many travels, the trip status is always calculated dynamically according to the several conditions below:

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;
    }

I need to convert the function below to an SQL function so that the status column is dynamically appended to the query result.

Answer

You can add a raw select, this way you keep the performance and PHP side of the code clean.

Only thing you need to be careful is indexing the fields you’re using for conditionals.

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`;

A simple equivalent of the above query could be written like this in 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);

And then if you’re planning to use this often, you could extract it to a scope inside your model.

/**
 * Query scope withStatus.
 *
 * @param  IlluminateDatabaseEloquentBuilder
 * @return IlluminateDatabaseEloquentBuilder
 */
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`
        "),
    );
}

Above code will let you run the select wherever you want easily BUT it has a catch.

You would need to specify the fields you want from database since we use the addSelect method inside the scope it assumes that we do not want to get * and only gets status. To prevent this you can simply say:

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