-->
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.  [ 15 posts ] 
Author Message
 Post subject: GROUP BY a many-to-one association
PostPosted: Mon May 10, 2004 5:28 pm 
Beginner
Beginner

Joined: Thu Mar 04, 2004 11:51 am
Posts: 34
I'm unable to get this to work:

sales.by.region.mtd = \
select \
sls.location, \
max(sls.salesDate), \
sum(sls.sales), \
sum(sls.costOfGoodsSold), \
sum(sls.transactionCount), \
sum(sls.lastYearsSales), \
max(sls.budgetedSales), \
max(sls.salesDaysInMonthToDate) \
from com.fubar.DailyLocationSales sls \
where sls.salesDate between ? and ? \
and sls.location.region.id = ? \
group by sls.location

sls.location is defined in the mapping file for DailyLocationSales as
<many-to-one name="location"
class="com.fubar.Location"
column="location_id"
outer-join="true"
not-null="true"
/>
The complaint is
java.sql.SQLException: ERROR: Attribute location1_.location_id must be GROUPed or used in an aggregate function

In other words, the sql trying to access the LOCATION table's location_id
rather than use the SALES table's location_id.

I see examples in the manual of using aggregate functions, and I see examples of using joins, but I see no examples of where the element that
is being joined on is in the GROUP BY clause.

What, if anything, am I missing?


Top
 Profile  
 
 Post subject:
PostPosted: Mon May 10, 2004 5:34 pm 
Hibernate Team
Hibernate Team

Joined: Mon Aug 25, 2003 9:11 pm
Posts: 4592
Location: Switzerland
try: select location.id, .... group by location.id

_________________
JAVA PERSISTENCE WITH HIBERNATE
http://jpwh.org
Get the book, training, and consulting for your Hibernate team.


Top
 Profile  
 
 Post subject:
PostPosted: Mon May 10, 2004 5:38 pm 
Beginner
Beginner

Joined: Thu Mar 04, 2004 11:51 am
Posts: 34
I was hoping to avoid this. I can do it, but then I no longer have DailyLocationSales objects (which own a full up Location, not just a LocationId).


Top
 Profile  
 
 Post subject:
PostPosted: Mon May 10, 2004 5:42 pm 
Beginner
Beginner

Joined: Thu Mar 04, 2004 11:51 am
Posts: 34
Tried this and "select location.id, .... group by location.id" also fails with the same error message.


Top
 Profile  
 
 Post subject:
PostPosted: Mon May 10, 2004 6:02 pm 
Hibernate Team
Hibernate Team

Joined: Mon Aug 25, 2003 9:11 pm
Posts: 4592
Location: Switzerland
Code:
select loc
max(sls.salseDate),
...
from DailyLocationSales sls
join sls.location as loc
where ...
group by loc.id, loc.and, loc.all, loc.other, loc.simple, loc.properties


Looks a bit weird, but is semantically correct and works.

_________________
JAVA PERSISTENCE WITH HIBERNATE
http://jpwh.org
Get the book, training, and consulting for your Hibernate team.


Top
 Profile  
 
 Post subject:
PostPosted: Mon May 10, 2004 6:24 pm 
Beginner
Beginner

Joined: Thu Mar 04, 2004 11:51 am
Posts: 34
Thanks, but
Still no joy:

sales.by.region.mtd = \
select \
loc, \
max(sls.salesDate), \
sum(sls.sales), \
sum(sls.costOfGoodsSold), \
sum(sls.transactionCount), \
sum(sls.lastYearsSales), \
max(sls.budgetedSales), \
max(sls.salesDaysInMonthToDate) \
from com.fubar.DailyLocationSales sls \
join sls.location loc \
where sls.salesDate between ? and ? \
and loc.region.id = ? \
group by loc.id, loc.locationCode, loc.city, \
loc.open, loc.usingCounterpoint, loc.saturdayWeightInBudget, \
loc.sundayWeightInBudget

and we still get

java.sql.SQLException: ERROR: Attribute location1_.location_id must be GROUPed or used in an aggregate function

For some reason by the time we get down to SQL we've forgotten that
loc.id and location.location_id are mapped. It thinks we haven't grouped by this field, but we have.

There are several other non-simple properties of location, but none are relevant here.

by the way, in case you're curious:
location.id is defined as
<id name="id" type="int" unsaved-value="null" >
<column name="location_id" sql-type="serial" not-null="true"/>
<generator class="native"/>
</id>

Postgres version 7.3.4
Hibernate version 2.1.2


Top
 Profile  
 
 Post subject:
PostPosted: Mon May 10, 2004 6:29 pm 
Hibernate Team
Hibernate Team

