laravel - Laravel Eloquent Postgresql Select with Case,然后按 Case 结果过滤
问题描述
我有一个 CASE 原始语句,它为每一行生成一个状态。如果用户不想查看所有项目但特定集合,我想按此状态进行过滤。
$query = Device::leftJoin('devices_meters', 'devices_meters.device_id', '=', 'devices.id')
->leftJoin('device_measurements', 'device_measurements.device_id', '=', 'devices.id')
->select(
'devices.*',
DB::raw("
CASE
WHEN
devices.retired = false
AND devices.last_reported_utc_at > :dateFilter
AND SUM(device_measurements.flow) > 0
THEN 'active'
WHEN
devices.retired = false
AND devices.last_reported_utc_at > :dateFilter
AND SUM(device_measurements.flow) = 0
THEN 'online'
WHEN
devices.retired = false
AND devices.last_reported_utc_at <= :dateFilter
THEN 'offline'
WHEN
devices.retired = false
AND devices.serial_number IS NULL
THEN 'inactive'
WHEN
devices.retired = true
THEN 'retired'
END AS status,
MAX(devices_meters.activated_at) AS activated_at,
MAX(devices_meters.created_at) AS latest
"),
)
->setBindings(['dateFilter' => $dateFilter]);
->groupBy('devices.id', 'devices_meters.activated_at')
->orderBy('devices.installed_at', 'DESC')
->paginate($user->settings_pagination);
我努力了:
$query->where('status', '=', $status);
$query->having('status', '=', $status);
但这些都行不通。我可以按status
列过滤的方式是什么?
解决方案
由于status
不是真正的列,您只需要使用整个 case 语句进行过滤。
如果$status
是“活动”,那么您需要添加
WHERE (
devices.retired = false
AND devices.last_reported_utc_at > :dateFilter
AND SUM(device_measurements.flow) > 0
)
每个状态的 SQL 查询等等。
但是,使用 Eloquent,您可以在模型中定义本地查询范围Device
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\Model;
class Device extends Model
{
public function scopeActive(Builder $query, $dateFilter)
{
$hasNecessaryJoins = collect($query->getQuery()->joins)
->contains('table', 'device_measurements');
return $query
->unless($hasNecessaryJoins, function ($subquery) {
// do the left Join if it's not done already
$subquery->leftJoin('device_measurements', 'device_measurements.device_id', '=', 'devices.id'));
})
->where(function($subquery) use ($dateFilter) {
$subquery->where('retired', false)
->where('last_reported_utc_at', '>', $dateFilter)
->whereRaw('SUM(device_measurements.flow) > 0');
});
}
public function scopeOnline(Builder $query, $dateFilter)
{
// online seems to be the same as active?
return $query
->active($dateFilter);
}
public function scopeOffline(Builder $query, $dateFilter)
{
return $query
->where(function($subquery) use ($dateFilter) {
$subquery->where('retired', false)
->where('last_reported_utc_at', '<=', $dateFilter);
});
}
public function scopeInactive(Builder $query, $dateFilter)
{
return $query
->where(function($subquery) {
$subquery->where('retired', false)
->whereNull('serial_number');
});
}
public function scopeRetired(Builder $query, $dateFilter)
{
return $query
->where('retired', true);
}
}
有了这些范围,您可以调用$query->active($dateFilter)
, $query->online($dateFilter)
, $query->offline($dateFilter)
, $query->inactive($dateFilter)
, $query->retired($dateFilter)
.
所以如果你有$status = 'active'
你可以打电话$query->$status($dateFilter)
;
推荐阅读
- bash - 以秒为单位的 bash 时间到人类可读的字符串
- windows - 从 Sublime Text 3 中删除某些语言
- c# - How to cast string to class property
- django - django 管理站点更改字段的显示标签
- java - java.time.format.DateTimeParseException:无法在索引 0 处解析文本
- avro - Azure Synapse 可以查询 AVRO 吗?(EventHubs 捕获文件)
- parquet - 如何在模式中使用 Parquet UUID 逻辑类型
- javascript - 地图左下角的 OpenLayer 静态归因
- python - 奇怪的线性回归学习曲线
- r - 尝试在 R 中绘制动画图时出错