While I've got a pretty good handle on loading collections, I'm not very good at loading many-to-one relations. I was wondering what would be the best and quickest way to make business lazy on my RnsTransaction mapping. The reason I don't want to load it is because the query is carrying a lot of baggage when I really don't need it (this is true for most queries). In the below query dump, you can see that rns transaction is getting the business, it's country and it's administrator. However, my view that uses the results of this query only needs the RnsTransaction and it's histories.
HQL]
Code:
select transaction from RnsTransaction as transaction left outer join transaction.histories as history where history.date = ( select max( history.date ) from RnsTransactionHistory as history where history.rnsTransaction = transaction ) and business_id = 2 order by history.date desc
SQL Queries Being Generated:Code:
[10:25:22,562]DEBUG SQL:237 - select rnstransac0_.id as id, rnstransac0_.business_id as business2_, rnstransac0_.cargo_control_number as cargo_co3_, rnstransac0_.transaction_number as transact4_, rnstransac0_.service_option_id as service_5_, rnstransac0_.release_office_id as release_6_, rnstransac0_.sub_location_id as sub_loca7_, rnstransac0_.container_number as containe8_ from rns_transaction rnstransac0_ left outer join rns_transaction_history histories1_ on rnstransac0_.id=histories1_.rns_transaction_id where (histories1_.date=(select max(rnstransac2_.date) from rns_transaction_history rnstransac2_ where (rnstransac2_.rns_transaction_id=rnstransac0_.id )))and(business_id=2 ) order by histories1_.date desc limit ?
[10:25:22,625]DEBUG SQL:237 - select business0_.id as id3_, business0_.administrator_id as administ2_3_, business0_.mailbox as mailbox3_, business0_.name as name3_, business0_.address as address3_, business0_.city as city3_, business0_.province as province3_, business0_.postal_code as postal_c8_3_, business0_.country_id as country_id3_, business0_.phone_number as phone_n10_3_, business0_.fax_number as fax_number3_, business0_.account_state as account12_3_, business0_.type as type3_, administra1_.id as id0_, administra1_.name as name0_, administra1_.address as address0_, administra1_.city as city0_, administra1_.province as province0_, administra1_.postal_code as postal_c6_0_, administra1_.country_id as country_id0_, administra1_.phone_number as phone_nu8_0_, administra1_.fax_number as fax_number0_, country2_.id as id1_, country2_.name as name1_, country2_.currency_code as currency3_1_, country3_.id as id2_, country3_.name as name2_, country3_.currency_code as currency3_2_ from business business0_ left outer join administrator administra1_ on business0_.administrator_id=administra1_.id left outer join country country2_ on administra1_.country_id=country2_.id left outer join country country3_ on business0_.country_id=country3_.id where business0_.id=?
[10:25:22,687]DEBUG SQL:237 - select histories0_.id as id__, histories0_.rns_transaction_id as rns_tran2___, histories0_.id as id1_, histories0_.rns_transaction_id as rns_tran2_1_, histories0_.date as date1_, histories0_.release_code_id as release_4_1_, histories0_.edi_message as edi_mess5_1_, histories0_.delivery_instructions as delivery6_1_, releasecod1_.id as id0_, releasecod1_.name as name0_, releasecod1_.number as number0_ from rns_transaction_history histories0_ left outer join release_code releasecod1_ on histories0_.release_code_id=releasecod1_.id where histories0_.rns_transaction_id=? order by histories0_.date desc
[10:25:22,703]DEBUG SQL:237 - select histories0_.id as id__, histories0_.rns_transaction_id as rns_tran2___, histories0_.id as id1_, histories0_.rns_transaction_id as rns_tran2_1_, histories0_.date as date1_, histories0_.release_code_id as release_4_1_, histories0_.edi_message as edi_mess5_1_, histories0_.delivery_instructions as delivery6_1_, releasecod1_.id as id0_, releasecod1_.name as name0_, releasecod1_.number as number0_ from rns_transaction_history histories0_ left outer join release_code releasecod1_ on histories0_.release_code_id=releasecod1_.id where histories0_.rns_transaction_id=? order by histories0_.date desc
[10:25:22,718]DEBUG SQL:237 - select histories0_.id as id__, histories0_.rns_transaction_id as rns_tran2___, histories0_.id as id1_, histories0_.rns_transaction_id as rns_tran2_1_, histories0_.date as date1_, histories0_.release_code_id as release_4_1_, histories0_.edi_message as edi_mess5_1_, histories0_.delivery_instructions as delivery6_1_, releasecod1_.id as id0_, releasecod1_.name as name0_, releasecod1_.number as number0_ from rns_transaction_history histories0_ left outer join release_code releasecod1_ on histories0_.release_code_id=releasecod1_.id where histories0_.rns_transaction_id=? order by histories0_.date desc
Hibernate version: 2.1.6
Mapping documents: (pertinent to problem)Code:
<class
name="com.upfactor.rns.domain.business.Business"
table="business"
>
<id name="id" column="id" type="long" unsaved-value="0">
<generator class="sequence">
<param name="sequence">business_id_seq</param>
</generator>
</id>
<many-to-one name="administrator" column="administrator_id" class="com.upfactor.rns.domain.business.Administrator"/>
<property name="mailbox" column="mailbox"/>
<property name="name" column="name"/>
<property name="address" column="address"/>
<property name="city" column="city"/>
<property name="province" column="province"/>
<property name="postalCode" column="postal_code"/>
<many-to-one name="country" column="country_id" class="com.upfactor.rns.domain.Country"/>
<property name="phoneNumber" column="phone_number"/>
<property name="faxNumber" column="fax_number"/>
<set name="persons" table="business_to_person" lazy="true">
<key column="business_id"/>
<many-to-many column="person_id" class="com.upfactor.rns.domain.business.Person"/>
</set>
<property name="accountState" column="account_state"/>
<property name="type" column="type"/>
</class>
<class name="com.upfactor.rns.domain.core.RnsTransaction" table="rns_transaction">
<id name="id" column="id" type="long" unsaved-value="0">
<generator class="sequence">
<param name="sequence">rns_transaction_id_seq</param>
</generator>
</id>
<many-to-one name="business" column="business_id"
class="com.upfactor.rns.domain.business.Business" />
<property name="cargoControlNumber" column="cargo_control_number" />
<property name="transactionNumber" column="transaction_number" />
<many-to-one name="serviceOption" column="service_option_id" class="com.upfactor.rns.domain.core.ServiceOption"/>
<many-to-one name="releaseOffice" column="release_office_id" class="com.upfactor.rns.domain.core.ReleaseOffice"/>
<many-to-one name="subLocation" column="sub_location_id" class="com.upfactor.rns.domain.core.SubLocation"/>
<property name="containerNumber" column="container_number" />
<bag name="histories" table="rns_transaction_history" inverse="true"
order-by="date desc" cascade="save-update">
<key column="rns_transaction_id"/>
<one-to-many class="com.upfactor.rns.domain.core.RnsTransactionHistory" />
</bag>
</class>
Postgres 7.5
Login
FAQ
Search