Joined: Mon Aug 25, 2003 9:11 pm
Posts: 4592
Location: Switzerland
select i, max(b.amount.value) from Bid b join b.item as i group by i.id, i.name, i.description

This works for me, it's a many-to-one between Bid and Item.

_________________
JAVA PERSISTENCE WITH HIBERNATE
http://jpwh.org
Get the book, training, and consulting for your Hibernate team.


Top
 Profile  
 
 Post subject:
PostPosted: Mon May 10, 2004 6:36 pm 
Beginner
Beginner

Joined: Thu Mar 04, 2004 11:51 am
Posts: 34
Certainly seems that it should work, and yet it doesn't in my case. Unless you can come up with a better idea, I am going to drop down to bare SQL and do it that way. Seems like a bug to me.


Top
 Profile  
 
 Post subject:
PostPosted: Mon May 10, 2004 6:40 pm 
Hibernate Team
Hibernate Team

Joined: Mon Aug 25, 2003 9:11 pm
Posts: 4592
Location: Switzerland
I'm pretty sure its not a bug, but you haven't shown the full mapping and code yet.

_________________
JAVA PERSISTENCE WITH HIBERNATE
http://jpwh.org
Get the book, training, and consulting for your Hibernate team.


Top
 Profile  
 
 Post subject:
PostPosted: Mon May 10, 2004 6:50 pm 
Beginner
Beginner

Joined: Thu Mar 04, 2004 11:51 am
Posts: 34
The location mapping:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping
PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<!--
Document : Location.hbm.xml
-->

<hibernate-mapping>

<class name="com.fubar.Location"
table="location">

<!-- A 32 hex character is our surrogate key. It's automatically
generated by Hibernate with the identity pattern. -->
<id name="id" type="int" unsaved-value="null" >
<column name="location_id" sql-type="serial" not-null="true"/>
<generator class="native"/>
</id>

<property name="locationCode">
<column
name="location_code"
sql-type="char(3)"
not-null="true"
unique="true"
/>
</property>

<!-- the rest of this file is yet to be written. Not needed
for phase 1.
-->
<property name="city">
<column name="city" sql-type="varchar(25)" not-null="true"/>
</property>

<many-to-one name="state"
class="com.fubar.State"
column="state_id"
cascade="none"
outer-join="true"
not-null="false"
/> <!-- a location may be a dummy, having no state -->

<many-to-one name="payrollGroup"
class="com.fubar.PayrollGroup"
column="payroll_frequency_id"
cascade="none"
outer-join="true"
not-null="false"
/>

<many-to-one name="group"
class="com.fubar.LocationGroup"
column="group_id"
outer-join="true"
not-null="false"
/>

<many-to-one name="region"
class="com.fubar.LocationRegion"
column="region_id"
outer-join="true"
not-null="false"
/>

<many-to-one name="district"
class="com.fubar.LocationDistrict"
column="district_id"
outer-join="true"
not-null="false"
/>

<property name="open">
<column name="open" sql-type="boolean" not-null="true"/>
</property>
<property name="usingCounterpoint">
<column name="cp_flag" sql-type="boolean" not-null="true"/>
</property>

<property name="saturdayWeightInBudget" column="weight_in_budget_sat" type="int"/>
<property name="sundayWeightInBudget" column="weight_in_budget_sun" type="int"/>

</class>

</hibernate-mapping>

The DailyLocationSales mapping:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping
PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<!--
Document : DailyLocationSales.hbm.xml
-->

<hibernate-mapping>

<class name="com.fubar.DailyLocationSales"
table="daily_sales_v">

<!-- A 32 hex character is our surrogate key. It's automatically
generated by Hibernate with the identity pattern. -->
<id name="id" type="integer" unsaved-value="null" >
<column name="sales_id" sql-type="serial" not-null="true"/>
<generator class="native"/>
</id>

<many-to-one name="location"
class="com.fubar.Location"
column="location_id"
outer-join="true"
not-null="true"
/>

<property name="salesDate" column="sales_date" type="date" not-null="true"/>
<property name="sales" type="java.math.BigDecimal"/>
<property name="costOfGoodsSold" column="cost" type="java.math.BigDecimal"/>
<property name="transactionCount" column="txact_count" type="integer"/>
<property name="lastYearsSales" column="last_years_sales" type="java.math.BigDecimal"/>
<property name="budgetedSales" column="budgeted_sales" type="java.math.BigDecimal"/>
<property name="salesDaysInMonthToDate" column="sales_days_month_to_date" type="java.math.BigDecimal"/>

</class>

</hibernate-mapping>


