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