Joins -- Philosophy

Hello Franck,

Now the big question -- is implementing full joins a good idea in the first place?

I have only spent a couple of hours working through your join code, but it is now probably the most conceptually complex part of SimpleORM, which is a problem.  And as discussed it still needs more work on the Select list at least.  And more test cases.

It hurt when you deleted my foreign key generation code long ago and thus weakened SimpleORM.  But you made it much simpler, and I think that that was a good trade off.  I am wondering whether the full join code might be another case of the same sort of thing.  (Likewise it hurt when I needed to delete the first cut of the DataSet split code.)


ALTERNATIVES
===========

What I would suggest is:-

1. Implement one level joins as before your changes, no recursive joins, but add outer joins.  This does not require any clever alias processing.

2. Implement a simple calculus approach using subqueries as described below.  Much easier to understand than full joins and again no complex table aliases.

3. Always provide an simple single alias for the top level table, default being the first letter of the table name.  This enables recursive subqueries.  (Need to then use the alias consistently everywhere for Oracle.)

4. Provide rawJoin and rawClause for special cases.

I believe that 1,2 and 3 will cover 95% of all cases naturally and efficiently.  We can then resort to option 4 for the last 5% without complicating SimpleOrm.

There are then other directions that the query language could be extended later.  In particular to return derived values.  And non-trivial expressions rather than our current simple flat structure.


CALCULUS APPROACH
================

To keep it simple I would prefer to focus on the calculus approach.  Ie. have an operator

SQuery(Employee).eq(Employee.MANAGER, Employee.Name, "Fred")

OR

...rawCorrellate(Employee.MANAGER, Employee.Name, "= ?", "Fred")
...rawCorrellate(Employee.MANAGER, "NAME = ?", "Fred") // be sure to generate ()s.

These can generate the sub-select in a very intuitive way.  There is no need for aliases on the inner tables as the SQL queries are scoped.  No issues with outer joins and missing rows.  

(The generated query is
  SELECT * FROM EMPLOYEE E 
   WHERE EXISTS (SELECT * FROM EMPLOYEE
      WHERE E.EMP_ID = EMP_ID AND (NAME = ?))

N+1 queries can always be converted to 1+1 using subqueries.  But I suspect that single level outer joins will handle virtually all the normal cases as single queries anyway.


RANT: ALGEBRA VS CALCULUS
======================

Your problems below about the result basically document why I think that joins are generally a bad idea period.  

The "Alegebra" approach with joins Join is about a Cartesian product, very counter intuitive.   The result is a new Relation that is a *copy* of data out of the original tables with "outer" rows then poked back in.  This then makes it difficult to relate the result back to one of the original tables.  Ie.  
  Result =copy table1 joinop table2 joinop table2.   
The algebra is absolutely not about relating the result back to one of the original tables for update. "Result" is a new relation, a first class object, to be used in further expressions.  Nothing ever gets updated in the relational algebra -- it is functional.  Ie. exactly how we do NOT use a database.

On the other hand the "Calculus" approach is about filtering the primary table by reference to secondary tables.  Ie.  the set 
  {r:T | P(r)}   // set r of type T where P(r) is true.
P(r) can reference other tables.  This means corollated subqueries in SQL.  No new "Result" relations are created, nothing needs to be matched.

It is a *major* problem with SQL syntax that the more natural calculus approach is ugly, but the weird join approach is easy.  So instead of writing 
  "employee.Department.name = 'Count Penies' " 
we need to write 
  "Exists (select * from Department where department.id  = employee.departmentId and name = 'Count Penies' )".  
However we can address this with our simple query language.  And then all of your list problems go away, including the outer join one.

The only real advantage of Joins is that extra columns can be returned by the one query.  And that is the one thing that we do not really support very well.  One level should be more than enough for most cases.

As to your return type problem, I suspect you should just return duplicates, that is what the weird semantics are (I have not thought this through properly).

CONCLUSION
==========

I have strong reservation as to the complexity of the join code.

I would much prefer to add subqueries before full join processing.  They are much easier to implement and are more object oriented.

I believe that once implemented the need for full joins will largely disappear.   So we can then keep it simple.

Please have a look at your actual queries, and see if they can be done naturally as subqueries.  If so then simplicity wins.

Am I missing something important?

Regards,

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/