openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "KARR, DAVID (ATTCINW)" <dk0...@att.com>
Subject Can I "join fetch" a one-to-many if it can be empty?
Date Mon, 11 Jan 2010 22:57:44 GMT
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
View raw message