Hi all,
I'll start with standard stuff:
Hibernate version: 2.1.7c
Name and version of the database you are using: MySQL 4.0
Mapping documents (only relevant parts shown for brevity):
Code:
<hibernate-mapping>
<class
name="gr.org.bridge.business.entity.Person"
table="Persons"
dynamic-update="false"
dynamic-insert="false">
<id name="id" column="person_id" type="java.lang.Long">
<generator class="native"/>
</id>
<discriminator column="type" type="string" length="2"/>
<subclass
name="gr.org.bridge.business.entity.Player"
dynamic-update="false"
dynamic-insert="false"
discriminator-value="PL">
<map
name="masterPoints"
table="PlayerMasterPoints"
lazy="false"
sort="natural"
inverse="false"
cascade="all">
<key column="person_id"/>
<index column="date_awarded" type="java.util.Date"/>
<composite-element class="gr.org.bridge.business.value.MasterPoints">
<property
name="black"
type="int"
access="property"
column="black"
not-null="true"/>
<property
name="gold"
type="int"
access="property"
column="gold"
not-null="true"/>
<property
name="placing"
type="double"
access="property"
column="placing"
not-null="true"/>
</composite-element>
</map>
</subclass>
</class>
</hibernate-mapping>
and my db schema:
Code:
CREATE TABLE `Persons` (
`person_id` bigint(20) NOT NULL auto_increment,
`type` char(2) NOT NULL default '',
PRIMARY KEY (`person_id`),
);
CREATE TABLE `PlayerMasterPoints` (
`person_id` bigint(20) NOT NULL default '0',
`black` int(11) NOT NULL default '0',
`gold` int(11) NOT NULL default '0',
`placing` double NOT NULL default '0',
`date_awarded` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`person_id`,`date_awarded`)
);
What I wish to do is get a list of all Player objects sorted by their most recent black rating.
So I write the following HQL:
Code:
select p.id, max(indices(p.masterPoints)) from Player p inner join p.masterPoints mp group by p.id order by mp.black
and it procudes the expected result but the generated SQL uses 2 JOINs:
Code:
select
player0_.person_id as x0_0_,
max(masterpoin2_.date_awarded) as x1_0_
from
Persons player0_
inner join PlayerMasterPoints masterpoin1_
on player0_.person_id=masterpoin1_.person_id,
PlayerMasterPoints masterpoin2_
where
player0_.type='PL'
and player0_.person_id=masterpoin2_.person_id
group by
player0_.person_id
order by
masterpoin1_.black
If I were to handwrite the SQL myself I could simply do:
Code:
select person_id, max(date_awarded) from PlayerMasterPoints group by person_id order by black;
I wish I could write something along these lines:
Code:
select p.id, max(indices(p.masterPoints)) from Player p group by p.id order by elements(p.masterPoints).black
but of course it's invalid HQL.
I don't expect Hibernate to produce sql with no joins but at least one of the JOINs above is completely unnecessary as the Persons table is joined twice to the PlayerMasterPoints table.
Any help with optimizing my HQL to get better generated SQL will be greatly appreciated.
Thanks,
Giorgos
Login
FAQ
Search
