David, yes, JPA spec defines "optional" attribute on ToOne relations only.
Catalina On Tue, Jan 12, 2010 at 8:37 AM, KARR, DAVID (ATTCINW) <[email protected]>wrote: > > -----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? > > > >
