Skip to content

Using SimpleFunction with Subselect? #1831

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Closed
koenpunt opened this issue Jul 8, 2024 · 0 comments
Closed

Using SimpleFunction with Subselect? #1831

koenpunt opened this issue Jul 8, 2024 · 0 comments
Assignees
Labels
type: enhancement A general enhancement

Comments

@koenpunt
Copy link
Contributor

koenpunt commented Jul 8, 2024

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?

@spring-projects-issues spring-projects-issues added the status: waiting-for-triage An issue we've not yet triaged label Jul 8, 2024
@schauder schauder added the type: enhancement A general enhancement label Jul 9, 2024
@schauder schauder self-assigned this Jul 9, 2024
@schauder schauder removed the status: waiting-for-triage An issue we've not yet triaged label Jul 9, 2024
@schauder schauder added this to the 3.4 M1 (2024.1.0) milestone Jul 9, 2024
schauder added a commit that referenced this issue Jul 16, 2024
This allows using Select instances as Expression where this isn't yet anticipated.

Closes #1831
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
type: enhancement A general enhancement
Projects
None yet
Development

No branches or pull requests

3 participants