-
Notifications
You must be signed in to change notification settings - Fork 81
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
Having issue on alias on subquery #706
Comments
I didn't realize that subqueries require an alias when they are in a from clause. What you want is the same as defining a DrivedEntity. You can write a query like the following: data class MinPrices(
val category: Int,
val minPrice: Long,
)
data class Row(
val brandName: String,
val price: Long,
val category: Int,
)
repository.findAll {
val minPrices = select<MinPrices>(
path(Product::category).alias(expression("category1")),
min(Product::price).alias(expression("price1")),
).from(
entity(Product::class),
).groupBy(
path(Product::category),
).asEntity("minPrices")
selectNew<Row>(
path(Brand::brandName),
path(Product::price),
path(Product::category),
).from(
entity(Product::class),
join(Product::brand),
join(minPrices).on(
and(
path(Product::category).eq(expression("category1")),
path(Product::price).eq(expression("price1")),
),
),
)
}.forEach { println(it) } This query is then passed to MySQL as the following:
|
@shouwn Since, the exception message only says about 'missing alias in select expression'
you also didn't expect that we have to specify aliases of fields in subquery
https://kotlin-jdsl.gitbook.io/docs/jpql-with-kotlin-jdsl/subqueries#derived-entity |
@4whomtbts I saw your answer too late. The alias is used in the on clause because the projection of the derived table has an alias. You can just think of it as the same as in SQL. The derived table called minPrices has aliased category to category1 and price to price1 as column names, so if you want to reference the columns of that table in an on clause or a where clause, you need to use those aliases. The reason I put alias in the on clause is because of the error Hibernate threw. If Hibernate hadn't thrown the error, I wouldn't have aliased the columns in the DerivedTable, and therefore wouldn't have used the alias in the on clause. And I realized I might have encountered that error before, I just forgot... My example also has aliases on the columns of the DerivedTable... The reason why I didn't use alias in the from clause is because in my example I didn't need to create a condition. If I needed a condition like join or where in my example, I would have used alias. |
Oh, and you were wondering why I didn't use alias in the select clause as well? My example was wrong, I just used employeeId as an alias for the employeeId column by mistake and it worked because they have the same name. It would be better to include examples like join, so I'll fix the example later. |
I will close this issue. If you have any additional questions, please reopen it. |
Thank you kotlin-jdsl team for developing and maintaining such a great project like this!
Version Information
kotlin-jdsl : 3.3.1
hibernate-core: 6.4.4
I have a MySQL tables as follows.
Then, I want to write query as follows.
So, I tried to convert above MySQL query into kotlin-jdsl like as follows.
Since, I couldn't find any example codes similar to this one in the kotlin-jdsl gitbook and github issues;
Honestly, I'm not sure If It's correct code.
(excerpted significant paragraph from a entire code for a brevity.)
When the above query is executed, hibernate occurs SemanticException with message
Select item at position 1 in select list has no alias (aliases are required in CTEs and in subqueries occurring in from clause)
exception message indicates the absence of alias on the select query of the nested query.
But, I believe that I provided adequate alias to both of selection node. (may be I'm wrong..)
I read some related code in a hibernate library that causes exception.
It seems that selection nodes lost there alias information when they arrives below method . .
org.hibernate.query.derived.AnonymousTupleType#AnonymousTupleType(org.hibernate.query.sqm.tree.select.SqmSelectableNode<?>[])
Either there is any mistakes on my kotlin-jdsl or kotlin-jdsl doesn't support kind of queries like this?
Thank you!
The text was updated successfully, but these errors were encountered: