Re: SQuery Join

Hi Anthony,

I'm a bit ill at ease with restricting SQuery that much!

I'd rather add the ability to either join (meaning inner join) or
leftJoin, with two distinct methods. Getting all departments that have
employees respecting some criteria and and sub-departments respecting
others is a perfectly legitimate request (so, one -> many). Same with
leftJoin, ie all departments, and their employees is they exists...

I also think aliasing tables will be a must, to allow multiple joins on
the same table (I do that every day for "Celko" like tree
representations). This means table A join Table B as B1 join Table B as
B2 (so two levels)

Doing relational algebra is what a database is good at, and the main
reason you would want to afford the (huge) overhead of using one. ORM's
already restrict you to existing relations (it's uneasy to create new
one on the fly), so restricting query possibilities makes things really
unappealing... I admit we can always use raw sql, but then, we are not
using an ORM.

Until now we have bypassed this shortcoming in Simpleorm by adding a
rawJoin in SQuery, but I'd really like to enable real join possibilities
in Sorm3.

On the other hand, I'm speaking, but you did the work on SQuery :) I
recognize implementing joins is a lot of work. But postponing it to an
uncertain future should only be done if we are sure the API won't break
then.

What do you think ?

Franck



Le dimanche 20 juillet 2008 à 21:07 +1000, Anthony Berglas a écrit :
> Hello Franck,
> 
> I have been reading through the Join code, tidying things up a 
> bit.  But I have also substantially cut down the provided 
> functionality in the last commit.
> 
> In particular, Joins now can only go one level from the many 
> table.  And we can only go from the many to the One.  So
>    new SQuery(Department).join(Employee) // NOT allowed.
> I'm not sure the above made that much sense anyway.
> 
> With that gone, so is Distinct processing. (I might have over purged 
> here. svn diff is your friend.)
> 
> I have also changed the default SelectMode to be NORMAL, not 
> NONE.  Seems more normal to me.  (Change it back if you think NONE is better.)
> 
> I don't think that I have removed any very useful functionality.
> 
> The previous semantics were a bit woolly IMHO.  To do full joins one 
> really needs to get control of the table aliases which we do not want 
> to do now.  And this affects the API, which we need to keep 
> clean.  Chains of references should really have the chain specified 
> explicitly rather than infer it.  But I would rather just not do it 
> at all for now.
> 
> The important OUTER join code is also simpler to write if there is 
> only one level, many table to one table.  We need to put the 
> conditions in the "ON" clause rather than the WHERE.
> 
> (Remember that one can always write raw SQL for unusual cases.  Goal 
> is to keep SimpleOrm simple.)
> 
> Another priority is to clean up the code around SPreparedStatement, 
> SSessionDatabase and SQuerySql.  It is quite messy at the moment, 
> control bounces all over the place.  I have removed some redundant 
> state from SPreparedStatement, but more needs to be done.  Once it is 
> all clean, then more could be added.
> 
> (I also started working on sub selects.  So that we can write
>          SQuery<Employee> subQ2 = new SQuery<Employee>(Employee.meta)
>            .eq(Employee.MANAGER, Employee.NAME,  "One00");
> to retrieve employee's whose managers  name is One00.  But it is not 
> complete, and I should probably not have started it.  Actually, I 
> have just deleted it -- need to stay focused.  I think that I had had 
> similar code in an early version of SimpleOrm, before SQuery.)
> 
> And there is also the problem of IS NULL vs EQ that remains 
> outstanding.  (eq(field, null) should automatically generate IS NULL 
> code, we should hide that from the user.  But the test is not really 
> isNull, but isEmpty -- Oracle treats "" like 
> NULL.  SRecordInstance.isEmpty is probably wrong.)
> 
> Please let me know what you think.
> 
> Anthony
> 
> 
> 
> Dr Anthony Berglas, [email protected]       Mobile: +61 4 4838 8874
> Just because it is possible to push twigs along the ground with ones nose
> does not necessarily mean that is the best way to collect firewood.



------------------------------------

Yahoo! Groups Links

<*> To visit your group on the web, go to:
    http://groups.yahoo.com/group/SimpleORM/

<*> Your email settings:
    Individual Email | Traditional

<*> To change settings online go to:
    http://groups.yahoo.com/group/SimpleORM/join
    (Yahoo! ID required)

<*> To change settings via email:
    mailto:[email protected] 
    mailto:[email protected]

<*> To unsubscribe from this group, send an email to:
    [email protected]

<*> Your use of Yahoo! Groups is subject to:
    http://docs.yahoo.com/info/terms/