You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Bruno René Santos <br...@holos.pt> on 2011/01/05 01:14:36 UTC
Left Outer Joins without expressions
Hello all,
Is there any way to create a left outer join without using an actual
path on the query expressions? I tried something like this:
query.addPrefetch(HoTiposFechoContas.TO_HO_TABLE_PROPERTY +
"+").setSemantics(PrefetchTreeNode.JOINT_PREFETCH_SEMANTICS);
but cayenne raises an exception because of the concatenated '+' sign.
Regards
Bruno
--
Bruno René Santos | brunorene@holos.pt <ma...@holos.pt> |
Gestor de Projectos | Analista | Programador | Tech Hunter
Holos - Soluções Avançadas em Tecnologias de Informação S.A.
Parque de Ciência e Tecnologia de Almada/Setúbal . Edifício Madan Parque
Rua dos Inventores . Quinta da Torre . 2825 - 182 Caparica . Portugal
Phone: +351 210 438 686 . Fax: +351 210 438 687 . Web: www.holos.pt
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they are
addressed. If you are not the intended recipient or the person
responsible for delivering the email to the intended recipient, be
advised that you have received this email in error and that any use,
dissemination, forwarding, printing, or copying of this email is
strictly prohibited. If you have received this email in error please
notify Carlos Fazenda by telephone on +351 210 438 686
Re: Left Outer Joins without expressions
Posted by Bruno René Santos <br...@holos.pt>.
Hello Andrus,
I am so sorry you are right Cayenne is generating outer joins. The
problem was that i was checking one of the prefetches for a Many-to-one
relationship where the second query uses an inner join to join the
related table with the root table.
Thank you
Bruno
Em 06-01-2011 09:13, Andrus Adamchik escreveu:
> Which version of Cayenne are you using?
>
> I am checking our unit tests (Cayenne 3.1, but this part hasn't changed from 3.0) including simple to-one and to-many joint prefetching and both generate outer joins:
>
> SELECT DISTINCT t0.DATE_OF_BIRTH, t0.ARTIST_ID, t0.ARTIST_NAME, t1.ESTIMATED_PRICE, t1.PAINTING_DESCRIPTION, t1.PAINTING_TITLE, t1.ARTIST_ID, t1.GALLERY_ID, t1.PAINTING_ID FROM ARTIST t0 LEFT JOIN PAINTING t1 ON (t0.ARTIST_ID = t1.ARTIST_ID)
>
> SELECT t0.ARTIST_ID, t0.PAINTING_ID, t0.GALLERY_ID, t0.PAINTING_DESCRIPTION, t0.ESTIMATED_PRICE, t0.PAINTING_TITLE, t1.ARTIST_NAME, t1.DATE_OF_BIRTH, t1.ARTIST_ID FROM PAINTING t0 LEFT JOIN ARTIST t1 ON (t0.ARTIST_ID = t1.ARTIST_ID) ORDER BY t0.PAINTING_ID
>
> Andrus
>
> On Jan 6, 2011, at 1:37 AM, Bruno René Santos wrote:
>
>> Hello Andrus,
>>
>> In Mysql the generated join is:
>>
>> ... from table1 join table2
>>
>> which is an inner join... I needed a
>>
>> ... from table1 left join table2
>>
>> Regards
>> Bruno
>>
>> Em 05-01-2011 18:20, Andrus Adamchik escreveu:
>>> IIRC in 3.0 PrefetchTreeNode.JOINT_PREFETCH_SEMANTICS generates an outer join by default (???)
>>>
>>> Andrus
>>>
>>> On Jan 5, 2011, at 2:14 AM, Bruno René Santos wrote:
>>>
>>>> Hello all,
>>>>
>>>> Is there any way to create a left outer join without using an actual path on the query expressions? I tried something like this:
>>>>
>>>> query.addPrefetch(HoTiposFechoContas.TO_HO_TABLE_PROPERTY + "+").setSemantics(PrefetchTreeNode.JOINT_PREFETCH_SEMANTICS);
>>>>
>>>> but cayenne raises an exception because of the concatenated '+' sign.
>>>>
>>>> Regards
>>>> Bruno
>>>> --
>>>> Bruno René Santos | brunorene@holos.pt<ma...@holos.pt> | Gestor de Projectos | Analista | Programador | Tech Hunter
>>>>
>>>> Holos - Soluções Avançadas em Tecnologias de Informação S.A.
>>>> Parque de Ciência e Tecnologia de Almada/Setúbal . Edifício Madan Parque
>>>> Rua dos Inventores . Quinta da Torre . 2825 - 182 Caparica . Portugal
>>>> Phone: +351 210 438 686 . Fax: +351 210 438 687 . Web: www.holos.pt
>>>>
>>>>
>>>> This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you are not the intended recipient or the person responsible for delivering the email to the intended recipient, be advised that you have received this email in error and that any use, dissemination, forwarding, printing, or copying of this email is strictly prohibited. If you have received this email in error please notify Carlos Fazenda by telephone on +351 210 438 686
>>>>
>>>
>>
>> --
>> Bruno René Santos | brunorene@holos.pt<ma...@holos.pt> | Gestor de Projectos | Analista | Programador | Tech Hunter
>>
>> Holos - Soluções Avançadas em Tecnologias de Informação S.A.
>> Parque de Ciência e Tecnologia de Almada/Setúbal . Edifício Madan Parque
>> Rua dos Inventores . Quinta da Torre . 2825 - 182 Caparica . Portugal
>> Phone: +351 210 438 686 . Fax: +351 210 438 687 . Web: www.holos.pt
>>
>>
>> This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you are not the intended recipient or the person responsible for delivering the email to the intended recipient, be advised that you have received this email in error and that any use, dissemination, forwarding, printing, or copying of this email is strictly prohibited. If you have received this email in error please notify Bruno René Santos by telephone on +351 210 438 686
>>
>
>
--
Bruno René Santos | brunorene@holos.pt <ma...@holos.pt> |
Gestor de Projectos | Analista | Programador | Tech Hunter
Holos - Soluções Avançadas em Tecnologias de Informação S.A.
Parque de Ciência e Tecnologia de Almada/Setúbal . Edifício Madan Parque
Rua dos Inventores . Quinta da Torre . 2825 - 182 Caparica . Portugal
Phone: +351 210 438 686 . Fax: +351 210 438 687 . Web: www.holos.pt
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they are
addressed. If you are not the intended recipient or the person
responsible for delivering the email to the intended recipient, be
advised that you have received this email in error and that any use,
dissemination, forwarding, printing, or copying of this email is
strictly prohibited. If you have received this email in error please
notify Bruno René Santos by telephone on +351 210 438 686
Re: Left Outer Joins without expressions
Posted by Andrus Adamchik <an...@objectstyle.org>.
Which version of Cayenne are you using?
I am checking our unit tests (Cayenne 3.1, but this part hasn't changed from 3.0) including simple to-one and to-many joint prefetching and both generate outer joins:
SELECT DISTINCT t0.DATE_OF_BIRTH, t0.ARTIST_ID, t0.ARTIST_NAME, t1.ESTIMATED_PRICE, t1.PAINTING_DESCRIPTION, t1.PAINTING_TITLE, t1.ARTIST_ID, t1.GALLERY_ID, t1.PAINTING_ID FROM ARTIST t0 LEFT JOIN PAINTING t1 ON (t0.ARTIST_ID = t1.ARTIST_ID)
SELECT t0.ARTIST_ID, t0.PAINTING_ID, t0.GALLERY_ID, t0.PAINTING_DESCRIPTION, t0.ESTIMATED_PRICE, t0.PAINTING_TITLE, t1.ARTIST_NAME, t1.DATE_OF_BIRTH, t1.ARTIST_ID FROM PAINTING t0 LEFT JOIN ARTIST t1 ON (t0.ARTIST_ID = t1.ARTIST_ID) ORDER BY t0.PAINTING_ID
Andrus
On Jan 6, 2011, at 1:37 AM, Bruno René Santos wrote:
> Hello Andrus,
>
> In Mysql the generated join is:
>
> ... from table1 join table2
>
> which is an inner join... I needed a
>
> ... from table1 left join table2
>
> Regards
> Bruno
>
> Em 05-01-2011 18:20, Andrus Adamchik escreveu:
>> IIRC in 3.0 PrefetchTreeNode.JOINT_PREFETCH_SEMANTICS generates an outer join by default (???)
>>
>> Andrus
>>
>> On Jan 5, 2011, at 2:14 AM, Bruno René Santos wrote:
>>
>>> Hello all,
>>>
>>> Is there any way to create a left outer join without using an actual path on the query expressions? I tried something like this:
>>>
>>> query.addPrefetch(HoTiposFechoContas.TO_HO_TABLE_PROPERTY + "+").setSemantics(PrefetchTreeNode.JOINT_PREFETCH_SEMANTICS);
>>>
>>> but cayenne raises an exception because of the concatenated '+' sign.
>>>
>>> Regards
>>> Bruno
>>> --
>>> Bruno René Santos | brunorene@holos.pt<ma...@holos.pt> | Gestor de Projectos | Analista | Programador | Tech Hunter
>>>
>>> Holos - Soluções Avançadas em Tecnologias de Informação S.A.
>>> Parque de Ciência e Tecnologia de Almada/Setúbal . Edifício Madan Parque
>>> Rua dos Inventores . Quinta da Torre . 2825 - 182 Caparica . Portugal
>>> Phone: +351 210 438 686 . Fax: +351 210 438 687 . Web: www.holos.pt
>>>
>>>
>>> This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you are not the intended recipient or the person responsible for delivering the email to the intended recipient, be advised that you have received this email in error and that any use, dissemination, forwarding, printing, or copying of this email is strictly prohibited. If you have received this email in error please notify Carlos Fazenda by telephone on +351 210 438 686
>>>
>>
>>
>
>
> --
> Bruno René Santos | brunorene@holos.pt <ma...@holos.pt> | Gestor de Projectos | Analista | Programador | Tech Hunter
>
> Holos - Soluções Avançadas em Tecnologias de Informação S.A.
> Parque de Ciência e Tecnologia de Almada/Setúbal . Edifício Madan Parque
> Rua dos Inventores . Quinta da Torre . 2825 - 182 Caparica . Portugal
> Phone: +351 210 438 686 . Fax: +351 210 438 687 . Web: www.holos.pt
>
>
> This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you are not the intended recipient or the person responsible for delivering the email to the intended recipient, be advised that you have received this email in error and that any use, dissemination, forwarding, printing, or copying of this email is strictly prohibited. If you have received this email in error please notify Bruno René Santos by telephone on +351 210 438 686
>
Re: Left Outer Joins without expressions
Posted by Bruno René Santos <br...@holos.pt>.
Hello Andrus,
In Mysql the generated join is:
... from table1 join table2
which is an inner join... I needed a
... from table1 left join table2
Regards
Bruno
Em 05-01-2011 18:20, Andrus Adamchik escreveu:
> IIRC in 3.0 PrefetchTreeNode.JOINT_PREFETCH_SEMANTICS generates an outer join by default (???)
>
> Andrus
>
> On Jan 5, 2011, at 2:14 AM, Bruno René Santos wrote:
>
>> Hello all,
>>
>> Is there any way to create a left outer join without using an actual path on the query expressions? I tried something like this:
>>
>> query.addPrefetch(HoTiposFechoContas.TO_HO_TABLE_PROPERTY + "+").setSemantics(PrefetchTreeNode.JOINT_PREFETCH_SEMANTICS);
>>
>> but cayenne raises an exception because of the concatenated '+' sign.
>>
>> Regards
>> Bruno
>> --
>> Bruno René Santos | brunorene@holos.pt<ma...@holos.pt> | Gestor de Projectos | Analista | Programador | Tech Hunter
>>
>> Holos - Soluções Avançadas em Tecnologias de Informação S.A.
>> Parque de Ciência e Tecnologia de Almada/Setúbal . Edifício Madan Parque
>> Rua dos Inventores . Quinta da Torre . 2825 - 182 Caparica . Portugal
>> Phone: +351 210 438 686 . Fax: +351 210 438 687 . Web: www.holos.pt
>>
>>
>> This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you are not the intended recipient or the person responsible for delivering the email to the intended recipient, be advised that you have received this email in error and that any use, dissemination, forwarding, printing, or copying of this email is strictly prohibited. If you have received this email in error please notify Carlos Fazenda by telephone on +351 210 438 686
>>
>
>
--
Bruno René Santos | brunorene@holos.pt <ma...@holos.pt> |
Gestor de Projectos | Analista | Programador | Tech Hunter
Holos - Soluções Avançadas em Tecnologias de Informação S.A.
Parque de Ciência e Tecnologia de Almada/Setúbal . Edifício Madan Parque
Rua dos Inventores . Quinta da Torre . 2825 - 182 Caparica . Portugal
Phone: +351 210 438 686 . Fax: +351 210 438 687 . Web: www.holos.pt
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they are
addressed. If you are not the intended recipient or the person
responsible for delivering the email to the intended recipient, be
advised that you have received this email in error and that any use,
dissemination, forwarding, printing, or copying of this email is
strictly prohibited. If you have received this email in error please
notify Bruno René Santos by telephone on +351 210 438 686
Re: Left Outer Joins without expressions
Posted by Andrus Adamchik <an...@objectstyle.org>.
IIRC in 3.0 PrefetchTreeNode.JOINT_PREFETCH_SEMANTICS generates an outer join by default (???)
Andrus
On Jan 5, 2011, at 2:14 AM, Bruno René Santos wrote:
> Hello all,
>
> Is there any way to create a left outer join without using an actual path on the query expressions? I tried something like this:
>
> query.addPrefetch(HoTiposFechoContas.TO_HO_TABLE_PROPERTY + "+").setSemantics(PrefetchTreeNode.JOINT_PREFETCH_SEMANTICS);
>
> but cayenne raises an exception because of the concatenated '+' sign.
>
> Regards
> Bruno
> --
> Bruno René Santos | brunorene@holos.pt <ma...@holos.pt> | Gestor de Projectos | Analista | Programador | Tech Hunter
>
> Holos - Soluções Avançadas em Tecnologias de Informação S.A.
> Parque de Ciência e Tecnologia de Almada/Setúbal . Edifício Madan Parque
> Rua dos Inventores . Quinta da Torre . 2825 - 182 Caparica . Portugal
> Phone: +351 210 438 686 . Fax: +351 210 438 687 . Web: www.holos.pt
>
>
> This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you are not the intended recipient or the person responsible for delivering the email to the intended recipient, be advised that you have received this email in error and that any use, dissemination, forwarding, printing, or copying of this email is strictly prohibited. If you have received this email in error please notify Carlos Fazenda by telephone on +351 210 438 686
>