Re: SQuery Join
Anthony Berglas <[email protected]>
| Newsgroups | gmane.comp.java.orm.simpleorm |
|---|---|
| Message-ID | <[email protected]> |
Hello Franck and Eric,
leftJoin() is a better name. (But I will only
rename once we have implemented the left
join!) Hibernate actually calls .leftJoin
something like .eagerFetch, which is essentially what it does.
Thanks for your examples, now I understand. It
never occurred to me to use Joins the way that
you have, I would have done each of them with sub
selects (in SQL, regardless of SimpleOrm).
Specifically, the algebra oriented query
SELECT DISTINCT D.* FROM DEPARTMENTS D, EMPLOYEES E, PRODUCTS P
WHERE E.DEPT_NR = D.DEPT_NR AND E.SALARY < 100
AND P.DEPT_NR = D.DEPT_NR AND P.COLOR = BROWN
becomes the calculus oriented query
SELECT D.* FROM DEPARTMENTS D
WHERE EXISTS (SELECT * FROM EMPLOYEE E WHERE
E.DEPT_NR = D.DEPT_NR AND E.SALARY < 100)
AND EXISTS (SELECT * FROM PRODUCT P WHERE
P.DEPT_NR = D.DEPT_NR AND P.COLOR = BROWN)
To me the latter is much clearer, despite the awkward SQL syntax.
The second query directly reflects your English
formulation of the problem, ie.
"query all departments that have (ie. Exists)
Employees with salary < 100 and Products that are brown."
You did not say
"Join departments, employees and products and
filter by salary < 100 and Products that are brown, and return distinct rows."
As the queries become more complex getting the
Join approach to work properly (with outer joins
etc.) can become quite confusing, IMHO.
Regardless, the second approach can easily be
done now in SimpleOrm by adding a raw clause to
the where. The following is an existing test case
SQuery<Employee> subQ1 = new SQuery<Employee>(Employee.meta)
.rawPredicate("? = (SELECT BUDGET FROM
XX_DEPARTMENT D WHERE D.DEPT_ID = XX_EMPLOYEE.DEPT_ID)")
.rawParameter(20000);
Not as pretty as we would like, but not too bad for a non-trivial query.
Your third query can also be directly expressed
as an Exisits subquery "all depts with at least
one employee whose manager is Fred".
Your second case is harder "all Depts that are
located in the US and their Employees if
any". I would simply make this two simple
queries, one for departments, another for
employees. The overhead will be tiny, and I believe it would be clearer.
One thing that I will do is add a rawFrom(...)
clause that can be used to replace the from
clause of the query. This could be used in
conjunction with rawPredicate() to implement anything.
The recursive queries can also almost be implemented using sub selects, eg.
SELECT * FROM EMPLOYEE E WHERE 'FRED' =
(SELECT M.NAME FROM EMPLOYEE M WHERE E.NAME = M.NAME)
(The thing that we are lacking at the moment is
the alias E. I might add this in for the short
term. But it could be poked in using the rawFrom().)
And in a future version this could be written elegantly as
new SQuery(EMPLOYEE).eq(MANAGER, NAME, "Fred");
So... While I think that there is certainly more
to do with SQuery, I also believe that effective
use of subqueries will address almost all of your needs in practice.
I propose that short term changes to SQuery be limited to:-
+ Outer Join, on clause, rename to .outerJoin()
+ rawFrom().
(Or possibly just rawQuery() that provides the entire sql.
But we currently retrieve column by order, not name.)
+ Default alias for main query table, being first letter of table name.
This will support the recursive query above.
But apparently then need to use the alias in
the select list or Oracle breaks.
+ Fix rawQueryDB to return a List of Maps. This
is handy for add hoc SQL queries.
+ Later .eq(MANAGER, NAME, "Fred")
To go further with .or() etc. we really need
something like TopLink's expression builder. The
linear structure of SQuery does not capture it properly.
It should be something nested like
QExpression qx = new SQueryExpressionBuilder(); // stateless class
// (each qx method returns an object that represents part of a query tree.)
SQuery myq = new SQuery(EMPLOYEE).add(
qx.and(qx.gt(SALARY, 10000),
qx.or(
qx.eq(qx.subquery(MANAGER, NAME), "Fred"),
qx.eq(HAPPY, Boolean.TRUE)
)));
This nested style is more powerful, but it is
also much more ugly for the simple cases. We
probably do not want to support both.
My feeling is that we stick with the simple
linear structure, and let people just use raw SQL
for more complex cases, which are (hopefully)
relatively rare. A few things like or can be
poked into the linear structure by having
.orBegin()....orEnd(). But this will not scale
to a full expression language, which I don't think that we need.
What are your thoughts?
For Toplink, see
http://www.oracle.com/technology/products/ias/toplink/doc/1013/MAIN/_html/qrybas002.htm
(Toplink was the first place that I saw this type of thing long ago.)
Or Hibernate query by criteria does a similar thing
session.CreateCriteria(User.class)
.add(
Restrictions.or(
Restrictions.like("name", "G%"),
Restrictions.like("nameOops", "H%") ) );
We could make it a bit less ugly, but is this somewhere we really want to go?
Questions for later.
I certainly do not want to invent a whole new query language like HQL!
Thanks for all of your feedback.
Anthony
At 11:44 PM 21/07/2008, Franck Routier wrote:
>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.
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/