-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 17 posts ]  Go to page 1, 2  Next
Author Message
 Post subject: Hibernate query GROUP BY multiple @ManyToOne associations
PostPosted: Mon Jul 17, 2017 10:10 am 
Newbie

Joined: Mon Jul 17, 2017 10:02 am
Posts: 9
hello,
i'm a wildfly 9.0.2 developer with a "big" problem

first of all let me indicate the wildfly pom.xml with hibernate versions:

<version.org.hibernate>4.3.10.Final</version.org.hibernate>
<version.org.hibernate.commons.annotations>4.0.5.Final</version.org.hibernate.commons.annotations>
<version.org.hibernate.validator>5.1.3.Final</version.org.hibernate.validator>
<version.org.hibernate.javax.persistence.hibernate-jpa-2.1-api>1.0.0.Final</version.org.hibernate.javax.persistence.hibernate-jpa-2.1-api>
<version.org.hibernate.hql>1.1.0.Final</version.org.hibernate.hql>
<version.org.hibernate.search>5.2.0.Final</version.org.hibernate.search>

now let's go to the problem

i have an entity bean "realted" with other:

@ManyToOne
@JoinColumns({ @JoinColumn(referencedColumnName = "id", name = "idFornitoreRisorsa", nullable = false) })
private FornitoreRisorsa fornitoreRisorsa;

@ManyToOne
@JoinColumns({ @JoinColumn(referencedColumnName = "id", name = "idFornitoreTariffa", nullable = false) })
private FornitoreTariffa fornitoreTariffa;


my named query is:

@NamedQuery(name = CalendarioAttivitaView.findByTest,
query = "SELECT "
+ "a.fornitoreRisorsa,"
+ "a.fornitoreTariffa,"
+ "SUM(a.quantita), "
+ "FROM "
+ "CalendarioAttivitaView a "
+ "WHERE "
+ "a.data BETWEEN :dataInizio AND :dataFine "
+ "GROUP BY "
+ "a.fornitoreRisorsa,"
+ "a.fornitoreTariffa")

the generated sql statement is:

SELECT
calendario0_.idFornitoreRisorsa AS col_1_0_,
calendario0_.idFornitoreTariffa AS col_2_0_,
SUM(calendario0_.quantita) AS col_7_0_,
fornitorer2_.dataCreazione AS dataCrea2_6_1_,
fornitorer2_.dataModifica AS dataModi3_6_1_,
fornitorer2_.utenteCreazione AS utenteCr4_6_1_,
fornitorer2_.utenteModifica AS utenteMo5_6_1_,
fornitorer2_.cognome AS cognome6_6_1_,
fornitorer2_.idFornitore AS idForni10_6_1_,
fornitorer2_.login AS login7_6_1_,
fornitorer2_.nome AS nome8_6_1_,
fornitorer2_.note AS note9_6_1_,
fornitorer2_.idReferente AS idRefer11_6_1_,
fornitoret3_.dataCreazione AS dataCrea2_7_2_,
fornitoret3_.dataModifica AS dataModi3_7_2_,
fornitoret3_.utenteCreazione AS utenteCr4_7_2_,
fornitoret3_.utenteModifica AS utenteMo5_7_2_,
fornitoret3_.idAliquotaIva AS idAliqu13_7_2_,
fornitoret3_.assegnabile AS assegnab6_7_2_,
fornitoret3_.idFornitore AS idForni14_7_2_,
fornitoret3_.nome AS nome7_7_2_,
fornitoret3_.note AS note8_7_2_,
fornitoret3_.quantitaMassima AS quantita9_7_2_,
fornitoret3_.quantitaMinima AS quantit10_7_2_,
fornitoret3_.righeGiornaliere AS righeGi11_7_2_,
fornitoret3_.tipoTariffa AS tipoTar12_7_2_
FROM VW_CalendarioAttivita calendario0_
INNER JOIN TB_FornitoreRisorsa fornitorer2_ ON calendario0_.idFornitoreRisorsa=fornitorer2_.id
INNER JOIN TB_FornitoreTariffa fornitoret3_ ON calendario0_.idFornitoreTariffa=fornitoret3_.id
WHERE calendario0_.data BETWEEN ? AND ?
GROUP BY
calendario0_.idFornitoreRisorsa ,
calendario0_.idFornitoreTariffa


as you can see not all of the selected columns are "grouped by" (because they are part of the join) ant this works fine on mySQL (my dev env)
unfortunatelly, my production system has ms sql intalled and this doesn't works

the error is:

Column 'TB_FornitoreRisorsa.dataCreazione' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

property name="hibernate.dialect" value="org.hibernate.dialect.SQLServerDialect"

in the persistence.xml file on the jpa project but the error persists

any idea?

thanks a lot!


Top
 Profile  
 
 Post subject: Re: Column is invalid in the select list because it is not...
PostPosted: Mon Jul 17, 2017 2:17 pm 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1630
Location: Romania
While you can group by a single entity which will use the entity id for grouping by, you can't group by multiple entities.

Therefore, you need to split this query in 2:

