CODEWITHSUNDEEP
javahql
Kejml
Kejml

Reputation: 2204

Alternative solution to nonexisting limit in JOIN ... WITH clause

I need to model following scenario: Entities have values, that can change over time and I need to keep all the historical values. For example I have entity Article, that has title. If article is created on 1st January 2015, it will get title "Title 1", then, on 1st February 2015 someone changes the title to "Title 2" and of 1st March, title is changed to "Title 3". And I want to able to ask "What was the article's title on 20th February 2015?"

For this, I created Article entity having list of titles:

@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 titles look like this:

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

and DateDependentEntity looks like this (I have many of them)

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

Everything works fine, I have created methods for saving and reading these entities (like "give me article where id = 1 with values valid on 20. 2. 2015").

But I have problem figuring out HQL filter queries dependent on date, like "Give me all articles that had title containing 'dog' on '20. 2. 2015'".

First, I though I'll use something like this: 

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

But this does doesn't always return expected result - If the title was "We love dogs" in January and "We like cats" since February and I filter for values containg dogs in March, it will still find this article. As far as I know I can't use some kind of limit in WITH clause, am I right?

So I though I'll use subquery, but those doesn't support LIMIT in HQL, so to problem is same as with JOIN above.

Is there some HQL query to solve my problem, or is my model completely wrong for this purpose?

Upvotes: 4

Views: 63

Answers (1)

Kejml
Kejml

Reputation: 2204

After whole day fiddling with HQL (and after realizing, that IntelliJ marks my query as having incorrect syntax although the query works), I came up with this:

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

This will do for now, although size of the query for just one field kind of scares me, but I think it won't get much simpler with given model. Time will tell if this will be usable in practice.

Upvotes: 1

Related Questions