It should work... Just to be sure I tried it on MySQL 5. It works:
1. One-to-many OUTER JOIN:
mysql> select t0.artist_id, t1.painting_title FROM artist t0 left
join painting t1 on t0.artist_id = t1.artist_id order by
t1.PAINTING_TITLE;
+-----------+----------------+
| artist_id | painting_title |
+-----------+----------------+
| 2 | NULL |
| 1 | ABAAC |
| 1 | ADDDD |
| 1 | BBDDDD |
| 1 | BDDDD |
+-----------+----------------+
2. Many to one OUTER JOIN:
mysql> select t0.painting_id, t1.artist_name FROM painting t0 left
join artist t1 on t0.artist_id = t1.artist_id order by t1.artist_name;
+-------------+-------------+
| painting_id | artist_name |
+-------------+-------------+
| 33005 | NULL |
| 33001 | X |
| 33002 | X |
| 33003 | X |
| 33004 | X |
+-------------+-------------+
Andrus
On Feb 28, 2008, at 3:15 PM, Marek Wawrzyczny wrote:
> Thanks Marcin and Andrus,
>
> Andrus, are you sure that the problem Marcin is describing can be
> solved?
>
> I thought that something like:
>
> SELECT t1.id, t1.a, t1.t2_fk, t2.id, t2.x
> FROM table1 AS t1
> LEFT JOIN table2 AS t2 ON t2.id = t1.t2_fk
> ORDER BY t2.x
>
> would never return results from t1 that do not have a related t2
> record. Of
> course it's been a while since SQL theory lectures :)
>
> Cheers,
>
> Marek Wawrzyczny
>
>
> On Thu, 28 Feb 2008 23:56:07 Andrus Adamchik wrote:
>> 3.0 EJBQLQuery should be able to handle that.
>>
>> Andrus
>>
>> On Feb 28, 2008, at 2:44 PM, Marcin Skladaniec wrote:
>>> Hi
>>>
>>> Short comment on the cayenne sorted queries, they work great but be
>>> aware of a limitation:
>>> (the query examples assume one to many relation Artist-Painting)
>>>
>>> SelectQuery query = new SelectQuery(Painting.class);
>>> query.addOrdering(Painting.ARTIST_PROPERTY
>>> +"."+Artist.LAST_NAME,true);
>>> will not return the Paintings without artist. This behaviour is
>>> correct 90% of the times, but would be awesome if it could be
>>> controlled and customized so the null in the order path does not
>>> alter the results.
>>>
>>> also be aware that a query for a relationship existence (or non-
>>> existence) does not work:
>>> SelectQuery query = new SelectQuery(Painting.class,
>>> ExpressionFactory.matchExp(Painting.ARTIST_PROPERTY, null));
>>> so you might need a flag to indicate whether the relationship is set
>>> or not to allow easy querying and/or sorting.
>>>
>>> If someone has a (simple) workaround to the problems I mentioned
>>> please share!
>>>
>>> Cheers
>>> Marcin
>>>
>>> On 29/02/2008, at 8:03 AM, Marek Wawrzyczny wrote:
>>>> Thanks Kevin,
>>>>
>>>> Your response was quite encouraging. Most of the pages are very
>>>> simple but
>>>> there is one or two which enforces quite coplex workflow. It's a
>>>> perfect
>>>> candidate for a child context.
>>>>
>>>> Just out of curiosity, how do people manage contexts in a typical
>>>> CRUD
>>>> application containing several (>10) entities. We're also using
>>>> AJAX calls
>>>> (DWR).
>>>>
>>>> Regarding queries. Hibernate often handles lazy initialized
>>>> relationships very
>>>> badly. There are two bugs I've hit where both HSQL and the Criteria
>>>> API (kind
>>>> of like the query builder API in Cayenne) have trouble creating the
>>>> SQL
>>>> query. Take the following two entities:
>>>>
>>>> Course
>>>> name
>>>> classes
>>>>
>>>> Cllass
>>>> course
>>>> startsOn
>>>> endsOn
>>>>
>>>> Hibernate could potentially have trouble building a query that
>>>> would return
>>>> courses where we discrimante on both startsOn and endsOn properties
>>>> of a
>>>> Class.
>>>>
>>>> An additional bug may prevent ordering on either startsOn or
>>>> endsOn if the
>>>> property was used in the query.
>>>>
>>>> The issue is with the way Hibernate aliases the joins in the
>>>> resultset.
>>>> Obviously, I'd want to avoid that in the future.
>>>>
>>>>
>>>> Cheers,
>>>>
>>>> Marek Wawrzyczny
>>>>
>>>> On Thu, 28 Feb 2008 00:51:59 Kevin Menard wrote:
>>>>> Hi Marek,
>>>>>
>>>>> My Hibernate experience is limited to a single project that did
>>>>> not use
>>>>> Spring, so it's hard for me to draw a fair comparison. My take
>>>>> from
>>>>> passively watching on discussion lists (particularly the Tapestry
>>>>> one)
>>>>> is that Spring makes Hibernate usable in a way that Cayenne is out
>>>>> of
>>>>> the box. It seems you know this already, though.
>>>>>
>>>>> In a Web app I have here, we an account creation operation split
>>>>> up over
>>>>> three screens and it involves several different entities. For
>>>>> this, I
>>>>> simply used a peer context per page and coalesce everything at the
>>>>> end.
>>>>> This is a little bit older code and I would likely use a child
>>>>> context
>>>>> for it now. The reason I did it this way is that while
>>>>> conceptually a
>>>>> single operation, completing any of the phases is a complete
>>>>> transaction. If you want to enforce start to finish behavior, you
>>>>> could
>>>>> use a single context shared via session. Just watch yourself
>>>>> because
>>>>> it's a lot harder to enforce a workflow through a browser than
>>>>> it is
>>>>> through Swing.
>>>>>
>>>>> As for sorting across multiple relationships, I guess I would have
>>>>> to
>>>>> understand a little more as to what you want to do. Simplest
>>>>> thing is
>>>>> to write your own Comparator, but you may want to look at
>>>>> mapping a
>>>>> query, and barring that, use SQLTemplate to achieve what you need
>>>>> in the
>>>>> DB.
>>>>>
>>>>> I hope that helps.
>
>
>
This archive was generated by hypermail 2.0.0 : Thu Feb 28 2008 - 08:28:56 EST