Skip to content

Using SimpleFunction with Subselect? #1831

Closed
@koenpunt

Description

@koenpunt

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?

Metadata

Metadata

Assignees

Labels

Type

No type

Projects

No projects

Relationships

None yet

Development

No branches or pull requests

Issue actions