You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openjpa.apache.org by Patrick Linskey <pl...@gmail.com> on 2007/07/05 16:02:11 UTC

Re: Wrong join type on relation traversal.

Hi,

Can you post your persistence.xml and Company.java?
Generally-speaking, I believe that you should see an outer join if
your model is configured accordingly.

-Patrick

On 7/5/07, Daniel Migowski <dm...@ikoffice.de> wrote:
>
>  Hello OpenJPA users,
>
>  if have the following OpenJPA query. It gets all company-ids and for the
> companies having a lastmodifiedby-user, it should return the username:
>
>  select c.id, c.lastModifiedBy.username from Company c
>
>  The resulting native query is:
>
>  SELECT t0.id, t1.username FROM Company t0 INNER JOIN dbuser t1 ON
> t0.lastModifiedBy_id = t1.id
>
>  This one does an inner join on the dbuser table, but i need to fetch the
> records by a left join. In my opinion, the left join should be default on
> relation traversial operations, because then you could easiely have the
> other behavoiur with a simple "WHERE c.lastModifiedBy IS NOT NULL". With the
> current behaviour i have to do UNION-SELECTs if i just want to have every
> field. Is there a way to change this behaviour?
>
>  Greetings,
>  Daniel Migowski
>


-- 
Patrick Linskey
202 669 5907

Re: Wrong join type on relation traversal.

Posted by Patrick Linskey <pl...@gmail.com>.
Hmm. So I think that the spec is clear that you'd get an outer join if
you had done something like:

    select c from Company c
        where c.lastModifiedBy.username = :name or c.lastModifiedBy is null

and that you'd get an inner join if you did:

    select c from Company c
        where c.lastModifiedBy.username = :name

However, in your case, you're only using the path expression in the
SELECT clause. I think that section 4.4.4 of the spec is the problem
here:

"Path expression navigability is composed using "inner join"
semantics. That is, if the value of a non-terminal association-field
in the path expression is null, the path is considered to have no
value, and does not participate in the determination of the result."

Applied to the context of a query that has no WHERE clause, this is
what's causing the behavior you're seeing.

I think that an explicit join should do the trick:

    select c.id, user.username from Company c left join c.lastModifiedBy user

-Patrick

On 7/5/07, Daniel Migowski <dm...@ikoffice.de> wrote:
> Hi Patrick,
>
> thank you for your reply. My persistence.xml list the company and the user:
>
> <?xml version="1.0" encoding="UTF-8"?>
>
> <persistence xmlns="http://java.sun.com/xml/ns/persistence"
>     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
>     version="1.0">
>
>     <persistence-unit name="none" transaction-type="RESOURCE_LOCAL">
>         <class>de.ikoffice.model.EntityBase</class>
> ...
>         <class>de.ikoffice.app.core.model.Company</class>
> ...
>         <class>de.ikoffice.app.core.model.User</class>
>     </persistence-unit>
>
>     <!-- persistence unit for IKOffice -->
>     <persistence-unit name="ikoffice" transaction-type="RESOURCE_LOCAL">
>         <class>de.ikoffice.model.EntityBase</class>
> ...
>         <class>de.ikoffice.app.core.model.Company</class>
> ...
>         <class>de.ikoffice.app.core.model.User</class>
>     </persistence-unit>
> </persistence>
>
> In Company.java I have the following definition:
>
>     @ManyToOne(cascade=CascadeType.PERSIST)
>     User lastModifiedBy;
>
> Any ideas?
>
> With best regards,
> Daniel Migowski
>
> Patrick Linskey schrieb:
> > Hi,
> >
> > Can you post your persistence.xml and Company.java?
> > Generally-speaking, I believe that you should see an outer join if
> > your model is configured accordingly.
> >
> > -Patrick
> >
> > On 7/5/07, Daniel Migowski <dm...@ikoffice.de> wrote:
> >>
> >>  Hello OpenJPA users,
> >>
> >>  if have the following OpenJPA query. It gets all company-ids and for
> >> the
> >> companies having a lastmodifiedby-user, it should return the username:
> >>
> >>  select c.id, c.lastModifiedBy.username from Company c
> >>
> >>  The resulting native query is:
> >>
> >>  SELECT t0.id, t1.username FROM Company t0 INNER JOIN dbuser t1 ON
> >> t0.lastModifiedBy_id = t1.id
> >>
> >>  This one does an inner join on the dbuser table, but i need to fetch
> >> the
> >> records by a left join. In my opinion, the left join should be
> >> default on
> >> relation traversial operations, because then you could easiely have the
> >> other behavoiur with a simple "WHERE c.lastModifiedBy IS NOT NULL".
> >> With the
> >> current behaviour i have to do UNION-SELECTs if i just want to have
> >> every
> >> field. Is there a way to change this behaviour?
> >>
> >>  Greetings,
> >>  Daniel Migowski
> >>
> --
>
>  |¯¯|¯¯|    IKOffice GmbH             Daniel Migowski
>  |  |  |/|                            Mail: dmigowski at ikoffice.de
>  |  | // |  Nordstr. 10               Tel.: +49 (441) 21 98 89 52
>  |  | \\ |  26135 Oldenburg           Fax.: +49 (441) 21 98 89 55
>  |__|__|\|  http://www.ikoffice.de    Mob.: +49 (176) 22 31 20 76
>
>             Geschäftsführer: Ingo Kuhlmann, Daniel Migowski
>             Amtsgericht Oldenburg, HRB 201467
>
>


