我正在做一个php项目，我需要一个动态列作为sql查询结果的一部分，我有一个旅行表，每个旅行表可能有很多旅行，旅行状态总是根据以下几个条件：
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函数，以便动态地将状态列附加到查询结果中。
您可以添加一个原始select，这样可以保持代码的性能和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`
"),
);
}
上面的代码可以让您轻松地在任何地方运行select，但它有一个缺点。
您需要从数据库中指定所需的字段，因为我们在范围内使用了addSelect方法，该方法假定我们不想获取*，而只获取状态。要防止这种情况，您可以简单地说：
Trip::select('*')->withStatus()->get();
根据Ozan的建议，任何查询都可以与作用域一起使用
问题#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;
如果希望在查询时将自定义属性附加到“trips”对象：我建议使用自定义属性，您可以“始终”随模型加载，也可以按需加载。
您可以定义一个trip\u status属性，并将您的方法重命名为getTripStatusAttribute，它将被添加到查询的trips结果中。
请注意，这不会影响主tripsSQL查询。但是，如果未加载行程，则将为每个生成的行程添加一个行程查询。并且可能是资源贪婪的，因为每次加载trip时，您都会对关系使用SQL计数（如前所述，始终或按需）。
您还可以使用with count查询您的行程，以便通过行程查询加载行程计数。并将trip\u statusaccessor方法基于查询的计数属性。仍然可以属于繁重的数据库，因此更愿意仅在需要时加载该属性。
您可以使用带条件的with count（另请参见），并在访问器中使用它：
$posts = Post::withCount('upvotes')
->having('upvotes_count', '>', 5)
->get();
第二个注意事项：您可以将travel计数添加到trip查询结果中，但我认为您不能仅使用SQL添加trip\u状态。通过考虑性能，Laravel属性是一条可行之路（如果需要，加载行程relationload，使用with count…）
编辑2：在看到您的评论后，我正在使用Yajra Datatables并为不存在的动态状态列创建一个自定义筛选器
您不能对trip\u状态进行SQL查询，因为它不是数据库。您可以在trip\u status上过滤包含所有trip的集合，但这对性能来说是不可能的：您需要一个雄辩的/SQL查询。但正如我所解释的，您可以使用custom和count进行查询，从而重现您的需求。
所以对于GET数据部分，我将使用自定义属性。
但是对于查询部分，在可数据中，我会基于前端过滤器过滤我的查询。因此，您需要一个基于多个渴望加载计数的查询（检查所有链接片段逻辑以“混合”它们）。
以下是查询的外观（未完全测试，但测试了雄辩的/SQL部分以检查结果）：
//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
