I use @Query annotation in Jpql, I modified my query with additional "distinct", How to Fix it?

Question

In my Repositories, I modified my query with additional "distinct" and it can't work

@Query(value = "select distinct i from Item i " 
                  +"where i.store = ?1 and i.itemVariant IN ?2 "
                  +"and i.status = ?3 " )
Page<Item> findByStoreAndItemVariantInAndStatus
      (Store store, List<ItemVariant> itemVariant ,byte  status, Pageable pageable);

if I remove the additional modifying @Query, this works but the result is duplicated

Possible duplicate of [How does DISTINCT work when using JPA and Hibernate](https://stackoverflow.com/questions/1346181/how-does-distinct-work-when-using-jpa-and-hibernate) — Mebin Joe, Apr 16 '19 at 06:20

score 0 · Accepted Answer · answered Apr 16 '19 at 09:52

I have fixed it. I change it using native query and changed distinct to group by, and it's works

    @Query(value = "SELECT i.* " 
                  +"  FROM public.mst_item as i " 
                  +"  join public.mst_store as ist " 
                  +"  on i.store_id = ist.id " 
                  +"  join public.mst_item_classifiers as iv " 
                  +"  on iv.item_id = i.id " 
                  +"  where ist.id = :store and iv.name IN "
                  +"       (select ic.name from public.mst_item_classifiers as ic "
                  +"        where ic.name like concat('%',:itemVariant,'%') and ic.status = :status ) " 
                  +"  and i.status = :status "
                  +"  group by ?#{#pageable}, i.id",
                    nativeQuery = true)
 Page<Item> findByStoreAndItemVariantInAndStatus(@Param("store")Store store,@Param("itemVariant") String itemVariant , @Param("status") byte  status, Pageable pageable);

I use @Query annotation in Jpql, I modified my query with additional "distinct", How to Fix it?

1 Answers1