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