Self Join using criteria query

Hibernate ORM
1

I would like to find the greatest n per group from a table.

SELECT a.* FROM YourTable a
LEFT OUTER JOIN YourTable b
     ON a.id = b.id AND a.rev < b.rev 
WHERE b.id IS NULL;

As some inputs are dynamic I relied on using criteria queries.
As Criteria Joins require mapping of the entities this operation requires mapping the entity within itself.

Here is the entity I’m relying upon

    @Entity
    @Getter
    @Setter
    @Table(name = "user_order_package_subscription_v1")
    public class UserOrderPackageSubscriptionV1 {
    
        @Id
        @Column(name = "user_order_package_subscription_id", nullable = false, updatable = false)
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        private Long userOrderPackageSubscriptionId;
    
        @Column(name = "total_paid_amount", nullable = false)
        private Double totalPaidAmount;
    
        @Column(name = "purchase_date", nullable = false)
        private LocalDateTime purchaseDate;
    
        @ManyToOne
        @JoinColumn(name="uam_id", referencedColumnName = "uam_id", insertable=false, updatable=false)
        private UserOrderPackageSubscriptionV1 uopsv1;
    
        @ManyToOne
        @JoinColumn(name = "uam_id", referencedColumnName = "uam_id")
        private StudentDetailsV1 studentDetail;
    
    }

The column in which I want to perform the left join is uam_id but it is a foreign key that refers to another table studentDetails and hence I have written another mapping for it.

The join segment from the service is as follows

Root<UserOrderPackageSubscriptionV1> userOrderSubscriptionTableLeft = query.from(UserOrderPackageSubscriptionV1.class);
    
    Join<UserOrderPackageSubscriptionV1, UserOrderPackageSubscriptionV1> selfJoin = userOrderSubscriptionTableLeft.join(UserOrderPackageSubscriptionV1_.uopsv1, JoinType.LEFT);
            selfJoin.on(
                           cb.lessThan(userOrderSubscriptionTableLeft.get("purchaseDate"),
                            selfJoin.get("purchaseDate"))
            );

But this makes the UAM_ID unique and tries to create a foreign key constraint on uam_id that self references the table.

Also if I remove referencedColumnName it joins using the uam_id and the user_order_package_subscription_id(PK of the table). How do I perform a self join.

2

You can add a cross join by calling query.from(..) again, but since you want a left join, you will have to either use Hibernate 6 JPA Criteria extension APIs i.e. ((JpaRoot<UserOrderPackageSubscriptionV1>) userOrderSubscriptionTableLeft).leftJoin(UserOrderPackageSubscriptionV1.class), or by using HQL which supports entity joins already since Hibernate 5.1.

3

Hello beikov but I have some dynamic inputs like list depending upon which I may or may not use in operator in where condition of criteria query if its empty. And also other dynamic conditions does hql work for this kind of use case.

Also you suggested hibernate 6 criteria api but I would like to perform join on a non primary key field which might not be unique. How do I specify the field in which I want to perform join?

4

If you have the need for dynamic filtering, then you should use a query builder like JPA Criteria, QueryDSL or Blaze-Persistence, which all support entity joins.

With Hibernate 6, you simply write the condition into the join i.e.

var join = ((JpaRoot<UserOrderPackageSubscriptionV1>) userOrderSubscriptionTableLeft).leftJoin(UserOrderPackageSubscriptionV1.class);
join.on(<your predicate>);