Perhaps the most interesting point to consider here is that daily_sales_v is actually a view, not a table. Could that be part of the problem?


Top
 Profile  
 
 Post subject:
PostPosted: Mon May 10, 2004 6:55 pm 
Hibernate Team
Hibernate Team

Joined: Mon Aug 25, 2003 9:11 pm
Posts: 4592
Location: Switzerland
I have the same mapping. Check the Hibernate SQL log, this kind of query works as I have shown.

_________________
JAVA PERSISTENCE WITH HIBERNATE
http://jpwh.org
Get the book, training, and consulting for your Hibernate team.


Top
 Profile  
 
 Post subject:
PostPosted: Mon May 10, 2004 8:27 pm 
Beginner
Beginner

Joined: Thu Mar 04, 2004 11:51 am
Posts: 34
Could you be a little more specific about what you mean when you say you have the same mapping? Are you referring just to the relationship between the two classes? Do you mean that your mapping also uses a view? Does your mapping also have several many-to-ones hanging off the "one" side of the main main many-to-one? Does your mapping have as part of its where clause parameters from both sides of the main many-to-one?

I'm trying to sort out what is significant from what isn't.


Top
 Profile  
 
 Post subject:
PostPosted: Tue May 11, 2004 10:15 am 
Beginner
Beginner

Joined: Thu Mar 04, 2004 11:51 am
Posts: 34
Now (after logging the SQL) we're getting somewhere.

The above HQL Query with the above mappings produced this SQL (formatted for human readability):

select
location1_.location_id as location1_,
location1_.location_code as location2_,
location1_.city as city,
location1_.state_id as state_id,
location1_.payroll_frequency_id as payroll_5_,
location1_.group_id as group_id,
location1_.region_id as region_id,
location1_.district_id as district8_,
location1_.open as open,
location1_.cp_flag as cp_flag,
location1_.weight_in_budget_sat as weight_11_,
location1_.weight_in_budget_sun as weight_12_,
location1_.location_id as x0_0_,
max(dailylocat0_.sales_date) as x1_0_,
sum(dailylocat0_.sales) as x2_0_,
sum(dailylocat0_.cost) as x3_0_,
sum(dailylocat0_.txact_count) as x4_0_,
sum(dailylocat0_.last_years_sales) as x5_0_,
max(dailylocat0_.budgeted_sales) as x6_0_,
max(dailylocat0_.sales_days_month_to_date) as x7_0_
from daily_sales_v dailylocat0_
inner join location location1_ on
dailylocat0_.location_id=location1_.location_id
where (dailylocat0_.sales_date between ? and ? )
and(location1_.region_id=? )

Two questions arise:
1) why the second instance of location1_location_id
2) where's the group by clause? That's why we're getting the error message. Something is confusing hibernate's SQL generator. Can someone tell me what?


Top
 Profile  
 
 Post subject:
PostPosted: Tue May 11, 2004 10:37 am 
Beginner
Beginner

Joined: Thu Mar 04, 2004 11:51 am
Posts: 34
Further info:
this is the HQL hibernate creates (from the log):

hql.QueryTranslator: HQL: select loc, max(sls.salesDate), sum(sls.sales), sum(sls.costOfGoodsSold), sum(sls.transactionCount), sum(sls.lastYearsSales), max(sls.budgetedSales), max(sls.salesDaysInMonthToDate) from com.xpedx.xpediate.persist.DailyLocationSales sls join sls.location loc where sls.salesDate between ? and ? and loc.region.id = ?

Something has caused the original HQL query to truncate.

AHA! The query is read in from a properties file and the backslash at the end of the line ...and loc.region.id = ? \
had a space after it, truncating the query.

Fix that and it works, almost - but contrary to your earlier reply, Christian, I had to include every field of Location, not just the simple properties.

Thanks a lot for your help. I could never have found this on my own! However, I would like to request that the Hibernate team make it a little easier to create HQL for this situation. This one falls a little short in the ease of use area.


Top
 Profile  
 
 Post subject:
PostPosted: Tue May 11, 2004 10:42 am 
Hibernate Team
Hibernate Team

Joined: Mon Aug 25, 2003 9:11 pm
Posts: 4592
Location: Switzerland
It's pretty close to SQL, and we discussed having a translation from 'GROUP BY location" to a SQL 'group by LOC_ID, LOC_NAME, LOC_FOO' and so on, but it is not easily doable in Hibernate2. We will get it for Hibernate3.

_________________
JAVA PERSISTENCE WITH HIBERNATE
http://jpwh.org
Get the book, training, and consulting for your Hibernate team.


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 15 posts ] 

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:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.