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.  [ 12 posts ] 
Author Message
 Post subject: Many to One extra select issue
PostPosted: Mon Dec 04, 2006 4:01 pm 
Beginner
Beginner

Joined: Wed Nov 01, 2006 5:33 pm
Posts: 22
Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version: NHibernate 1.2 Beta 2

Mapping documents:


<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
<class name="MyAssembly.Domain.EntityA, MyAssembly.Domain" table="tblEntityA" lazy="true">
<id name="EntityAID" column="EntityAID" type="Int32" unsaved-value="0">
<generator class="native"/>
</id>
<property name="Name" column="EntityAName"/>
<one-to-one name="EntityB" class="MyAssembly.Domain.EntityB, MyAssembly.Domain" property-ref="EntityA" constrained="true" lazy="proxy" />
</class>
</hibernate-mapping>


<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
<class name="MyAssembly.Domain.EntityB, MyAssembly.Domain" table="tblEntityB" lazy="true">
<id name="EntityBID" column="EntityBID" type="Int32" unsaved-value="0">
<generator class="native"/>
</id>
<many-to-one name="EntityA" class="MyAssembly.Domain.EntityA, MyAssembly.Domain" column="EntityAID" lazy="proxy" />
<property name="Message" column="EntityBMessage"/>
</class>
</hibernate-mapping>




Name and version of the database you are using: SQL Server 2000

Firstly, apologies for the cross post - I seem to have accidentally posted to the Hibernate forum (and was wondering why I got no response..:o)

Hi all,

I've got a really frustrating issue. Basically I have a DB entity mapping of
EntityA (1:M) EntityB but I want it to work as a pseudo 1:1 because that is the actual use, e.g. EntityAID is unique in table EntityB.

My problem is that whenever I do a select for EntityA, even with an eager fetch for EntityB then it always does a seperate select for EntityB. I've tried every combination of lazy load, outerjoin, constrained etc. etc. I can think of but no matter what I do EntityB is always got in a seperate select when I select EntityA, even when I don't access the EntityB property of EntityA.

Is there an answer? This is the only thing now really stopping me using this for a port of our company app and it is so frustrating I can't work it out. This is an existing DB so I can't really change the schema as I did to maintain two seperate apps of the same DB.


Many thanks

Stuart Jones


Top
 Profile  
 
 Post subject:
PostPosted: Mon Dec 04, 2006 6:19 pm 
Newbie

