Speedment aggregation

speedment.java

// For a single book, I guess I would do something like this:

Book book = speedment.managerOf(Book.class)
	.stream()
	.findAny()
	.orElseThrow(() -> new RuntimeException());
		
double averageRating = book.findBookRatings()
	.collect(averagingDouble(r -> r.getRating()));

tables.sql

// MySQL tables

CREATE TABLE Book
(
	bookId		BIGINT		NOT NULL	PRIMARY KEY	AUTO_INCREMENT
,	bookName	NVARCHAR(512)	NOT NULL

,	PRIMARY KEY (bookId)
)

CREATE TABLE Account
(
	accountId	BIGINT		NOT NULL	PRIMARY KEY	AUTO_INCREMENT
,	accountName	NVARCHAR(512)	NOT NULL

,	PRIMARY KEY (accountId)
)

CREATE TABLE BookRating
(
	bookId		BIGINT		NOT NULL
,	accountId	BIGINT		NOT NULL
,	rating		SMALLINT	NOT NULL

,	PRIMARY KEY (bookId, accountId)
)

// In Microsoft SQL, I would query it something like this:
SELECT	b.bookId
,	b.bookName
,	x.averageRating
FROM	Book b WITH (READPAST)
OUTER APPLY
(
	SELECT	AVG(CAST(br.rating AS NUMERIC)) averageRating
	FROM	BookRating br WITH (READPAST)
	WHERE	br.bookId = b.bookId
) x