Spring Data JPA - Custom Query with multiple aggregate functions in result

Question

I was trying to return an average and count of a set of ratings in one query. I managed it fairly easily in two queries following the example I found browsing. For example:

@Query("SELECT AVG(rating) from UserVideoRating where videoId=:videoId")
public double findAverageByVideoId(@Param("videoId") long videoId);

but as soon as I wanted an average and a count in the same query, the trouble started. After many hours experimenting, I found this worked, so I am sharing it here. I hope it helps.

1) I needed a new class for the results:

The I had to reference that class in the query:

@Query("SELECT new org.magnum.mobilecloud.video.model.AggregateResults(AVG(rating) as rating, COUNT(rating) as TotalRatings) from UserVideoRating where videoId=:videoId")
public AggregateResults findAvgRatingByVideoId(@Param("videoId") long videoId);

One query now returns average rating and count of ratings

does this populate other attribites as well.

Jef
– Jef

2021-12-02 21:20:19 +00:00
Commented Dec 2, 2021 at 21:20 — Jef
– Jef, Commented Dec 2, 2021 at 21:20

Anton Hlinisty · Accepted Answer · 2018-12-09 19:04:46Z

23

Solved myself.

Custom class to receive results:

public class AggregateResults {

    private final double rating;
    private final int totalRatings;

    public AggregateResults(double rating, long totalRatings) {
        this.rating = rating;
        this.totalRatings = (int) totalRatings;
    }

    public double getRating() {
        return rating;
    }

    public int getTotalRatings() {
        return totalRatings;
    }
}

and

@Query("SELECT new org.magnum.mobilecloud.video.model.AggregateResults(
    AVG(rating) as rating, 
    COUNT(rating) as TotalRatings) 
    FROM UserVideoRating
    WHERE videoId=:videoId")
public AggregateResults findAvgRatingByVideoId(@Param("videoId") long videoId);

edited Dec 9, 2018 at 19:04

Anton Hlinisty

1,4671 gold badge22 silver badges36 bronze badges

answered Aug 17, 2015 at 11:16

formica

9441 gold badge8 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Garry Over a year ago

Please define your custom class

formica Over a year ago

public class AggregateResults { private final double rating; private final int totalRatings; public AggregateResults(double rating, long totalRatings) { this.rating = rating; this.totalRatings = (int) totalRatings; } public double getRating() { return rating; } public int getTotalRatings() { return totalRatings; } }

Garry Over a year ago

thank you...Please add this to your answer and relate

jmhostalet Over a year ago

Is your custom class marked as "Entity"? I've got error "entity has no primary key attribute defined"

formica Over a year ago

@jmhostalet You need (at)Entity against the classes that map to tables, yes, but not the custom result class. Your error sounds like an issue with the class mapped to the db table. You need to have a column tagged with (at)Id somewhere in that class. For example: (at)Id (at)GeneratedValue(strategy = GenerationType.AUTO) private long id; Sorry the dialogue won't let me enter "at" signs

|

kCH · Accepted Answer · 2017-01-01 21:12:44Z

4

Thanks.

You should prevent NPEs and hibernate parsing tuple errors as following :

public class AggregateResults {

private final double rating;
private final int totalRatings;

public AggregateResults(Double rating, Long totalRatings) {
    this.rating = rating == null ? 0 : rating;
    this.totalRatings = totalRatings == null ? 0 : totalRatings.intValue();
}

public double getRating() {
    return rating;
}
public int getTotalRatings() {
    return totalRatings;
}}

answered Jan 1, 2017 at 21:12

kCH

411 bronze badge

1 Comment

formica Over a year ago

Yes, I guess the AVG() could return null although I think the COUNT() would be 0, if no rows matched.

Collectives™ on Stack Overflow

Spring Data JPA - Custom Query with multiple aggregate functions in result

2 Answers 2

6 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related