Re: Joins -- Philosophy

Hi again, thanks for your comments

...
>  And as discussed it still needs more work on the Select list at
> least.

I think this problem is handled by the rsindex part. See my other mail.

>  And more test cases.

Yep.

> 
> 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.)

I know that. I am also still missing your first try at DataSets, which
were complex but elegant...

> 
> 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.

Yep. In fact this works with the code as is.

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

Why not, but this only handles the case when we want to filter. This
does not allow instantiating joined records...
> 
> 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.)

That what I did. You don't have to provide an alias. If yu don't the
code defaults to the table name. This was the most simple way I found to
get unique alias for simple case (my other idea was to use a counter an
generate T1, T2, T3 etc (like Cognos Impromptu does, but this makes the
sql quite unreadable).

> 
> 4. Provide rawJoin and rawClause for special cases.

Does exist.

> 
> 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.

What do you mean by derived values ?

>  And non-trivial expressions rather than our current simple flat
> structure.

Yes, that would be much welcome.

> 
> 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 = ?))

I don't find this more intuitive than
select e.* from employee e
join manager m on e.manager_id = m.id
where m.name = 'Fred'

But that's probably a matter of taste. What I like with join synths is
that you clearly seperate clauses based on the database structure (on)
and clause that are related to your ponctual business concern (where).

> 
> 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. 

I have always liked you tact :)))

> 
> 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.

You've got a point here. But by insanciating the different parts of the
query as record instances and working on the dataset, we do overcome
this limitation of sql...

> 
> 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.
> 
Agreed. But are we going to change the sql spec soon ? :)

> The only real advantage of Joins is that extra columns can be returned
> by the one query.

This is the major advantage. This is why we need joins.

>  And that is the one thing that we do not really support very well.

Well. I think we do with the code I submitted.

>  One level should be more than enough for most cases.

It would certainly be as long as we allow one-to-many joins (PaySlip /
Employee / PaySlipDetail case). But then, one level or multi-level does
not make a great difference in complexity.
> 
> 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).

I changed the code to do that. The use can then use a LinkedHashSet to
remove duplicates if he wants.
> 
> CONCLUSION
> ==========
> 
> I have strong reservation as to the complexity of the join code.

I don't find it so complex. The major drawback is adding methods with
aliases, which make the API a bit bloated.

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

I really think my implementation works. The part that is not object
oriented is when we turn resultSet into records (reading a table)... but
this is what an ORM is for !

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

Well, I don't see what is complex with :

select * from department d
left join employee e on e.dept_id = d.dept_id
where e.salary > 10000

written in Simpleorm as 
new
SQuery<Department>(Department.meta).leftJoin(Employee.DEPARTMENT).gt(Employee.SALARY, 10000);

that would be easier as :

select * from department d
where exists(select * from employee
where d.dept_id = dept_id and salary > 10000)

written as
SQuery<Department>(Department.meta).rawCorrelate(Employee.DEPARTMENT,
Employee.Salary, "> ?", 10000));  // How to express outer join ?

+

select * from employee
where salary > 10000
and dept_id is not null

written as 
SQuery<Employee>(Employee.meta).gt(Employee.Salary,
10000).notNull(Employee.DEPT_ID);

to get the _same_ result, that is a dataset with my departments, and all
employees that are in a department and have a salary > 10000.


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

We have a lot of queries where we do things like this :

select d.* , u.quantity, r.account from detail d
join used_resource u on d.used_id = u.id and d.company = u.company
join resource r on r.id = u.res_id and r.company = u.company
where resource.group = 'a group'
and d.company = 'bigone'

translating them into :

select * from detail d
where exists(select * from used_resource u , resource r
             where r.id = u.res_id
             and r.company = u.company
             and r.group = 'a group'
             and u.company = d.company
             and d.used_id = u.id)
and d.company = 'bigone'
+
select * from used_resource u
where exists(select * from detail d
              where exists(select * from resource r
                           where r.id = u.res_id
                           and r.company = u.company
                           and r.group = 'a group'
                           and u.company = d.company
                           and d.used_id = u.id)
        )
and u.company = 'bigone'
+
select * from resource r... well, I'm not sure I'll manage this one :)


or resorting to N+1 queries, won't make things any simplier...

What do you think of it ?

Regards,

Franck





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

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/