Criteria API Specification - Filter records and return only the latest record for many to one mappings

user_5381672 0
Viewed 93

I have two tables, Lead and LeadActivity. A lead can have many lead activities and mapping is done as @ManyToOne form LeadActivity to Lead.

Problem Statement -I want to to filter LeadActivity records such that, If there are more than one leadactivity records with same leadId, i should get only one record which is latest (have max primary key). Can anyone guide me on how to write specification or criteria API in such situations? I know this can be achieved through other ways but I have to use specification API. Below are the entity classes

Lead

@Entity
@Table(name = "customer_lead")
@Where(clause = ReusableFields.SOFT_DELETED_CLAUSE)
@Audited(withModifiedFlag = true)
@Data
public class Lead extends ReusableFields implements Serializable
{
    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "lead_id", updatable = false, nullable = false)
    Long leadId;

    @Column(name = "name")
    String customerName;

    @Column(name = "primary_mobile")
    String primaryMobile;

    @Column(name = "secondary_mobile")
    String secondaryMobile;

    //more fields
}

Lead Activity

@Entity
@Table(name = "LeadActivity")
@Data
@Where(clause = ReusableFields.SOFT_DELETED_CLAUSE)
public class LeadActivity extends ReusableFields implements Serializable
{
@Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "leadactivity_id", updatable = false, nullable = false)
    Long leadActivityId;

    @Column(name = "activity_date_time", nullable = false)
    @NonNull
    @JsonFormat(shape = JsonFormat.Shape.STRING, pattern = "yyyy-MM-dd HH:mm")
    Date activityDateTime;

    @Column(name = "title")
    @NonNull
    String title;

@OneToOne(fetch = FetchType.LAZY, cascade = CascadeType.ALL)
    @JoinColumn(name = "lead_id", nullable = false)
    @JsonIgnoreProperties(
    { "hibernateLazyInitializer", "handler" })
    @NotFound(action = NotFoundAction.IGNORE)
    Lead lead;

//More fields
}

Expected Output - Suppose there are two records present with same leadId. I want to fetch only the latest among them based on their id. One with lower id should be ignored

spring-data-jpa spring-boot criteriaquery criteria-api criteria
1 Answers

Try this:

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<LeadActivity> cq = cb.createQuery(LeadActivity.class);

Root<LeadActivity> rootLeadActivity = cq.from(LeadActivity.class);
Join<LeadActivity,Lead> joinLead = rootLeadActivity.join(LeadActivity_.lead,JoinType.INNER);

/* If you dont use metamodel: 
 * Join<LeadActivity,Lead> joinLead = rootLeadActivity.join("lead",JoinType.INNER);
 */

// For performance, if you use JPA 2.1 set the leader id condition in the join
joinLead.on(cb.equal(joinLead.get(Lead_.leadId),LEAD_ID));

List<Predicate> predicatesList= new ArrayList<>();
/* if you use version 2.0 you will have to put it in the where 
 * predicatesList.add(cb.equal(joinLead.get(Lead_.leadId),LEAD_ID));
 */

Subquery<Long> sqMaxId = cq.subquery(Long.class);
Root<LeadActivity> sqRootActivity = sqMaxId.from(LeadActivity.class);
Join<LeadActivity,Lead> sqJoinLead = sqRootActivity.join(LeadActivity_.lead,JoinType.INNER);
sqMaxId.where(cb.equal(sqJoinLead.get(Lead_.leadId),joinLead.get(Lead_.leadId)));
sqMaxId.select(cb.max(sqRootActivity.get(LeadActivity_.leadActivityId)));

predicatesList.add(cb.equal(rootLeadActivity.get(LeadActivity_.leadActivityId),sqMaxId.getSelection()));

cq.where(predicatesList.toArray(new Predicate[predicatesList.size()]));
cq.multiselect(rootLeadActivity);

The result query:

select a.* from lead_activity a
inner join lead l on a.lead_id = l.lead_id and l.lead_id = LEAD_ID
where a.lead_activity_id = 
    (select max(lead_activity_id) from lead_activity where lead_id = LEAD_ID)
user_104652740
Related