JPA select only the most recent dates per item

Question

So I have a jpa database query that works, and returns all the SiteBmdfBusinessDay's from that table and relates them to their corresponding SiteBmdf by joining on their ID columns. What I want to do is, instead of getting all of the SiteBmdfBusinessDay's, I just want to get the ones with the most recent businessDay.

Here is the SiteBmdfBusinessDay class...

@Entity
public class SiteBmdfBusinessDay extends BaseEntity {

private static final long serialVersionUID = 1L;

private Long id;
private SiteBmdf siteBmdf;
private DateTime businessDay;
private Long failedCount;
private DateTime lastSuccessfulBusinessDay;
private DateTime lastSuccessfulFullBusinessDay;
private Ticket ticket;

@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "site_bmdf_business_day_site_bmdf_business_day_id_seq")
@SequenceGenerator(name = "site_bmdf_business_day_site_bmdf_business_day_id_seq", sequenceName = "site_bmdf_business_day_site_bmdf_business_day_id_seq", allocationSize = 1)
@Column(name = "site_bmdf_business_day_id")
public Long getId() {
    return id;
}

public void setId(Long id) {
    this.id = id;
}

@ManyToOne
@JoinColumn(name = "site_bmdf_id")
public SiteBmdf getSiteBmdf() {
    return siteBmdf;
}

public void setSiteBmdf(SiteBmdf siteBmdf) {
    this.siteBmdf = siteBmdf;
}

@Type(type = "org.jadira.usertype.dateandtime.joda.PersistentDateTime", parameters = {
        @Parameter(name = "databaseZone", value = "UTC"), @Parameter(name = "javaZone", value = "UTC") })
public DateTime getBusinessDay() {
    return businessDay;
}

public void setBusinessDay(DateTime businessDay) {
    this.businessDay = businessDay;
}

public Long getFailedCount() {
    return failedCount;
}

public void setFailedCount(Long count) {
    this.failedCount = count;
}

@Type(type = "org.jadira.usertype.dateandtime.joda.PersistentDateTime", parameters = {
        @Parameter(name = "databaseZone", value = "UTC"), @Parameter(name = "javaZone", value = "UTC") })
public DateTime getLastSuccessfulBusinessDay() {
    return lastSuccessfulBusinessDay;
}

public void setLastSuccessfulBusinessDay(DateTime lastSuccessfulBusinessDay) {
    this.lastSuccessfulBusinessDay = lastSuccessfulBusinessDay;
}

@Type(type = "org.jadira.usertype.dateandtime.joda.PersistentDateTime", parameters = {
        @Parameter(name = "databaseZone", value = "UTC"), @Parameter(name = "javaZone", value = "UTC") })
public DateTime getLastSuccessfulFullBusinessDay() {
    return lastSuccessfulFullBusinessDay;
}

public void setLastSuccessfulFullBusinessDay(DateTime lastSuccessfulFullBusinessDay) {
    this.lastSuccessfulFullBusinessDay = lastSuccessfulFullBusinessDay;
}

@ManyToOne(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
@JoinColumn(name = "ticket_id")
public Ticket getTicket() {
    return ticket;
}

public void setTicket(Ticket ticket) {
    this.ticket = ticket;
}
}

And the SiteBmdf class...

@Entity
public class SiteBmdf extends BaseEntity {

private static final long serialVersionUID = 1L;

private Long id;
private String site;
private Long failureCount;
private Set<SiteBmdfBusinessDay> businessDays;
private List<SiteBmdfNote> notes;
private Resolution resolution; 

@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "site_bmdf_site_bmdf_id_seq")
@SequenceGenerator(name = "site_bmdf_site_bmdf_id_seq", sequenceName = "site_bmdf_site_bmdf_id_seq", allocationSize = 1)
@Column(name = "site_bmdf_id")
public Long getId() {
    return id;
}

public void setId(Long id) {
    this.id = id;
}

public String getSite() {
    return site;
}

public void setSite(String site) {
    this.site = site;
}

@Transient
public Long getFailureCount() {
    return failureCount;
}

public void setFailureCount(Long failureCount) {
    this.failureCount = failureCount;
}

@JsonIgnore
@OneToMany(mappedBy = "siteBmdf", orphanRemoval = true, cascade = CascadeType.ALL, fetch = FetchType.EAGER)
public Set<SiteBmdfBusinessDay> getBusinessDays() {
    return this.businessDays;
}

public void setBusinessDays(Set<SiteBmdfBusinessDay> businessDays) {
    this.businessDays = businessDays;
}

