Unable to convert SQL which is meant for greatest

Question

I have two tables in Oracle namely product and product_image. As the names imply they have a one-to-many relationship from product to product_image.

The product entity:

@Entity
@Table(name = "PRODUCT", catalog = "", schema = "WAGAFASHIONDB")
public class Product  implements java.io.Serializable
{
    private static final long serialVersionUID = 1L;
    @Id
    @Basic(optional = false)
    @Column(name = "PROD_ID", nullable = false, precision = 35, scale = 0)
    @SequenceGenerator(name = "productIdSequence", sequenceName = "PRODUCT_SEQ", allocationSize=1, initialValue=1)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "productIdSequence")
    private Long prodId;
    @Column(name = "PROD_NAME", length = 50)
    private String prodName;
    @Column(name = "PROD_CODE", length = 50)
    private String prodCode;
    @Lob
    @Column(name = "PROD_DESC")
    private String prodDesc;
    @Basic(optional = false)
    @Column(name = "MARKET_PRICE", nullable = true, precision = 35, scale = 2)
    private BigDecimal marketPrice;
    @Basic(optional = false)
    @Column(name = "SALE_PRICE", nullable = true, precision = 35, scale = 2)
    private BigDecimal salePrice;
    @Column(name = "PROD_FEATURED")
    private Short prodFeatured;
    @Column(name = "EXPRESS_DELIVERY")
    private Short expressDelivery;
    @Basic(optional = false)
    @Column(name = "PROD_WEIGHT", nullable = true, precision = 35, scale = 2)
    private BigDecimal prodWeight;
    @Column(name = "PROD_OCCASSION", length = 50)
    private String prodOccassion;
    @Basic(optional = false)
    @Column(name = "QTY_AVAILABLE", nullable = true)
    private BigInteger qtyAvailable;
    @Column(name = "LATEST")
    private Short latest;
    @Column(name = "VISIBLE")
    private Short visible;
    @JoinTable(name = "PRODUCT_SIZE", joinColumns = {
        @JoinColumn(name = "PROD_ID", referencedColumnName = "PROD_ID")}, inverseJoinColumns = {
        @JoinColumn(name = "SIZE_ID", referencedColumnName = "SIZE_ID")})
    @ManyToMany(fetch = FetchType.LAZY)
    private Set<SizeTable> sizeTableSet;
    @ManyToMany(mappedBy = "productSet", fetch = FetchType.LAZY)
    private Set<Colour> colourSet;
    @OneToMany(mappedBy = "prodId", fetch = FetchType.LAZY)
    private Set<Measurement> measurementSet;
    @OneToMany(mappedBy = "prodId", fetch = FetchType.LAZY)
    private Set<Wish> wishSet;
    @OneToMany(mappedBy = "prodId", fetch = FetchType.LAZY)
    private Set<Cart> cartSet;



    @OneToMany(mappedBy = "prodId", fetch = FetchType.LAZY)
    private Set<ProductImage> productImageSet;             //<--------



    @OneToMany(cascade = CascadeType.ALL, mappedBy = "product", fetch = FetchType.LAZY)
    private Set<OrderItem> orderItemSet;
    @JoinColumn(name = "SUB_CAT_ID", referencedColumnName = "SUB_CAT_ID")
    @ManyToOne(fetch = FetchType.LAZY)
    private Subcategory subCatId;
    @JoinColumn(name = "FABRIC_ID", referencedColumnName = "FABRIC_ID", nullable = false)
    @ManyToOne(optional = false, fetch = FetchType.LAZY)
    private Fabric fabricId;
    @JoinColumn(name = "BRAND_ID", referencedColumnName = "BRAND_ID")
    @ManyToOne(fetch = FetchType.LAZY)
    private Brand brandId;
    @OneToMany(mappedBy = "prodId", fetch = FetchType.LAZY)
    private Set<Inquiry> inquirySet;
    @OneToMany(mappedBy = "prodId", fetch = FetchType.LAZY)
    private Set<Rating> ratingSet;
}

The ProductImage entity:

@Entity
@Table(name = "PRODUCT_IMAGE", catalog = "", schema = "WAGAFASHIONDB")
public class ProductImage  implements java.io.Serializable
{
    private static final long serialVersionUID = 1L;
    @Id
    @Basic(optional = false)
    @Column(name = "PROD_IMAGE_ID", nullable = false, precision = 35, scale = 0)
    @SequenceGenerator(name = "productIdSequence", sequenceName = "PRODUCT_IMAGE_SEQ", allocationSize=1, initialValue=1)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "productIdSequence")
    private Long prodImageId;
    @Lob
    @Column(name = "PROD_IMAGE")
    private String prodImage;
    @JoinColumn(name = "PROD_ID", referencedColumnName = "PROD_ID")



    @ManyToOne(fetch = FetchType.LAZY)
    private Product prodId;           //<--------------
}

I need to fetch only rows for the maximum prodId for each group of rows (actually each group of products) in ProductImage i.e the greatest N per group.

The Oracle native SQL can be constructed as follows.

SELECT pi.prod_image_id, pi.prod_id, pi.prod_image 
FROM product_image pi 
INNER JOIN (select max(pi.prod_image_id) AS prod_image_id, pi.prod_id FROM product_image pi GROUP BY pi.prod_id) prod_image 
ON pi.prod_image_id=prod_image.prod_image_id

This works exactly as intended but I couldn't convert this SQL to HQL. Trying to execute directly as HQL in the createQuery() method causes the QuerySyntexException, unexpected token at the very first opening parentheses. Could you please given me a hint to convert this SQL to HQL?

---

EDIT:

Some questions like the following haven't yet answered to accomplish this as it actually means:

• Best way to use hibernate for complex queries like top n per group
• hql query to retrieve top n from each group
• Using HQL with MySQL how can I order the result set before the group by so the right record is picked?

I think, there is no way to achieve this in Hibernate through HQL. I may need to break this SQL to appropriate two or more HQL statements using some DTO classes. If it is possible through a single HQL statement like the SQL above, then please don't forget answer here, since I have such requirements at too many places in my application.

Answer 1

Take a look at this:

select 
    pi.prodImageId, 
    pi.prodId 
from 
    ProductImage pi 
where 
    pi.prodImageId in (
    select max(pis.prodImageId)
    from Product p
        inner join p.productImageSet pis
    group by p.prodId
)

which translates to:

select
   productima0_.prodImageId as col_0_0_,
   productima0_.prodId_prodId as col_1_0_,
   product1_.prodId as prodId18_,
   product1_.name as name18_ 
from
   ProductImage productima0_ 
inner join
   Product product1_ on productima0_.prodId_prodId=product1_.prodId 
where
   productima0_.prodImageId in (
      select max(productima3_.prodImageId) 
      from 
         Product product2_ inner join ProductImage productima3_ on product2_.prodId=productima3_.prodId_prodId 
      group by product2_.prodId
   )

which is what you want i guess?