Joined: Fri Dec 01, 2006 1:24 pm
Posts: 4
If you cannot get nHibernate to do the fetching you want automatically, you can implement lazy loading manually very easily. Keep a reference ID in the parent class with an instance variable there. In the property accessor, put the logic to do the DB fetch when you desire the object. When got, set the instance variable. Finally, change the save() routine of your object to check for the presence of the object and manage saving both objects (if ID = 0 then create an empty object, else save the instance variable first, then set the parent's DB ID reference to the object in question, finally save the parent object).


Top
 Profile  
 
 Post subject:
PostPosted: Tue Dec 05, 2006 2:12 am 
Regular
Regular

Joined: Tue Feb 21, 2006 9:50 am
Posts: 107
I have the same problem. I have read in "Hibernate in Action" that Hibernate is capable to read 1:n relations via outer joins with one SQL. I have tried out "fetch=join" and "batch-size" on the one and many side of my relation and also the "MaxFetchDepth", "StatementFetchDepth" and "StatementBatchSize" properties in the configuration. It seems to me, that NHibernate ignores the attributes. The only thing i can imagine is, that all the stuff will only work with "lazy=true". In our application we treat our object-graph in a document oriented way and so all association mappings are defined with "lazy=false" to ensure that the complete object-graph is in memory (it's because we must be able to access the database via 56K-Modem line).

The solution douga suggests don't remove the number of database roundtrips. I think this is what tunic also wants to do.

Regards
Klaus


Top
 Profile  
 
 Post subject: Re: Many to One extra select issue
PostPosted: Tue Dec 05, 2006 2:20 am 
Newbie

Joined: Tue Dec 05, 2006 1:37 am
Posts: 2
tunic wrote:
Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version: NHibernate 1.2 Beta 2

Mapping documents:


<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
<class name="MyAssembly.Domain.EntityA, MyAssembly.Domain" table="tblEntityA" lazy="true">
<id name="EntityAID" column="EntityAID" type="Int32" unsaved-value="0">
<generator class="native"/>
</id>
<property name="Name" column="EntityAName"/>
<one-to-one name="EntityB" class="MyAssembly.Domain.EntityB, MyAssembly.Domain" property-ref="EntityA" constrained="true" lazy="proxy" />
</class>
</hibernate-mapping>


<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
<class name="MyAssembly.Domain.EntityB, MyAssembly.Domain" table="tblEntityB" lazy="true">
<id name="EntityBID" column="EntityBID" type="Int32" unsaved-value="0">
<generator class="native"/>
</id>
<many-to-one name="EntityA" class="MyAssembly.Domain.EntityA, MyAssembly.Domain" column="EntityAID" lazy="proxy" />
<property name="Message" column="EntityBMessage"/>
</class>
</hibernate-mapping>




Name and version of the database you are using: SQL Server 2000

Firstly, apologies for the cross post - I seem to have accidentally posted to the Hibernate forum (and was wondering why I got no response..:o)

Hi all,

I've got a really frustrating issue. Basically I have a DB entity mapping of
EntityA (1:M) EntityB but I want it to work as a pseudo 1:1 because that is the actual use, e.g. EntityAID is unique in table EntityB.

My problem is that whenever I do a select for EntityA, even with an eager fetch for EntityB then it always does a seperate select for EntityB. I've tried every combination of lazy load, outerjoin, constrained etc. etc. I can think of but no matter what I do EntityB is always got in a seperate select when I select EntityA, even when I don't access the EntityB property of EntityA.

Is there an answer? This is the only thing now really stopping me using this for a port of our company app and it is so frustrating I can't work it out. This is an existing DB so I can't really change the schema as I did to maintain two seperate apps of the same DB.


Many thanks

Stuart Jones


Top
 Profile  
 
 Post subject: Re: Many to One extra select issue
PostPosted: Tue Dec 05, 2006 2:21 am 
Newbie

Joined: Tue Dec 05, 2006 1:37 am
Posts: 2
tunic wrote:
Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version: NHibernate 1.2 Beta 2

Mapping documents:


<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
<class name="MyAssembly.Domain.EntityA, MyAssembly.Domain" table="tblEntityA" lazy="true">
<id name="EntityAID" column="EntityAID" type="Int32" unsaved-value="0">
<generator class="native"/>
</id>
<property name="Name" column="EntityAName"/>
<one-to-one name="EntityB" class="MyAssembly.Domain.EntityB, MyAssembly.Domain" property-ref="EntityA" constrained="true" lazy="proxy" />
</class>
</hibernate-mapping>


<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
<class name="MyAssembly.Domain.EntityB, MyAssembly.Domain" table="tblEntityB" lazy="true">
<id name="EntityBID" column="EntityBID" type="Int32" unsaved-value="0">
<generator class="native"/>
</id>
<many-to-one name="EntityA" class="MyAssembly.Domain.EntityA, MyAssembly.Domain" column="EntityAID" lazy="proxy" />
<property name="Message" column="EntityBMessage"/>
</class>
</hibernate-mapping>




Name and version of the database you are using: SQL Server 2000

Firstly, apologies for the cross post - I seem to have accidentally posted to the Hibernate forum (and was wondering why I got no response..:o)

Hi all,

I've got a really frustrating issue. Basically I have a DB entity mapping of
EntityA (1:M) EntityB but I want it to work as a pseudo 1:1 because that is the actual use, e.g. EntityAID is unique in table EntityB.

My problem is that whenever I do a select for EntityA, even with an eager fetch for EntityB then it always does a seperate select for EntityB. I've tried every combination of lazy load, outerjoin, constrained etc. etc. I can think of but no matter what I do EntityB is always got in a seperate select when I select EntityA, even when I don't access the EntityB property of EntityA.

Is there an answer? This is the only thing now really stopping me using this for a port of our company app and it is so frustrating I can't work it out. This is an existing DB so I can't really change the schema as I did to maintain two seperate apps of the same DB.


Many thanks

Stuart Jones


Top
 Profile  
 
 Post subject:
PostPosted: Tue Dec 05, 2006 4:21 am 
Beginner
Beginner

Joined: Wed Nov 01, 2006 5:33 pm
Posts: 22
Hi peeps

What I'd ideally like to do is have a unidirectional one-to-one mapping from ClassA to ClassB. I don't care whether class B is lazy loaded or not, in fact I am more than happy for it to be eager loaded.

I don't need the reference to ClassA from ClassB at all for my application.

However, ClassA and ClassB both have their own primary key. ClassB contains the ClassA key as a foreign key.

My problem is no matter how I retrieve ClassA, NHibernate always performs and additional select for ClassB - even if ClassA was loaded
with a fetch join to ClassB. And even if I don't access any properties of ClassB.

I've noticed Sergey has posted about 6 months ago that what I want to do isn't possible withouth the <JOIN> tag, which isn't currently in NHibernate. Can this be confirmed, and if so, are there plans to implement it?

Effectively my entire (legacy) database is implemented with lots of these pseudo 1:1 relationships which in db terms are actually 1:M. The reasoning is simple, the M part is contextual - hence in any given context it is only 1:1 but I have to apply another foreign key check (in HQL) to filter for the appropriate record. Maybe my answer is in a composite key....but can this still support a native ID generator?


Top
 Profile  
 
 Post subject:
PostPosted: Tue Dec 05, 2006 4:49 am 
Beginner
Beginner

Joined: Wed Nov 01, 2006 5:33 pm
Posts: 22
Having just checked there appears to be no way to use a native generator class as part of a composite key.

I am currently investigating implementing what I need using a custom <sql-query> but this looks like a last resort and it appears I won't be able to use HQL for this (and ideally I'd like to load by 2 WHERE clause keys, the parent key and a secondary FK).


Top
 Profile  
 
 Post subject:
PostPosted: Tue Dec 05, 2006 5:57 am 
Newbie

Joined: Thu Nov 23, 2006 4:32 am
Posts: 3
I have exactly the same problem. http://forum.hibernate.org/viewtopic.php?t=967675

Even when you tell the parent to fetch children with the fetch=join in the one-to-one mapping NHibernate issues a separate select for each child. And in this case it actually fetch the child twice...

What's strange is that one-to-one behaves differently from one-to-many which is able to issue one and only one select to get all objects.

If I would have time, I would look at the source code but it will not happen and I will live with it...

What would be interesting would be to check that Hibernate does the same.

Florent


Top
 Profile  
 
 Post subject:
PostPosted: Wed Dec 06, 2006 4:55 pm 
Beginner
Beginner

Joined: Wed Nov 01, 2006 5:33 pm
Posts: 22
Right, I have made some progress....

Firstly, I have found that the property-ref of the one-to-one relationship doesn't need to map to a parent object - the property-ref can point to the foreign key in the associated object. This creates a uni-directional mapping.

This means in testing I have been able to prove that it isn't the many-to-one object causing the extra select but the one-to-one side.

This makes it all the more confusing as I can fetch the associated entity eagerly using inner join, as per mapping or per HQL. Whatever happens it will always do this additional select.

Does anyone know whether this issue exists with Hibernate, as in, something likely to be changed in future. Afterall, the object must be loaded already so I am sure there should be some solution....


Top
 Profile  
 
 Post subject:
PostPosted: Tue Dec 12, 2006 4:59 pm 
Beginner
Beginner

Joined: Wed Nov 01, 2006 5:33 pm
Posts: 22
Hi all,

I've done some more experimenting on this issue. Up until now I had been trying different combinations of HQL. Now I've done lots of experiments using CreateCriteria API to see if that behaves any differently.

With HQL I had concluded if I couldn't remove the extra select then I'd at least minimise the call to the parent object as I needed to filter by an associated parent key.

So my HQL was:

select enta from EntityA enta join enta.EntityB where enta.EntityB.anotherID = 123

which would cause EntityA and EntityB to do an inner join but it doesn't return all of the fields from EntityB as part of that select, because I know the additional select will populate that object anyway (despite my best efforts).

My next issue is that no matter what I do with CreateCriteria to do the same thing, either with a second CreateCriteria, or with a CreateAlias this always returns all fields from table Entity B as part of the first select, despite still doing the extra select.

The reason this is an issue is because in light of the extra select issue I want to minimise the amount of SQL reads. Does anyone know if it is possible that I can do the equivalient to my HQL above using CreateCriteria without cause the first of two selects to include all of the fields from the second select? Is this, or the original issue a bug? It all seems a bit strange really.


Top
 Profile  
 
 Post subject:
PostPosted: Thu Dec 14, 2006 7:20 am 
Beginner
Beginner

Joined: Wed Nov 01, 2006 5:33 pm
Posts: 22
Any comments?


Top
 Profile  
 
 Post subject:
PostPosted: Fri Dec 15, 2006 9:15 am 
Beginner
Beginner

Joined: Wed Nov 01, 2006 5:33 pm
Posts: 22
I've solved my many-to-one extra select issue, and the answer was so obvious in the end I can't believe I missed it.

I didn't realise you can map a unidirectional 1:1 with a many-to-one tag, hence doing this, and setting the mapping to inner join accordingly means I only perform a single select, which subsequenly resolves the n+1 select issue.

So the answer was simple in the end, don't use a one-to-one tag unless you want the relationship to be bi-directional and don't mind the extra select.

It just needed me to think about things in a different light. :o)


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 12 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.