1. The first one will GROUP BY the entity identifiers
2. The second one will use the previously fetched identifiers and fetch the desired entities, possibly joined in case there is an association between them. Otherwise, you need a separate query for every entity type.


Top
 Profile  
 
 Post subject: Re: Hibernate query GROUP BY multiple @ManyToOne associations
PostPosted: Tue Jul 18, 2017 3:45 am 
Newbie

Joined: Mon Jul 17, 2017 10:02 am
Posts: 9
hi,
thanks for the answer

in the last days, i've tried something like:

@NamedQuery(name = CalendarioAttivitaView.findByTest,
query = "SELECT "
+ "a.idFornitoreRisorsa,"
+ "a.idFornitoreTariffa,"
+ "SUM(a.quantita), "
+ "FROM "
+ "CalendarioAttivitaView a "
+ "WHERE "
+ "a.data BETWEEN :dataInizio AND :dataFine "
+ "GROUP BY "
+ "a.idFornitoreRisorsa,"
+ "a.idFornitoreTariffa")

so the result was a list with just two integers (for the ids) and one double (for the sum)
and in a loop i've queried the two joined entities, but the performances was awful

can you write a simple sample of the tho queries?
i'm sure having understood what you mean


Top
 Profile  
 
 Post subject: Re: Hibernate query GROUP BY multiple @ManyToOne associations
PostPosted: Tue Jul 18, 2017 7:00 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1630
Location: Romania
Quote:
and in a loop i've queried the two joined entities, but the performances was awful


Don't do it in a loop. Read carefully what I have told you already:

Quote:
2. The second one will use the previously fetched identifiers and fetch the desired entities, possibly joined in case there is an association between them.
Otherwise, you need a separate query for every entity type.


Top
 Profile  
 
 Post subject: Re: Hibernate query GROUP BY multiple @ManyToOne associations
PostPosted: Tue Jul 18, 2017 11:22 am 
Newbie

Joined: Mon Jul 17, 2017 10:02 am
Posts: 9
i've read carefully your answer and that's why i've asked you for a sample

if i execute the first query to obtaint the two ids and the total, after that (in my opinion) i need TWO other queries
the first one to load all the entities based on the first id selected and the the second one based on the second id selected
in any case it is not very "confortable"

i think that having group by working only on id and not giving errors on entity is a bug

thanks


Top
 Profile  
 
 Post subject: Re: Hibernate query GROUP BY multiple @ManyToOne associations
PostPosted: Tue Jul 18, 2017 11:32 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1630
Location: Romania
Quote:
i need TWO other queries
the first one to load all the entities based on the first id selected and the the second one based on the second id selected
in any case it is not very "confortable"


3 simple queries can outperform a very complex one if the Execution Plan is much more complicated. While the N+1 query problem is a serious issue because it's an accidental complexity, splitting a complex query in 2 or 3 simpler might actually be beneficial, dependding on the underlying RDBMS (e.g. MySQL).

Quote:
i think that having group by working only on id and not giving errors on entity is a bug


This feature is not a JPA requirement, and Hibernate supports it only for a single entity. However, you can simply write a native SQL query and GROUP BY all columns if you really want it. Using JPQL for projections is a code smell anyway.


Top
 Profile  
 
 Post subject: Re: Hibernate query GROUP BY multiple @ManyToOne associations
PostPosted: Wed Jul 19, 2017 7:32 am 
Newbie

Joined: Mon Jul 17, 2017 10:02 am
Posts: 9
i agree that splitting the "problem" in multiple queries can help, but a bigger effort in "data union" is requested

suppose that the first query returns a list of array with the tho ids end the sum

i have to create and array of id and execute a query like "... WHERE id IN :list) to obtain all the relater beans
same thing fot the third query

now i have to write the algorithm for "data union" that takes data from the three lists finding the correct ones by id

this is a job that usually the DB with JOIN or LEFT JOIN does very easily without requesting coding and execution on the client side


Top
 Profile  
 
 Post subject: Re: Hibernate query GROUP BY multiple @ManyToOne associations
PostPosted: Wed Jul 19, 2017 7:55 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1630
Location: Romania
Quote:
this is a job that usually the DB with JOIN or LEFT JOIN does very easily without requesting coding and execution on the client side


But you don't have to do that if that does not work for you. You can always execute the native SQL with JOINs. Check out what I said previously:

Quote:
However, you can simply write a native SQL query and GROUP BY all columns if you really want it. Using JPQL for projections is a code smell anyway.


Top
 Profile  
 
 Post subject: Re: Hibernate query GROUP BY multiple @ManyToOne associations
PostPosted: Wed Jul 19, 2017 8:29 am 
Newbie

Joined: Mon Jul 17, 2017 10:02 am
Posts: 9
that's correct, but if i have to write native queries why i loose a big part of hibernate beauty and portability


Top
 Profile  
 
 Post subject: Re: Hibernate query GROUP BY multiple @ManyToOne associations
PostPosted: Wed Jul 19, 2017 10:28 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1630
Location: Romania
There are many ways to address portability, like offering specialized data access components. Check out this article for more details.


