CODEWITHSUNDEEP
jpa-2.0java-ee-6jpql
Subraya Mallya
Subraya Mallya

Reputation: 56

How to get the first member of the related collection in JPQL

I have Product table which has a related table Images with a relation 1:M.

Class Product {
  private Integer productId;
  private String productName;
  ....
  ....
  ....
  private List<Image> productImageList;
  ....
  ....
  ....
}

Class Image{
  private Integer imageId;
  private String imageName;
}

Class ProductLite{
  private Integer productId;
  private String productName;
  private String imageName;
}

I am trying a JPQL query where I want to query to fetch products and the first image from the productImageList and returning a ProductLite object using the new constructor.

@TransactionAttribute(TransactionAttributeType.NOT_SUPPORTED)
public List<ProductLite> getAllProductLite() {
  Query q = em.createQuery("SELECT NEW com.mycomp.application.entity.ProductLite(p.productId, p.productName, p.productImageList.get(0).getImageName())"
                + " from Product p"
                + " ORDER by p.productName");
  List<ProductLite> prods = q.getResultList();
return prods;
}

But for some reason I am not able to get it to work. I get a NoViableException. So I tried moving the logic of getting the first image (getImage() method) to the Product Entity so in the query I could just call the getImage(). Even that does not seem to work.

java.lang.IllegalArgumentException: An exception occurred while creating a query in EntityManager: 
Exception Description: Syntax error parsing the query [SELECT NEW com.meera.application.entity.ProductLite(distinct p.productId,  p.productName, p.getImage()) from Product p, IN(p.productImageList) pil  where p.category.categoryCode = :categoryCode   ORDER by p.productName ], line 1, column 52: unexpected token [distinct].
Internal Exception: NoViableAltException(23@[452:1: constructorItem returns [Object node] : (n= scalarExpression | n= aggregateExpression );])

Any help is appreciated.

Upvotes: 2

Views: 3926

Answers (1)

Mikko Maunu
Mikko Maunu

Reputation: 42084

First, you cannot call methods in entity class from your JP QL query. Second, to use the order of entities in list, you need persisted order.

To create column for order to the join table between image and product, you have to add @OrderColumn-annotation to the productImageList. For example:

@OrderColumn(name = "myimage_order")
//or dont't define name and let it default to productImageList_order
@OneToMany
private List<Image> productImageList;

Then you have to modify query to use that order to choose only first image:

SELECT NEW com.mycomp.application.entity.ProductLite(
           p.productId, p.productName, pil.imageName)
FROM Product p JOIN p.productImageList pil
WHERE INDEX(pil) = 0
ORDER by p.productName

Upvotes: 3

Related Questions