https://kotlinlang.org logo
#exposed
Title
# exposed
m

martmists

12/06/2023, 12:30 AM
The DAO documentation states that for doing nested selections, you need to convert to expressions:
Copy code
val postPoints = wrapAsExpression<Int>(PostTable.slice(PostTable.points.sum()).select { UserTable.id eq PostTable.author })
val usersByPoints = UserTable.selectAll().orderBy(postPoints), SortOrder.DESC).toList()
What's the benefit over doing this as an alternative with the DAO:
Copy code
val usersByPoints = User.all().sortedByDescending { it.posts.sumOf { it.points } }
Because I was under the impression that the DAO did lazy evaluation and the latter would be more readable and preferred
c

Chantal Loncle

12/20/2023, 6:46 PM
Hi @martmists The example in the Wiki is very basic, but the option to wrap a subquery as an expression gives users the power to generate potentially complex SQL queries. Particularly if they need to use functions, aggregate or otherwise, that may be database-specific and not necessarily a part of the Kotlin standard lib. Much like how Exposed provides an option to eager load with DAO, this provides an option to execute a single complex query when needed versus the lazy load approach. If you add a logger to view the generated SQL, the first approach executes a single
SELECT
statement (albeit a nested one). The second approach first executes a parent
SELECT
for
all()
which is a terminal operation of sorts, since it returns a
SizedIterable
. Then one
SELECT
statement is executed for every referencing child entity as they are iterated over by
sumOf()
. It comes down to preference on how a user wants to manipulate data and, depending on the underlying database, potentially performance. Does the user want all the data processing to occur on the database-side by aggregating and loading references simultaneously in a single complex query? Or do they want to handle the data processing themselves? And whether the scale of references to lazy load (potentially leading to N additional queries for every parent query) is a concern for the user.