Top
 Profile  
 
 Post subject: Re: Hibernate query GROUP BY multiple @ManyToOne associations
PostPosted: Thu Jul 20, 2017 3:45 am 
Newbie

Joined: Mon Jul 17, 2017 10:02 am
Posts: 9
i thank you very much for all the time you're spending with me, but, reading again all the treead, i don't change my mind also if surely there are several workarounds that can be used

if grouping is allowed only for one entity, i'm expectong an exception before executing the query
what we can see, instead, is that the system has been able to work with all the related entities and the group by verb includes all the relation fields:

Code:
GROUP BY
calendario0_.idFornitoreRisorsa ,
calendario0_.idFornitoreTariffa


the system has also been able to retrieve all the fields of the related entities as we can see in the "select" clause:

Code:
fornitorer2_.dataCreazione AS dataCrea2_6_1_,
fornitorer2_.dataModifica AS dataModi3_6_1_,
fornitorer2_.utenteCreazione AS utenteCr4_6_1_,
fornitorer2_.utenteModifica AS utenteMo5_6_1_,
fornitorer2_.cognome AS cognome6_6_1_,
fornitorer2_.idFornitore AS idForni10_6_1_,
fornitorer2_.login AS login7_6_1_,
fornitorer2_.nome AS nome8_6_1_,
fornitorer2_.note AS note9_6_1_,
fornitorer2_.idReferente AS idRefer11_6_1_,
fornitoret3_.dataCreazione AS dataCrea2_7_2_,
fornitoret3_.dataModifica AS dataModi3_7_2_,
fornitoret3_.utenteCreazione AS utenteCr4_7_2_,
fornitoret3_.utenteModifica AS utenteMo5_7_2_,
fornitoret3_.idAliquotaIva AS idAliqu13_7_2_,
fornitoret3_.assegnabile AS assegnab6_7_2_,
fornitoret3_.idFornitore AS idForni14_7_2_,
fornitoret3_.nome AS nome7_7_2_,
fornitoret3_.note AS note8_7_2_,
fornitoret3_.quantitaMassima AS quantita9_7_2_,
fornitoret3_.quantitaMinima AS quantit10_7_2_,
fornitoret3_.righeGiornaliere AS righeGi11_7_2_,
fornitoret3_.tipoTariffa AS tipoTar12_7_2_


and this works on mySQL because theese columns are part of jined tables and specifing them in the group by is useless
BUT mssql doesn't agree with this because it needs a strict syntax
SO having i specified to use a mssql dialect, that's why i call it BUG


Top
 Profile  
 
 Post subject: Re: Hibernate query GROUP BY multiple @ManyToOne associations
PostPosted: Thu Jul 20, 2017 5:13 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1630
Location: Romania
Quote:
SO having i specified to use a mssql dialect, that's why i call it BUG


You can write a replicating test case and open a Jira issue for it, but the priority for this one is going to be rather low. If it's really that important to you, then you will supply a Pull Request that fixes it, right?


Top
 Profile  
 
 Post subject: Re: Hibernate query GROUP BY multiple @ManyToOne associations
PostPosted: Thu Jul 20, 2017 5:36 am 
Newbie

Joined: Mon Jul 17, 2017 10:02 am
Posts: 9
now i'm pretty late with my project so i'll find the best workaround
i just hope that someone will understand the bug and fix it

thanx a lot


Top
 Profile  
 
 Post subject: Re: Hibernate query GROUP BY multiple @ManyToOne associations
PostPosted: Thu Jul 20, 2017 9:28 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1630
Location: Romania
Code:
now i'm pretty late with my project so i'll find the best workaround


We are just 5 people in the Hibernate team and we have to address:

- the development for 6.0
- fixes for 5.x
- writing documentation
- answering forum and StackOverflow questions
- taking care of infrastructure issues (site, Jenkins, etc)

So, I understand what you mean.

Since there is already a workaround (the native SQL query), it's a minor issue. If it's a major issue, you'll surely send a Pull Request.


Top
 Profile  
 
 Post subject: Re: Hibernate query GROUP BY multiple @ManyToOne associations
PostPosted: Thu Jul 20, 2017 9:49 am 
Newbie

Joined: Mon Jul 17, 2017 10:02 am
Posts: 9
i understand, thanks a lot for your great work

we cant spend some days tring to understand where the problem is, but we have never worked with hibernate sources

knowing that the version user by wildfly is:
Code:
<version.org.hibernate>4.3.10.Final</version.org.hibernate>
<version.org.hibernate.commons.annotations>4.0.5.Final</version.org.hibernate.commons.annotations>
<version.org.hibernate.validator>5.1.3.Final</version.org.hibernate.validator>
<version.org.hibernate.javax.persistence.hibernate-jpa-2.1-api>1.0.0.Final</version.org.hibernate.javax.persistence.hibernate-jpa-2.1-api>
<version.org.hibernate.hql>1.1.0.Final</version.org.hibernate.hql>
<version.org.hibernate.search>5.2.0.Final</version.org.hibernate.search>

can you give us some raccomentation to start this activity?


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 17 posts ]  Go to page 1, 2  Next

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
cron
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.