HQL not working. Gives strange Exception in console.
How could that happen?
I have this HQL:
SELECT mfr.id FROM EMailToEMailFolderOrdered mfr WHERE NOT(SIZE(mfr.EMail) = MAX(mfr.orderNumber) AND MIN(mfr.orderNumber)=1) GROUP BY mfr.id34 Replies
⌛ This post has been reserved for your question.
Hey @Peter Rader! Please useTIP: Narrow down your issue to simple and precise questions to maximize the chance that others will reply in here./closeor theClose Postbutton above when your problem is solved. Please remember to follow the help guidelines. This post will be automatically closed after 300 minutes of inactivity.
Can you show the stack trace?
``````
Can you please also show the code of the entity and the code you used for querying?
In an eMailFolder we have the eMails ordered. If 10 eMails are in the folder, the first eMail have the OrderNumber 1 and the last eMail have the order Number 10
Does it work without lazy fetching?
However, sometimes the order numbers are broken. Then the order-numbers must be reindexed. In order to find a broken order, I assume the lowest order number is 1 and the highest is equal the count of emails.
which Spring version are you using?
5.1.7.RELEASE
What do you mean? I should load the EMailToEMailFolderOrdered? Or do you mean does entity fetching work in general?
I meant without the
FetchType.LAZY
Can you show your pom.xml? Same exception with eager loading.
I have about 60 pom.xml in the project.
Are you explicitly setting the version of anything related to Spring, Java EE or Hibernate?
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-envers</artifactId>
<version>5.4.28.Final</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-webmvc</artifactId>
<version>5.1.7.RELEASE</version>
</dependency>
mft.eMail doesn't have a size..?It may be that some folders have no emails. Yes
like you are trying to get the size of something that has exactly one element
if you want to use
SIZE, I think it needs to be a collectionSince I use Group it is a collection.
I don't think that is supposed to work
Also you are grouping by id
I try again using: SELECT mfr.id FROM EMailToEMailFolderOrdered mfr WHERE SIZE(mfr) > 0 AND NOT(SIZE(mfr) = MAX(mfr.orderNumber) AND MIN(mfr.orderNumber)=1) GROUP BY mfr.id
Please be patient.
which eliminates any grouping
1. Grouping by the primary key doesn't actually group anything
2. What do you expect
SIZE() to do here?You are right. I changed to
GROUP BY mfr.folder.idif you do that, you cannot
SELECT mfr.id2. Size should give the count of eMails in the folder.
I changed it to mfr.folder.id accordingly.
Query is now:
SELECT mfr.folder.id FROM EMailToEMailFolderOrdered mfr WHERE SIZE(mfr) > 0 AND NOT(SIZE(mfr) = MAX(mfr.orderNumber) AND MIN(mfr.orderNumber)=1) GROUP BY mfr.folder.idDo you want
or something like that
Max/Min works without group?
you know that
WHERE is executed before grouping by, right?
?
oh I gorgot the group by
edited
WHERE is always without grouping
edited againSo you can never use min or max in where? Are you sure?
(except subquery)
not for grouping
WHERE filters the elements before grouping
HAVING filters the elements after groupingI tried this and hibernate yelled: "ERROR: aggregate functions are not allowed in WHERE"
Ok, your query works fine
Thanks
If you are finished with your post, please close it.
If you are not, please ignore this message.
Note that you will not be able to send further messages here after this post have been closed but you will be able to create new posts.
Post Closed
This post has been closed by <@574922348642172929>.