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). Catalina 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? >
