> -----Original Message----- > From: catalina wei [mailto:[email protected]] > Sent: Monday, January 11, 2010 7:10 PM > To: [email protected] > Subject: Re: Can I "join fetch" a one-to-many if it can be empty? > > David, > If you want a join fetch to produce left join results, you would > specify > LEFT keyword on join fetch, like below: > "select ctgry from Category ctgry LEFT join fetch ctgry.childProducts > where > ctgry.id = :id" > > The one-to-one relation is not annotated with optional=false (default > is > optional=true), then for loading that one-to-one relation, the LEFT > join is > generated for default (optional=true). > If you want a inner join for toOne relation and you know that this > toOne > relation can never be null, then add optional=false to get inner join > SQL. > (example, @OneToOne(optional=false); @ManyToOne(optional=false). > When optional=false is used, LEFT join fetch request still gets INNER > join > SQL (because optional=false).
Curious. So "optional" is an allowed attribute for "OneToOne" and "ManyToOne", but not "OneToMany"? In any case, changing to a "left" join fixed my empty list problem. > On Mon, Jan 11, 2010 at 2:57 PM, KARR, DAVID (ATTCINW) > <[email protected]>wrote: > > > I have an entity "Category" that has a "one-to-many" field called > > "childProducts". I believe I had been running a query like the > > following with no problems: > > > > "select ctgry from Category ctgry join fetch ctgry.childProducts > where > > ctgry.id = :id" > > > > When the "childProducts" list wasn't empty, I got some child > products. > > When it was empty, I got an empty list. That's fine. > > > > Then I went on and made more changes, for instance adding another > field > > to Category which is a one-to-one. I didn't change this query. When > I > > run it now, if I have child products, I get a reasonable result. If > I > > do NOT have a child product, however, the query returns no rows, > which > > causes an error. > > > > If I change the query to remove the "join fetch ctgry.childProducts", > it > > then returns rows (one) again. > > > > When I look at the generated SQL, I can understand why the "join > fetch" > > is causing it to return no rows. It's doing an inner join on > products, > > not an outer join. > > > > Here's the SQL without the "join fetch": > > > > SELECT t0.CATEGORY_ID, t1.CATEGORY_ID, t1.DESCRIPTION, > t1.DISPLAY_NAME, > > t1.SEO_LABEL, t0.CREATION_DATE, t0.DISPLAY_NAME FROM CATEGORY t0, > > CATEGORY_ES t1 WHERE (t0.CATEGORY_ID = ?) AND t0.CATEGORY_ID = > > t1.CATEGORY_ID(+) > > > > And with: > > > > SELECT t0.CATEGORY_ID, t1.CATEGORY_ID, t1.DESCRIPTION, > t1.DISPLAY_NAME, > > t1.SEO_LABEL, t0.CREATION_DATE, t0.DISPLAY_NAME, t2.CATEGORY_ID, > > t3.PRODUCT_ID, t3.PRODUCT_TYPE, t3.CREATION_DATE, t3.DISPLAY_NAME > FROM > > CATEGORY t0, DCS_CATEGORY_ES t1, CAT_CHLDPRD t2, DCS_PRODUCT t3 WHERE > > (t0.CATEGORY_ID = ?) AND t0.CATEGORY_ID = t1.CATEGORY_ID(+) AND > > t0.CATEGORY_ID = t2.CATEGORY_ID AND t2.CHILD_PRD_ID = t3.PRODUCT_ID > > ORDER BY t2.CATEGORY_ID ASC > > > > I tried changing this query manually in my SQL browser, changing the > > last two conditions to outer joins (adding "(+)" to the right side of > > the condition), and that makes it return the row I need. > > > > The XML for the "childProducts" relationship is the following: > > > > <one-to-many name="childProducts" target-entity="Product"> > > <join-table name="CAT_CHLDPRD"> > > <join-column name="CATEGORY_ID" > > referenced-column-name="CATEGORY_ID"/> > > <inverse-join-column name="CHILD_PRD_ID" > > referenced-column-name="PRODUCT_ID"/> > > </join-table> > > </one-to-many> > > > > What am I missing here? > >
