Package com.endurancetrio.data.tracker.repository

Interface DeviceTelemetryRepository

All Superinterfaces:

org.springframework.data.repository.CrudRepository<DeviceTelemetry,Long>, org.springframework.data.jpa.repository.JpaRepository<@NonNull DeviceTelemetry,@NonNull Long>, org.springframework.data.repository.ListCrudRepository<DeviceTelemetry,Long>, org.springframework.data.repository.ListPagingAndSortingRepository<DeviceTelemetry,Long>, org.springframework.data.repository.PagingAndSortingRepository<DeviceTelemetry,Long>, org.springframework.data.repository.query.QueryByExampleExecutor<DeviceTelemetry>, org.springframework.data.repository.Repository<DeviceTelemetry,Long>

@Repository
public interface DeviceTelemetryRepository
extends org.springframework.data.jpa.repository.JpaRepository<@NonNull DeviceTelemetry,@NonNull Long>

Method Summary

Modifier and Type	Method	Description
Set<String>	findExistingDevicesFrom(Set<String> devices)	Finds existing devices from the provided set of device identifiers.
List<DeviceTelemetry>	findMostRecentByDevices(@NonNull List<String> devices)	Finds the most recent active telemetry record for each specified device.
List<DeviceTelemetry>	findMostRecentRecordForEachDevice()	Finds the most recent telemetry data record for each device present in the database.

Methods inherited from interface org.springframework.data.repository.CrudRepository

count, delete, deleteAll, deleteAll, deleteAllById, deleteById, existsById, findById, save

Methods inherited from interface org.springframework.data.jpa.repository.JpaRepository

deleteAllByIdInBatch, deleteAllInBatch, deleteAllInBatch, deleteInBatch, findAll, findAll, flush, getById, getOne, getReferenceById, saveAllAndFlush, saveAndFlush

Methods inherited from interface org.springframework.data.repository.ListCrudRepository

findAll, findAllById, saveAll

Methods inherited from interface org.springframework.data.repository.ListPagingAndSortingRepository

findAll

Methods inherited from interface org.springframework.data.repository.PagingAndSortingRepository

findAll

Methods inherited from interface org.springframework.data.repository.query.QueryByExampleExecutor

count, exists, findAll, findBy, findOne

Method Details

findExistingDevicesFrom

@Query("SELECT DISTINCT d.device FROM DeviceTelemetry d WHERE d.device IN :devices")
Set<String> findExistingDevicesFrom(@Param("devices")
 Set<String> devices)

Finds existing devices from the provided set of device identifiers.

Parameters:

devices - set of device identifiers to check for existence

Returns:

set of device identifiers that exist in the database

findMostRecentByDevices

@Query(value="SELECT DISTINCT ON (device) * FROM {h-schema}device_telemetry\n        WHERE active = true AND device IN :devices ORDER BY device, created_at DESC\n",
       nativeQuery=true)
List<DeviceTelemetry> findMostRecentByDevices(@Param("devices")
 @NonNull List<String> devices)

Finds the most recent active telemetry record for each specified device.

This method uses PostgreSQL's DISTINCT ON feature to efficiently retrieve exactly one record per device - specifically, the record with the latest created_at timestamp for each device where active = true.

Implementation Details: The query uses ORDER BY device, created_at DESC to ensure deterministic results. PostgreSQL requires that DISTINCT ON columns (in this case, device) appear as the leftmost columns in the ORDER BY clause. This groups all records by device and sorts each group by created_at in descending order, guaranteeing that the first record selected for each device is the most recent one.

Example: Given devices [A, B] with the following records:

 Device | created_at       | active | value
 -------|------------------|--------|-------
 A      | 2024-01-05 10:00 | true   | 100
 A      | 2024-01-04 09:00 | true   | 150
 B      | 2024-01-05 10:00 | true   | 200  ← Same timestamp as A
 B      | 2024-01-03 08:00 | true   | 250
 

Returns: [A(2024-01-05 10:00), B(2024-01-05 10:00)]

Parameters:

devices - the list of device identifiers to query (must not be null)

Returns:

a non-null list containing the most recent active telemetry record for each device in the input list, ordered by device name ascending. If a device has no active records or is not in the database, it will not appear in the results. Returns an empty list if no active records exist for any of the specified devices.

Throws:

IllegalArgumentException - if devices is null

findMostRecentRecordForEachDevice

@Query("SELECT data FROM DeviceTelemetry data WHERE data.active = true AND data.time = (\n  SELECT MAX(maxTimeData.time)\n  FROM DeviceTelemetry maxTimeData WHERE maxTimeData.active = true AND maxTimeData.device = data.device\n) ORDER BY data.device\n")
List<DeviceTelemetry> findMostRecentRecordForEachDevice()

Finds the most recent telemetry data record for each device present in the database.

Uses a correlated subquery to identify records with the maximum timestamp per device. The main advantage of this approach is JPA provider compatibility, though performance may degrade with larger datasets. For databases supporting DISTINCT ON, consider migrating to a native query approach when performance becomes a concern.

PostgreSQL native query alternative: @Query(value = """ SELECT DISTINCT ON (device) * FROM {h-schema}device_telemetry WHERE active = true ORDER BY device, time DESC", nativeQuery = true )

Returns:

non-null list of telemetry data records containing the latest record for each device. Returns an empty list if no active records exist for any device.