Re: SQuery Join

Hello Franck,

I was thinking that initially we would Only 
support left outer joins.  Never inner 
joins.   If one really does not want Employees 
without Depatments one can simply say .isNotNull(DEPARTMENT).

>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 don't understand the type of query you are alluding to.  Examples might help.

I would certainly like to do more work on SQuery 
later.  Eg. Sub selects (correlated sub 
queries).  But my tactical agenda is that I want 
to get the current code cleaned up, well tested 
and live.   And soon.  We have been on a branch far too long already.

So my question to you is have I removed anything 
from SQuery that is important to you.  For 
example, I have removed all Distinct 
processing.  I have also removed .or().  Again examples would help.

(My code deletions are in the very last svn 
commits, so can easily be rolled back for now.)

I think that what is left in SQuery is simple 
enough to be extended later without 
regrets.  Things like .or() change the structure 
of the language -- I certainly do not want to reintroduce needsConjunction.

In the short term SSession.rawQueryDB should be 
reworked to be able to return a List of 
Map<ColumnName --> Value> which is easy and can 
be convenient for general ad hoc queries.

Regards,

Anthony

At 07:28 PM 21/07/2008, Franck Routier wrote:
>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.

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/