Re: Joins -- Philosophy

I'am happy you got convinced :)

I also agree join semantics can become weird, as join are not truly
associative, nor commutative. We can state it in the doc (but this is
not our fault, it's ansi sql, ins'nt it ?).

White paper and docs still need updating...

Franck


Le mardi 24 mars 2009 à 14:22 +1000, [email protected] a écrit :
> Hello Franck,
> 
> I like your examples. So, it looks like the full joins with aliases
> are going in.
> 
> The advantage of subselects is that aliases are easy -- no need for
> separate object. With easy semantics.
> 
> The N+1 problem is overstated. The N is the number of *distinct*
> Departments (say) for employees.
> 
> It is very hard to understand join queries like
> http://www.developmentnow.com/g/113_2004_8_0_0_431852/inner-and-outer-joins-not-associative.htm
> 
> But if it does go in then we need to keep it clean. Please no separate
> Join Class. And no special cases for the main table. 
> 
> And we need to document the fact that joined values are (normally)
> retrieved. And the white paper needs updating.
> 
> Anthony
> 
> >> 
> >> 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
> >
> >
> 
> 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/