Alternative solution for nonexistent limit in JOIN ... WITH clause

Question

I need to model the following scenario: entities have values ​​that can change over time, and I need to keep all historical values. For example, I have an Article object that has a title. If the article was created on January 1, 2015, it will be called “Title 1”, then on February 1, 2015, someone changes the title to “Title 2” and on March 1, the name changes to “Title 3”. And I want to ask: "What was the title of the article on February 20, 2015?"

To do this, I created an Article object with a list of names:

@Entity
public class Article {
    @Id
    private Long id;
    @OneToMany(mappedBy = "parent", cascade = CascadeType.ALL)
    private List<ArticleTitle> title = new LinkedList<>();

    //Setters and getters are here
}

where the headers look like this:

@Entity
public class Title extends DateDependentEntity<Title> {
    @NotNull
    private String title;
}

and DateDependentEntity looks like this (I have a lot of them)

@MappedSuperclass
public abstract class DateDependentEntity<PARENT> {
    @Id
    private Long id;
    @Column(nullable = false)
    private LocalDate validFrom;
    @ManyToOne
    private PARENT parent;
}

, (, " , id = 1 , 20. 2. 2015" ).

, HQL, , " , , " "" 20. 2. 2015 ".

-, - :

SELECT DISTINCT a FROM Article a JOIN a.title at WITH (at.validFrom <= :date)  WHERE at.title LIKE :title

. " " " " , , , . , - WITH, ?

, , LIMIT HQL, , JOIN .

- HQL , ?

Answer 1

, HQL (, , IntelliJ , ), :

SELECT at.parent FROM ArticleTitle at 
WHERE at.title LIKE :title AND (at.validFrom, at.parent) IN (
    SELECT max(at2.validFrom), at2.parent FROM ArticleTitle at2 
    WHERE at2.validFrom <= :date GROUP BY at2.parent
);

, , , . , .