openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From catalina wei <catalina....@gmail.com>
Subject Re: Can I "join fetch" a one-to-many if it can be empty?
Date Tue, 12 Jan 2010 18:31:29 GMT
David,
yes, JPA spec defines "optional" attribute  on ToOne relations only.

Catalina

On Tue, Jan 12, 2010 at 8:37 AM, KARR, DAVID (ATTCINW) <dk068x@att.com>wrote:

> > -----Original Message-----
> > From: catalina wei [mailto:catalina.wei@gmail.com]
> > Sent: Monday, January 11, 2010 7:10 PM
> > To: users@openjpa.apache.org
> > 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)
> > <dk068x@att.com>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?
> > >
>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message