Is there a way to get the latest entry that is less or equal to the current time with HQL?

Question

I'm trying to get the most recent data that isn't going to be applied until future dates.

For now i'm using the following HQL:

int x = goods.getId()
String hql = "FROM PriceTags as atcl WHERE atcl.goods_id = '"+x+"' AND atcl.updatedDate <= CURTIME() ORDER BY updatedDate DESC";
return (PriceTags) entityManager.createQuery(hql).setMaxResults(1).getSingleResult();

this will return all the pricetags with an updatedDate that is less or equal to the current time and order it descendingly. After that i just use the first entry it returns. Is it possible to make a HQL that gets all most updated pricetags for all goods?

For an example the table PriceTags will contain :

Id   goods_id   price   updatedDate  
1    101        100     2019-07-30  
2    101        150     2019-07-31  
3    102        120     2019-07-28  
4    102        90      2019-06-29  
5    102        130     2020-01-01

Lets say current day is 2019-08-01 i would like to get row 2 and 3, since they're the most recent entry up until current day. row 5 wont be relevant until january next year thus wont be returned.

Can you include some sample input/output data? – Tim Biegeleisen Aug 05 '19 at 03:03 — Tim Biegeleisen, Aug 05 '19 at 03:03
updated my question with an example – Joseph Dewandaru Aug 06 '19 at 04:20 — Joseph Dewandaru, Aug 06 '19 at 04:20

score 0 · Answer 1 · answered Aug 05 '19 at 07:00

0

I only intent to portray a way instead of giving the proper query.

I assume that goodsId, priceTag and updatedDate are 3 columns in table PriceTag.

select distinct(p.goodsId), p.priceTag, p.updatedDate from PriceTag p order by p.updatedDate desc

answered Aug 05 '19 at 07:00

abj1305

635
9
21

Konstantin Triger · Answer 2 · 2019-08-13T21:09:44.330

You need a WINDOW function for that, see select distinct on one column. This can be done with FluentJPA:

@Entity
@Data
@Table(name = "PriceTags")
public static class PriceTag {
    @Id
    private int id;
    private int goodsId;
    private int price;
    private Date updatedDate;
}

...

FluentQuery query = FluentJPA.SQL((PriceTag tag) -> {

    Long rowNumber = aggregateBy(ROW_NUMBER())
            .OVER(PARTITION(BY(tag.getGoodsId()))
                     .ORDER(BY(tag.getUpdatedDate()).DESC()))
                     .AS();

    SELECT(tag);
    FROM(tag);
    WHERE(lessEqual(tag.getUpdatedDate(), CURRENT_DATE()) && rowNumber == 1);

});

return query.createQuery(em, PriceTag.class).getResultList();

this is the resulting SQL

SELECT t0.* 
FROM PriceTags t0 

WHERE ((t0.updated_date <= CURRENT_DATE   ) AND
( ROW_NUMBER()  OVER(PARTITION BY  t0.goods_id   ORDER BY  t0.updated_date  DESC   ) = 1))

Is there a way to get the latest entry that is less or equal to the current time with HQL?

2 Answers2