CODEWITHSUNDEEP
javasqlhibernatejpahql
Czarek
Czarek

Reputation: 669

Hibernate, using distinct with more than one column inside count

How could I rewrite SQL query showed below into HQL? I would like to use javax.persistence.Query

SELECT COUNT(DISTINCT(book_id, author_id)) FROM dbo.book bo LEFT JOIN dbo.author au ON au.id = bo.author_id LEFT JOIN dbo.publisher pu ON pu.id = bo.publisher_id WHERE pu.id = 777

My attempt which does not work:

SELECT COUNT(DISTINCT(bl.id, o.id)) FROM PRDAuthor as o LEFT JOIN FETCH o.bookList as bl WHERE bl.publisher.id = 777

EDIT: I am attaching my entities

PRDAuthor

public class PRDAuthor {

    // ...
    @OneToMany(orphanRemoval = true, mappedBy = "author", cascade = {CascadeType.ALL}, fetch = FetchType.LAZY)
    @BatchSize(size = 100)
    private List<PRDBook> bookList = new ArrayList<>();
    // ...
}

PRDBook 

public class PRDBook {

    // ...
    @ManyToOne(cascade = CascadeType.MERGE, fetch = FetchType.LAZY)
    @BatchSize(size = 30)
    private PRDAuthor author;

    @ManyToOne(cascade = CascadeType.MERGE, fetch = FetchType.LAZY)
    @BatchSize(size = 30)
    private PRDPublisher publisher;

    // ...
}

PRDPublisher 

public class PRDPublisher {

    // ...
    @OneToMany(orphanRemoval = true, mappedBy = "publisher", cascade = {CascadeType.ALL}, fetch = FetchType.LAZY)
    @BatchSize(size = 100)
    private List<PRDBook> bookList = new ArrayList<>();
    // ...
}

Upvotes: 2

Views: 488

Answers (1)

Javasick
Javasick

Reputation: 2993

You can count only by one field Try this one (not tested):

SELECT COUNT(DISTINCT(*)) FROM PRDAuthor as o 
LEFT JOIN FETCH o.bookList as bl
WHERE bl.publisher.id = 777

Upvotes: 1

Related Questions