Spring Data JPA JPQL column name is discarded in count function

Question

I have a code selecting some records by using Spring Data JPA JPQL.

I have two entities:

public class Cheat implements Serializable {
    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "cheat_seq", length = 10)
    private Long cheatSeq;

    @OneToMany(mappedBy = "cheat")
    private Set<CheatGoodVote> goodVote;

    // skipped..
}

public class CheatGoodVote {

    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Column(name="vote_seq", length=10)
    private Long voteSeq;

    @Column(name="ip_address", nullable=false)
    private String ipAddress;

    @Column(name="reg_date", nullable=false)
    private Date regDate;

    @ManyToOne(cascade=CascadeType.ALL, fetch=FetchType.LAZY)
    @JoinColumn(name="cheat_fk", referencedColumnName="cheat_seq")
    public Cheat cheat;


}

And my repository is just simple:

public interface CheatRepository extends JpaRepository<Cheat, Long>{

    @Query("SELECT c FROM Cheat c WHERE COUNT(c.goodVote) <= :voteCnt")
    Page<Cheat> findByVoteLessThan(@Param("voteCnt") Long voteCnt, Pageable page);
}

When I call the method CheatRepository.findByVoteLessThan(), It executes below SQL.

select 
cheat0_.cheat_seq as cheat_se1_0_, cheat0_.answer as answer2_0_, cheat0_.question as question3_0_, cheat0_.reg_date as reg_date4_0_, cheat0_.writer_ip as writer_i5_0_ 
from cheat cheat0_ cross join cheat_good_vote goodvote1_ 
where cheat0_.cheat_seq=goodvote1_.cheat_fk and count(.)<=? 
order by cheat0_.reg_date desc limit ?

But in that SQL, there is strange code count(.)<=? in WHERE clause. Maybe that is the cause of the error thrown.

What could be the reason to face that problem ? Thank you.

You can't use count in a where clause... You need to change for a having clause — Desorder, Oct 11 '18 at 06:47
@Desorder That is good way. I will take into consideration of that query — , Oct 11 '18 at 07:25

score 0 · Answer 1 · answered Oct 11 '18 at 06:55

0

COUNT is a aggregate function can't be used in where clause. Instead you can use SQL native inner query or having clause.

answered Oct 11 '18 at 06:55

Deepak Jain

347
1
6

Thank you for answering my question. I found a proper way thanks to Alan Hay's comment. – Oct 11 '18 at 07:23

Spring Data JPA JPQL column name is discarded in count function

1 Answers1