LEFT JOIN avaliable?

[Andrey Razumovsky <ra...@gmail.com>]

Hi,

Imagine I have tables A and B. A has 'aid', 'aname' and 'bid' (relation to
B, which is non-mandatory) fields. B has 'bid' and 'bname'.
I want to make a selection from A but order it by 'bname'. When there is no
B related, 'bname' must be treated as '', i.e.
SELECT aid, aname FROM a LEFT JOIN b ON (a.bid=b.bid) ORDER BY bname.

That's fine in SQL, but i can't find out how this is to be made with mapped
classes in Cayenne, because when i add an ordering, Cayenne creates query
with simple JOIN, and I need LEFT JOIN here.So how can i tell Cayenne to
create LEFT JOIN instead? I'd prefer using SelectQuery, however any other
hints will be appreciated too.

Thanks,
Andrey

Re: LEFT JOIN avaliable?

[Mike Kienenberger <mk...@gmail.com>]

Read this for outer (left) join support in Cayenne 3.....

http://cayenne.apache.org/doc/path-expressions.html

Short version:  Put a "+" after the "a.bid=b.bid" relationship path
name (probably "bid" in your example, so "a.bid+".

On 5/28/08, Andrey Razumovsky <ra...@gmail.com> wrote:
> Hi,
>
>  Imagine I have tables A and B. A has 'aid', 'aname' and 'bid' (relation to
>  B, which is non-mandatory) fields. B has 'bid' and 'bname'.
>  I want to make a selection from A but order it by 'bname'. When there is no
>  B related, 'bname' must be treated as '', i.e.
>  SELECT aid, aname FROM a LEFT JOIN b ON (a.bid=b.bid) ORDER BY bname.
>
>  That's fine in SQL, but i can't find out how this is to be made with mapped
>  classes in Cayenne, because when i add an ordering, Cayenne creates query
>  with simple JOIN, and I need LEFT JOIN here.So how can i tell Cayenne to
>  create LEFT JOIN instead? I'd prefer using SelectQuery, however any other
>  hints will be appreciated too.
>
>  Thanks,
>
> Andrey
>