John Davidson

Message:

Sensors table

id  name        

1   Sensor no1

2   Sensor no2

3   Sensor no3

Temperatures table (log table)

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

In eloquent I have sensor model with relation

public function latestTemperature()

{

    return $this->hasOne(Temperature::class)->latestOfMany();

}

I get SQL error that temperatures.id does not exist. - I do not have ID in that table.

How can I tell it to where sensor id = x order by timestamp desc limit 1 or something?

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

EDIT 1

If only I could replace the id column in that query with timestamp....

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`

EDIT 2

I defined a custom newhasmany relation?

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'

    );

}

It works, but is this the correct way to solve this?

Problem with this is that the aggregating subquery does not include the 'where id in (...)', how can I add that?

---

My Answer:
Your approach of defining a custom `newHasMany` relation to handle this scenario is a valid solution. However, if you want to include the `where id in (...)` condition in the subquery, you can modify your custom relation as follows:

php
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`
where `temperatures`.`sensor_id` = ' . $this->id . '
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'
);
}


In this modified version, I added the `where temperatures.sensor_id = ' . $this->id` condition to the subquery to filter the results based on the current sensor's ID. This way, the subquery will only consider temperatures for the specific sensor for which you are retrieving the latest temperature.

This should ensure that the subquery includes the `where id in (...)` condition and returns the desired result.