许多没有ID的关系中的最新关系

Question

传感器表

id  name        
1   Sensor no1
2   Sensor no2
3   Sensor no3

温度表(日志表(

temperature  sensor_id   timestamp
2.85         1           2021-10-19 18:37:34
5.05         2           2021-10-19 18:37:34
2.90         3           2021-10-20 18:37:34
5.65         1           2021-10-21 18:37:34
21.5         3           2021-10-22 18:37:34

在雄辩中，我有传感器模型与的关系

public function latestTemperature()
{
return $this->hasOne(Temperature::class)->latestOfMany();
}

我得到SQL错误，temperature.id不存在。-我在那张表里没有身份证
我如何通过时间戳desc limit 1或其他信息告诉它传感器id=x的顺序？

Unknown column 'temperatures.id' 'field list'-s (SQL: select `temperatures`.* from `temperatures` inner join (select MAX(`temperatures`.`id`) as `id_aggregate`, `temperatures`.`sensor_id` from `temperatures` where `temperatures`.`sensor_id` in (1) group by `temperatures`.`sensor_id`) as `latestOfMany` on `latestOfMany`.`id_aggregate` = `temperatures`.`id` and `latestOfMany`.`sensor_id` = `temperatures`.`sensor_id`)

编辑1

如果我能用时间戳替换那个查询中的id列就好了。。。。

select `temperatures`.* 
from `temperatures` 
inner join (select MAX(`temperatures`.`timestamp`) as `id_aggregate`, `temperatures`.`sensor_id` from `temperatures` where `temperatures`.`sensor_id` in (295)
group by `temperatures`.`sensor_id`) as `latestOfMany` on `latestOfMany`.`id_aggregate` = `temperatures`.`timestamp` and `latestOfMany`.`sensor_id` = `temperatures`.`sensor_id`

编辑2

我定义了一个自定义的newhasmany关系？

public function latestTemperature()
{
return $this->newHasMany(
Temperature::select()->join(
DB::raw(
'(select MAX(`temperatures`.`timestamp`) as `id_aggregate`, `temperatures`.`sensor_id` as id from `temperatures`
group by `temperatures`.`sensor_id`) as agg'
),
function ($join) {
$join->on('agg.id_aggregate', '=', 'temperatures.timestamp');
$join->on('agg.id', '=', 'temperatures.sensor_id');
}
),
$this,
'sensor_id',
'id'
);
}

它是有效的，但这是解决这个问题的正确方法吗
问题是聚合子查询不包括"where id in(…("，我该如何添加？

Answer 1

您可以使用hasMany来代替：

return $this->hasMany(Temperature::class, 'sensor_id', 'id');