-- 
Patrick Linskey
202 669 5907

Re: Wrong join type on relation traversal.

Posted by Daniel Migowski <dm...@ikoffice.de>.
Hi Patrick,

thank you for your reply. My persistence.xml list the company and the user:

<?xml version="1.0" encoding="UTF-8"?>

<persistence xmlns="http://java.sun.com/xml/ns/persistence"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    version="1.0">

    <persistence-unit name="none" transaction-type="RESOURCE_LOCAL">
        <class>de.ikoffice.model.EntityBase</class>
...
        <class>de.ikoffice.app.core.model.Company</class>
...
        <class>de.ikoffice.app.core.model.User</class>
    </persistence-unit>

    <!-- persistence unit for IKOffice -->
    <persistence-unit name="ikoffice" transaction-type="RESOURCE_LOCAL">
        <class>de.ikoffice.model.EntityBase</class>
...
        <class>de.ikoffice.app.core.model.Company</class>
...
        <class>de.ikoffice.app.core.model.User</class>
    </persistence-unit>
</persistence>

In Company.java I have the following definition:

    @ManyToOne(cascade=CascadeType.PERSIST)
    User lastModifiedBy;

Any ideas?

With best regards,
Daniel Migowski

Patrick Linskey schrieb:
> Hi,
>
> Can you post your persistence.xml and Company.java?
> Generally-speaking, I believe that you should see an outer join if
> your model is configured accordingly.
>
> -Patrick
>
> On 7/5/07, Daniel Migowski <dm...@ikoffice.de> wrote:
>>
>>  Hello OpenJPA users,
>>
>>  if have the following OpenJPA query. It gets all company-ids and for 
>> the
>> companies having a lastmodifiedby-user, it should return the username:
>>
>>  select c.id, c.lastModifiedBy.username from Company c
>>
>>  The resulting native query is:
>>
>>  SELECT t0.id, t1.username FROM Company t0 INNER JOIN dbuser t1 ON
>> t0.lastModifiedBy_id = t1.id
>>
>>  This one does an inner join on the dbuser table, but i need to fetch 
>> the
>> records by a left join. In my opinion, the left join should be 
>> default on
>> relation traversial operations, because then you could easiely have the
>> other behavoiur with a simple "WHERE c.lastModifiedBy IS NOT NULL". 
>> With the
>> current behaviour i have to do UNION-SELECTs if i just want to have 
>> every
>> field. Is there a way to change this behaviour?
>>
>>  Greetings,
>>  Daniel Migowski
>>
-- 

 |¯¯|¯¯|    IKOffice GmbH             Daniel Migowski
 |  |  |/|                            Mail: dmigowski at ikoffice.de
 |  | // |  Nordstr. 10               Tel.: +49 (441) 21 98 89 52
 |  | \\ |  26135 Oldenburg           Fax.: +49 (441) 21 98 89 55
 |__|__|\|  http://www.ikoffice.de    Mob.: +49 (176) 22 31 20 76
 
            Geschäftsführer: Ingo Kuhlmann, Daniel Migowski
            Amtsgericht Oldenburg, HRB 201467