I have a table in my application that keeps track of users’ settings. The SQL definition of the table is something like this (PostgreSQL):
CREATE SEQUENCE settings_id_seq;
CREATE TABLE settings
(
id BIGINT NOT NULL PRIMARY KEY DEFAULT nextval('settings_id_seq'),
user_id BIGINT NOT NULL,
created_at TIMESTAMP NOT NULL,
definition TEXT NOT NULL
);
ALTER SEQUENCE settings_id_seq OWNED BY settings.id;
CREATE INDEX settings_user_id_index ON settings (user_id) ;
CREATE INDEX settings_created_at_index ON settings (created_at) ;
This table provides an (immutable) history of changes made to the settings for each user. The current settings for a user is the respective row with the maximum created_at
timestamp, per user. Adding new settings simply adds a new row to the table with the new settings and a new (monotonic) created_at
timestamp.
One way to find the current settings for each of the users is to use the following SQL:
SELECT s1.* FROM settings s1
WHERE s1.created_at = (SELECT max(s2.created_at) FROM settings s2 WHERE s2.user_id = s1.user_id);
I have attempted to recreate this SQL using a CriteriaQuery
, but I ran into a problem with the code in that the CriteriaBuilder.max
function will only accept * extends Number
types, and my entity column is a LocalDateTime
.
I don’t understand why Hibernate has this collation restriction on CriteriaBuilder.max
when clearly SQL does not have this restriction. (In fact, with SQL you can even do a max
on TEXT
columns. ¯_(ツ)_/¯)
Why not have CriteriaBuilder.max
use, say, * extends Comparable
instead?
As an aside, I’ve had folks recommend using an implementation of the following SQL to avoid the problem with CriteriaBuilder.max
:
SELECT s1.* from settings s1
WHERE s1.created_at = (SELECT s2.created_at FROM settings s2 ORDER BY s2.created_at DESC LIMIT 1);
But, I haven’t yet got to the bottom of limiting the subquery.
Does anybody have any other ideas?
Cheers, S t u a r t .
PS: I’ve thought of using CriteriaBuilder.max
on the id
column, which gets around the Number
problem, but that kinda feels dirty.