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.
