spring-projects / spring-data-relational

Spring Data Relational. Home of Spring Data JDBC and Spring Data R2DBC.
https://spring.io/projects/spring-data-jdbc
Apache License 2.0
753 stars 345 forks source link

Using SimpleFunction with Subselect? #1831

Closed koenpunt closed 2 months ago

koenpunt commented 2 months ago

I'm trying to construct the following query using the DSL;

SELECT opening_hours.* FROM opening_hours
WHERE 
    opening_hours.valid_from >= COALESCE(
      (
        SELECT MAX(opening_hours.valid_from) FROM opening_hours
        WHERE opening_hours.merchant_id = $1
          AND opening_hours.product_id IS NULL 
          AND opening_hours.valid_from <= $2
      ), 
      $2
    )
    AND opening_hours.merchant_id = $1
    AND opening_hours.product_id IS NULL
    AND opening_hours.valid_from <= $3
ORDER BY opening_hours.valid_from ASC

The inner select in the coalesce is build like this:

val innerSelect = Select.builder()
    .select(Expressions.just("MAX($validFrom)"))
    .from(table)
    .where(merchantIdCondition)
    .and(productIdCondition)
    .and(validFrom.isLessOrEqualTo(startMarker))
    .build()

But passing that select to SimpleFunction.create doesn't work;

val minValue = SimpleFunction.create("COALESCE", listOf(innerSelect, startMarker))

I did see the SubselectExpression, but its constructor isn't public. Implementing the same class with an accessible constructor resulted in a query where only the conditions were rendered, but not the SELECT ... FROM ... WHERE part.

I'm now working around this by explicitly rendering the nested select;

val minValidFrom = Expressions.just(
   "(${sqlRenderer.render(innerSelect)})"
)
val minValue = SimpleFunction.create("COALESCE", listOf(minValidFrom, startMarker))

but it would be preferred if rendering was handled by the framework. Is this in any way possible?