Skip to content

Commit 17a17ec

Browse files
jeffgbutlerjeffgbutler
committed
Expose aliases to all sub queries in where clauses
1 parent de1e4d8 commit 17a17ec

File tree

5 files changed

+109
-18
lines changed

5 files changed

+109
-18
lines changed

‎src/main/java/org/mybatis/dynamic/sql/select/render/QueryExpressionRenderer.java

Lines changed: 5 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -60,13 +60,13 @@ private QueryExpressionRenderer(Builder builder) {
6060

6161
/**
6262
* This function calculates a table alias calculator to use in the current context. There are several
63-
* possibilities: this could be a renderer for a regular select statement, or it could be a renderer for a table
63+
* possibilities: this could be a renderer for a top level select statement, or it could be a renderer for a table
6464
* expression in a join, or a column to sub query where condition, or it could be a renderer for a select
65-
* statement in an "exists" condition.
65+
* statement in an "exists" condition in a where clause.
6666
*
67-
* <p>In the case of "exists" conditions, we will have a parent table alias calculator. We want to give visibility
68-
* to the aliases in the outer select statement to this renderer so columns in aliased tables can be used in exists
69-
* conditions without having to re-specify the alias.
67+
* <p>In the case of conditions in a where clause, we will have a parent table alias calculator. This will give
68+
* visibility to the aliases in the outer select statement to this renderer so columns in aliased tables can be
69+
* used in where clause sub query conditions without having to re-specify the alias.
7070
*
7171
* <p>Another complication is that we calculate aliases differently if there are joins and sub queries. The
7272
* cases are as follows:

‎src/main/java/org/mybatis/dynamic/sql/where/render/WhereConditionVisitor.java

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
/*
2-
* Copyright 2016-2021 the original author or authors.
2+
* Copyright 2016-2022 the original author or authors.
33
*
44
* Licensed under the Apache License, Version 2.0 (the "License");
55
* you may not use this file except in compliance with the License.
@@ -96,6 +96,7 @@ public FragmentAndParameters visit(AbstractSubselectCondition<T> condition) {
9696
SelectStatementProvider selectStatement = SelectRenderer.withSelectModel(condition.selectModel())
9797
.withRenderingStrategy(renderingStrategy)
9898
.withSequence(sequence)
99+
.withParentTableAliasCalculator(tableAliasCalculator)
99100
.build()
100101
.render();
101102

‎src/test/java/org/mybatis/dynamic/sql/subselect/FooDynamicSqlSupport.java

Lines changed: 37 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,37 @@
1+
/*
2+
* Copyright 2016-2022 the original author or authors.
3+
*
4+
* Licensed under the Apache License, Version 2.0 (the "License");
5+
* you may not use this file except in compliance with the License.
6+
* You may obtain a copy of the License at
7+
*
8+
* http://www.apache.org/licenses/LICENSE-2.0
9+
*
10+
* Unless required by applicable law or agreed to in writing, software
11+
* distributed under the License is distributed on an "AS IS" BASIS,
12+
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13+
* See the License for the specific language governing permissions and
14+
* limitations under the License.
15+
*/
16+
package org.mybatis.dynamic.sql.subselect;
17+
18+
import org.mybatis.dynamic.sql.SqlColumn;
19+
import org.mybatis.dynamic.sql.SqlTable;
20+
21+
import java.sql.JDBCType;
22+
import java.util.Date;
23+
24+
public class FooDynamicSqlSupport {
25+
public static final Foo foo = new Foo();
26+
public static final SqlColumn<Date> column1 = foo.column1;
27+
static final SqlColumn<Integer> column2 = foo.column2;
28+
29+
public static class Foo extends SqlTable {
30+
public final SqlColumn<Date> column1 = column("column1", JDBCType.DATE);
31+
public final SqlColumn<Integer> column2 = column("column2", JDBCType.INTEGER);
32+
33+
public Foo() {
34+
super("foo");
35+
}
36+
}
37+
}

‎src/test/java/org/mybatis/dynamic/sql/subselect/SubSelectTest.java

Lines changed: 20 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
/*
2-
* Copyright 2016-2020 the original author or authors.
2+
* Copyright 2016-2022 the original author or authors.
33
*
44
* Licensed under the Apache License, Version 2.0 (the "License");
55
* you may not use this file except in compliance with the License.
@@ -18,29 +18,30 @@
1818
import static org.assertj.core.api.Assertions.assertThat;
1919
import static org.junit.jupiter.api.Assertions.assertAll;
2020
import static org.mybatis.dynamic.sql.SqlBuilder.*;
21+
import static org.mybatis.dynamic.sql.subselect.FooDynamicSqlSupport.*;
2122

22-
import java.sql.JDBCType;
2323
import java.util.Date;
2424

2525
import org.junit.jupiter.api.Test;
26-
import org.mybatis.dynamic.sql.SqlColumn;
27-
import org.mybatis.dynamic.sql.SqlTable;
2826
import org.mybatis.dynamic.sql.render.RenderingStrategies;
2927
import org.mybatis.dynamic.sql.select.render.SelectStatementProvider;
3028

3129
class SubSelectTest {
3230

33-
static final SqlTable table = SqlTable.of("foo");
34-
static final SqlColumn<Date> column1 = table.column("column1", JDBCType.DATE);
35-
static final SqlColumn<Integer> column2 = table.column("column2", JDBCType.INTEGER);
36-
3731
@Test
3832
void testInSubSelect() {
3933
Date d = new Date();
4034

35+
Foo foo2 = new Foo();
36+
4137
SelectStatementProvider selectStatement = select(column1.as("A_COLUMN1"), column2)
42-
.from(table, "a")
43-
.where(column2, isIn(select(column2).from(table).where(column2, isEqualTo(3))))
38+
.from(foo, "a")
39+
.where(column2, isIn(
40+
select(foo2.column2)
41+
.from(foo2)
42+
.where(foo2.column2, isEqualTo(3))
43+
)
44+
)
4445
.and(column1, isLessThan(d))
4546
.build()
4647
.render(RenderingStrategies.MYBATIS3);
@@ -61,9 +62,16 @@ void testInSubSelect() {
6162
void testNotInSubSelect() {
6263
Date d = new Date();
6364

65+
Foo foo2 = new Foo();
66+
6467
SelectStatementProvider selectStatement = select(column1.as("A_COLUMN1"), column2)
65-
.from(table, "a")
66-
.where(column2, isNotIn(select(column2).from(table).where(column2, isEqualTo(3))))
68+
.from(foo, "a")
69+
.where(column2, isNotIn(
70+
select(foo2.column2)
71+
.from(foo2)
72+
.where(foo2.column2, isEqualTo(3))
73+
)
74+
)
6775
.and(column1, isLessThan(d))
6876
.build()
6977
.render(RenderingStrategies.MYBATIS3);

‎src/test/kotlin/examples/kotlin/mybatis3/joins/JoinMapperTest.kt

Lines changed: 45 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -33,6 +33,7 @@ import org.assertj.core.api.Assertions.entry
3333
import org.junit.jupiter.api.Test
3434
import org.mybatis.dynamic.sql.util.kotlin.KInvalidSQLException
3535
import org.mybatis.dynamic.sql.util.kotlin.elements.equalTo
36+
import org.mybatis.dynamic.sql.util.kotlin.elements.max
3637
import org.mybatis.dynamic.sql.util.kotlin.elements.qualifiedWith
3738
import org.mybatis.dynamic.sql.util.kotlin.mybatis3.select
3839
import java.io.InputStreamReader
@@ -774,6 +775,50 @@ class JoinMapperTest {
774775
}.withMessage("You must specify an \"on\" condition in a join")
775776
}
776777

778+
@Test
779+
fun testThatAliasesPropagateToSubQueryConditions() {
780+
newSession().use { session ->
781+
val mapper = session.getMapper(JoinMapper::class.java)
782+
783+
val orderLine2 = OrderLineDynamicSQLSupport.OrderLine()
784+
785+
val selectStatement = select(orderLine.orderId, orderLine.lineNumber) {
786+
from(orderLine, "ol")
787+
where {
788+
orderLine.lineNumber isEqualTo {
789+
select(max(orderLine2.lineNumber)) {
790+
from(orderLine2, "ol2")
791+
where { orderLine2.orderId isEqualTo orderLine.orderId }
792+
}
793+
}
794+
}
795+
orderBy(orderLine.orderId)
796+
}
797+
798+
val expectedStatement = "select ol.order_id, ol.line_number " +
799+
"from OrderLine ol " +
800+
"where ol.line_number = " +
801+
"(select max(ol2.line_number) from OrderLine ol2 where ol2.order_id = ol.order_id) " +
802+
"order by order_id"
803+
804+
assertThat(selectStatement.selectStatement).isEqualTo(expectedStatement)
805+
806+
val rows = mapper.selectManyMappedRows(selectStatement)
807+
808+
assertThat(rows).hasSize(2)
809+
810+
assertThat(rows[0]).containsOnly(
811+
entry("ORDER_ID", 1),
812+
entry("LINE_NUMBER", 2)
813+
)
814+
815+
assertThat(rows[1]).containsOnly(
816+
entry("ORDER_ID", 2),
817+
entry("LINE_NUMBER", 3)
818+
)
819+
}
820+
}
821+
777822
companion object {
778823
const val JDBC_URL = "jdbc:hsqldb:mem:aname"
779824
const val JDBC_DRIVER = "org.hsqldb.jdbcDriver"

0 commit comments

Comments
 (0)