@OneToMany(mappedBy = "siteBmdf", orphanRemoval = true, cascade = CascadeType.ALL, fetch = FetchType.LAZY)
public List<SiteBmdfNote> getNotes() {
    Collections.sort(notes);
    return notes;
}

public void setNotes(List<SiteBmdfNote> notes) {
    this.notes = notes;
}

@ManyToOne
@JoinColumn(name = "resolutionId")
public Resolution getResolution() {
    return resolution;
}

public void setResolution(Resolution resolution) {
    this.resolution = resolution;
}
}

And here is the current query method....

@Override
public PageImpl<SiteBmdfBusinessDay> bmdfSitePaged(UiBuildCriteria criteria) {

    CriteriaBuilder builder = entityManager.getCriteriaBuilder();
    CriteriaQuery<SiteBmdfBusinessDay> query = builder.createQuery(SiteBmdfBusinessDay.class);
    Root<SiteBmdfBusinessDay> root = query.from(SiteBmdfBusinessDay.class);

    if (!criteria.getSearch().getPredicateObject().isEmpty()) {
        List<Predicate> predicates = predicateBuilderService.buildPredicates(builder, root, criteria.getSearch());
        query.where(builder.and(predicates.toArray(new Predicate[] {})));
    }

    query.select(root);

    builder.max(root.<Long>get("businessDay"));

    // get the count for PageImpl
    Long total = this.count(criteria);

    // Set the order by
    List<CustomSort> defaultSorts = new ArrayList<CustomSort>(
            Arrays.asList(
                new CustomSort("businessDay", Boolean.TRUE),
                new CustomSort("siteBmdf.site", Boolean.FALSE)
            ));

    List<Order> orders = sortOrderBuilderService.buildSort(builder, root, criteria.getSort(), defaultSorts);

    query.orderBy(orders);

    List<SiteBmdfBusinessDay> content = entityManager.createQuery(query).setFirstResult(criteria.getPagination().getStart())
            .setMaxResults(criteria.getPagination().getNumber()).getResultList();

    return new PageImpl<>(content, criteria.getPageRequest(), total);
}

So like I said, the current results are just all the items in the SiteBmdfBusinessDay table with their corresponding SiteBmdfs. I would like only the SiteBmdfBusinessDays with the most recent businessDay for each site.

For instance, if I have the table site_bmdf_business_day:

site_bmdf_business_day_id | site_bmdf_id | business_day
1                         | 1            |  6/1/2011
2                         | 2            |  6/1/2011
3                         | 1            |  6/6/2011
4                         | 3            |  6/6/2011

I would only want to display:

site_bmdf_business_day_id | site_bmdf_id | business_day
2                         | 2            |  6/1/2011
3                         | 1            |  6/6/2011
4                         | 3            |  6/6/2011

I know i will need something along the lines of builder.max(root.get("businessDay")) but i'm not sure how to implement it.

Any help here would be greatly appreciated.

Note that this question is similar, but not exactly what I am looking for as all of the answers are in SQL and I need something in jpa here.

score 1 · Accepted Answer · answered Jun 19 '17 at 22:36

For these type of queries, I typically boil it down to sql (without pagination and other limitations), and then reverse construct it into a querybuilder. It makes for cleaner code IMO. What you're looking for is something like

select a.* from SiteBmdfBusinessDay a 
inner join 
( select site_bmdf_id, max (business_day) as business_day 
  from SiteBmdfBusinessDay group by site_bmdf_id ) b 
on (a.site_bmdf_id = b.site_bmdf_id) and (a.business_day = b.business_day)

This assumes that there's at most one entry per day, per site_bmdf_id.

This is, as far as I know, not doable as a builder, however, you can put this as a create query and it works just fine (because the result is just a normal entity.) However, it's a very performance prohibitive query, assuming you have a decent amount of data. Unless you have a small data set, it's probably better to just take it all, and then programmatically filter out duplicates (which you should be able to do in O(n) time.)

Well that SQL definitely works, so thank you for that! Though, I am not going to mark this as solved just yet, in case anyone has an idea that works as a builder, which is what I really need. The thing here is that while, this specific data set may not get too large, I would like to use this implementation in places where the table may be close to 1M rows, so I definitely cannot pull the whole set and parse through it. — Stoic_Observer, Jun 20 '17 at 11:56
Alas, my boss likes the original outcome anyway so I am marking this as solved! — Stoic_Observer, Jun 20 '17 at 13:45

JPA select only the most recent dates per item

1 Answers1