Re: SQuery Join

Hi Anthony,

so, lets take it that way: are we (I mean me and my coworkers) using
some of the functionalities that have been removed in the last commit?

1) left outer joins only
Inner joins are needless if we only support many to one joins.
Otherwise (with one to many joins), inner joins would have been useful.

==> If we only implement left joins for now, could we name the method leftJoin() ?

2) distinct processing
I think it was only an optimization hack. Removing it has no functionnal impact, AFAICT.
So, removing this is ok for me.

3) removing or()
I was not using it, since the semantic was not clear to me (precedence, ...). So I'm fine with it.

SO the result is not worse than what we had before, just a bit simplier (+), less confuse (+). But it does not add anything (ok for now).


Now regarding what I would have liked to do, and that will still not be possible in a first step (to make my examples more explicit):

Say Departments have Employees and Departments manage Products.
Restricting to many -> one joins makes impossible to query all departments that have Employees with salary < 100 and Products that are brown.
I will have to do it this way:
1. query Employees with salary < 100
2. iterate to get there depts
3. then query the brown products
4. iterate to get there depts
5. and then compare get all the depts in the dataset

Database would have done this for me with a
"select depts inner join employee on emp.dept = dept and salary < 100 inner join products on product.dept = dept and color=brown".

Second case, I want all Depts that are located in the US and their Employees if any.
With one-> many, I could have done "select Depts left join Employees on Emp.dept = dept where dept.location = US".
But instead, I will have to do :
1. select all depts located in US
2. for each dept, query its employees (or with one request, use a in clause, which might have bad performance)

Third case is I want all depts with at least one employee whose manager is Fred:

select departments join employee on emp.dept = dept join employee "as manager" on emp.manager = manager.id and manager.name = Fred
I will have to do: 
1.select emloyees whose manager is fred
2.for each employee found, query the dept
3.get all depts from the dataset

All this is not very extraordinary with a database.
So while I agree Simpleorm must be simple, for me simplicity is in the way it will map objects to database (not POJOs, no subclasses, etc.),
not in the queries that can be issued!

So lets go for a quick release, but I think we must keep an eye on the roadmap...

Franck

Le lundi 21 juillet 2008 à 21:02 +1000, Anthony Berglas a écrit :
> 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/