Convert multiple php code conditions to an SQL command

Any of the query can be used with scope as suggested by Ozan

Query #1

SELECT
    A.id as id,
    CASE
        WHEN A.is_canceled THEN 'canceled'
        WHEN NOT EXISTS (SELECT * FROM B) THEN 'no_B'
        WHEN (SELECT count(*) FROM B WHERE A.id = B.trip_id) = (SELECT count(*) FROM B WHERE status = 'open' and A.id = B.trip_id) THEN 'open'
        WHEN (SELECT count(*) FROM B WHERE A.id = B.trip_id) = (SELECT count(*) FROM B WHERE status = 'finished' and A.id = B.trip_id) THEN 'finished'
        WHEN (SELECT count(*) FROM B WHERE A.id = B.trip_id) = (SELECT count(*) FROM B WHERE status = 'started' and A.id = B.trip_id) THEN 'started'
        ELSE 'Unknown'
    END as status
FROM
    A;

Query #2

with m As (
  SELECT
    A.id as id,
    A.is_canceled AS is_canceled,
    COUNT(*) AS total,
    COUNT(CASE WHEN status = 'open' then 1 ELSE NULL END) AS open,
    COUNT(CASE WHEN status = 'finished' then 1 ELSE NULL END) AS finished,
    COUNT(CASE WHEN status = 'started' then 1 ELSE NULL END) AS started
  FROM B JOIN A ON B.trip_id = A.id
  GROUP BY A.id, A.is_canceled
)

SELECT
  A.id,
  CASE
    WHEN A.is_canceled THEN 'cancelled'
    WHEN (select total from m where A.id = m.id) = (select open from m where A.id = m.id) THEN 'open'
    WHEN (select total from m where A.id = m.id) = (select finished from m where A.id = m.id) THEN 'finished'
    WHEN (select total from m where A.id = m.id) = (select started from m where A.id = m.id) THEN 'started'
    ELSE 'Unknown'
    END AS status
from A;

Query #3

SELECT
  id,
  CASE
    WHEN is_canceled THEN 'cancelled'
    WHEN total = open THEN 'open'
    WHEN total = finished THEN 'finished'
    WHEN total = started THEN 'started'
    ELSE 'Unknown'
  END AS status
FROM (
  SELECT
    A.id as id,
    A.is_canceled AS is_canceled,
    COUNT(*) AS total,
    COUNT(CASE WHEN status = 'open' then 1 ELSE NULL END) AS open,
    COUNT(CASE WHEN status = 'finished' then 1 ELSE NULL END) AS finished,
    COUNT(CASE WHEN status = 'started' then 1 ELSE NULL END) AS started
  FROM B JOIN A ON B.trip_id = A.id
  GROUP BY A.id, A.is_canceled
) t ;

Execution time for 10k records.

View on DB Fiddle

If you want a custom attribute to be appended to the "trips" objects when queried: I recommend using a custom attribute that you can "always" load with your models, or on-demand.

You can define a trip_status attribute, and rename your method to getTripStatusAttribute and it'll be added to the resulting trips queried.

Note that this will NOT affect the main trips SQL query. But will add a travel query for each resulting trips, if travel are not eager loaded. And can be resource greedy as you use a SQL count on a relation every time a trip is loaded (always or on-demand as said).

You can also query your trips using withCount to eager load the travel count with the trip query. And base the trip_status accessor method on the queried count attribute. Still can belong with a heavy database, so prefer to load the attribute only when needed.

You can use withCount with conditions (also see), and use it in accessor:

$posts = Post::withCount('upvotes')
         ->having('upvotes_count', '>', 5)
         ->get();

Second note: you can add the travel count to the trip query result, but I don't think that you can add a trip_status only using SQL. A Laravel attribute would be the way to go, by taking care of performance (load travel if needed relationLoaded, use withCount...)

Edit 2: after seeing your comment I am using Yajra Datatables and creating a custom filter for the non-exisiting dynamic status column

you cannot make SQL query against the trip_status as it's not a database. You could filter a collection with ALL the trips on the trip_status, but it's a no go for performance: you need a eloquent/SQL query. But as I explained, you can query with custom withCount, and so reproduce your needs.

So for the GET data part, I would use the custom attribute.

But for the query part, in a datatable, I would filter my query based on the frontend filters. So you'll need a query based on multiple eager loaded counts (check all the link snippets logic to "mix" them).

Here what a query could look like (not tested fully, but tested the eloquent/SQL part to check for results):

 //here you can add other query filters
 $trips = Trip::query();

        if($frontend_filter === 'canceled') {
            $trips = $trips->where('is_canceled', 1);
        }
        elseif($frontend_filter === 'open') {
            $trips = $trips::withCount([
                    'travels',
                    'travels AS opened_travel_count' => function ($query) {
                        $query->where('travels.status', '=', 'open');
                    }
                ])
                ->having('travels_count', '=', 'opened_travel_count'); //careful: will match 0   
        }
        elseif($frontend_filter === 'finished') {
            $trips = $trips::withCount([
                    'travels',
                    'travels AS finished_travel_count' => function ($query) {
                        $query->where('travels.status', '=', 'finished');
                    }
                ])
                ->having('travels_count', '>', 0) //could use whereHas travels
                ->having('travels_count', '=', 'finished_travel_count');
        }
        elseif($frontend_filter === 'started') {
            $trips = $trips::withCount([
                    'travels', //useless
                    'travels AS started_travel_count' => function ($query) {
                        $query->where('travels.status', '=', 'started');
                    }
                ])
                ->having('started_travel_count', '>', 0); //could use whereHas travels where travel
        }


        //pagination, global eager loads, selects etc...
        //you have the pre-filtered query to pass for pagination and stuff
        //$trips = $trips->get(); //this gets all and should NOT be used for a datatable