I looked around thinking this is answered somewhere but didn't find anything......

I'm using Hibernate 2.0.2. I'm creating the following HQL:



In the database to test, I have two Ads and 3 Redirects.
A1 links to R1 & R2
A2 links to R3

When I issue the above query A1 is returned twice (once for each Redirect record). Is this the expected functionality with the fetch keyword?

Because when I remove the fetch keyword, the query return A1 once liked I expected. Except now I have to manually initialize the collection before returing to the view....

Here are the mappings:

I've got the same problem in my tests. The insert of "FETCH" in join alter the generated query to fetch the related tables, that's ok, but in the generated query also the related table appear in the SELECT clause altering the DISTINCT semantics (actualy negating the distinct).
Is that behavor forced by the necessity of traverse the related table ?
may be a bug ?
or there's something we miss ?

Thanks.

This is known and intended behaviour (though contraversial). Applying DISTINCT in memory can be a quite expensive operation, so we prefer to leave it to the application. Just add the query results to a hashset and you will be fine....

hmmm... Ok, thanks for the heads up. I couldn't find the discussion around FETCH in the archive; but I can understand the controversial nature. This should probably go into the documentation [in big bold letters :)] or at least become an FAQ....


Thanks for the answer,
Chris....[/quote]

Note that another reason for this decision is that we prefer that if we call setMaxRows(100), then that limits the results to exactly 100 rows!

let me get this straight.....

that would be 100 rows including the collection records and not 100 rows of the parent object.

So if I had 200 object As and each had 2 object Bs in a collection then only 50 objects As would be returned?
50 As
100 Bs

I understand where you coming from, however, it's just not intuitive at all. So if I really wanted to return 100 As than I should not use FETCH in the query. I should probably run each A.getCollectionB() through Hibernate.initialize().

Is there another way to initialize all of the Bs for a set of As? Just trying to cut down on the number of queries. In my initialize example, there will be 101 queries executed (1 for A, 100 for B).

Chris....

Dude, this is a limitation of SQL and the JDBC API. I cannot possibly provide any way to limit the number of actual instances of the parent returned! Think about it ;)

Sorry, not exactly what I was asking. I understand the SQL LIMIT limitations. Given the implementation of FETCH, I was thinking outloud about how to best retrieve objects.

I have most of my children collections marked to lazy load since I need them only infrequently depending on the function being executed.

Also, this particular function is passing data back to the view layer after the session is closed and therefore lazy loading is not available.

Right now, I'm iterating through each parent object and calling Hibernate.initialize() for each child collection (hence 1 SQL call per parent object to retrieve the collection).

What I am trying to figure out (and tried to ask) is how to minimize the number of SQL queries generated.

I was wondering if there is a way to quickly retrieve all of the collection sub-objects in one query. For example, could I issue an HQL query that did something like "select b from B where b.parentID in(......)". This HQL would return all of the child objects. I would do this just to get them into the session cache.

Then if I call Hibernate.initialize() for each parent.collection, the children are loaded into the session cache. Now the question is, does Hibernate.initalize() code path use the Session cache to retrieve objects or is a SQL call still generated.

hmmm.... sounds like I'll just go play a bit and see what actually happens...

Chris....

If you enable batch loading for the collection then of course the collections are loaded by batch.