You are viewing a plain text version of this content. The canonical link for it is here.
Posted to jdo-dev@db.apache.org by Michael Bouschen <mb...@spree.de> on 2006/06/23 00:18:29 UTC
Re: JDOQL Subquery proposals
Hi,
I agree to what Craig mentioned in the JDO TCK minutes: method
addSubquery separates the outer from the subquery. So it looks like that
this approach cannot support correlated subqueries where the subquery
iterates a collection field defined in the outer query. My preference is
using variables declaration to support subqueries even in the Query API
case.
A query selecting employees earning more than the average salary could be:
SELECT FROM Employee WHERE this.salary > averageSalary
VARIABLES float averageSalary = SELECT AVG(this.salary) FROM Employee
But I still have problems with a correlated subquery. I'm looking for a
query selecting employees earning more than the average salary of their
department. The difference to the query above is that the candidates
collection of the subquery: the employees collection of the department
of the current employee and not the entire Employee extent. Here are
some ideas to stimulate the discussion, but I'm not quite happy with these.
(1) Use a collection variable in the FROM clause of the subquery:
SELECT FROM Employee WHERE this.salary > averageSalary
VARIABLES Collection emps = this.department.employees;
float averageSalary = SELECT AVG(this.salary) FROM emps
One issue is that 'this' is ambiguous: I would think that 'this' always
refers to an instance of the inner most scope. But this means the
subquery cannot directly access a field of the outer query. So the outer
query declares a variable emps that may be used in the inner query.
Maybe we need a special syntax to access the 'this' from the outer query.
(2) The second form does not define any FROM clause, instead it uses a
variable bound to a collection field of the outer query:
SELECT FROM Employee WHERE this.salary > averageSalary
VARIABLES Employee e;
float averageSalary = SELECT AVG(e.salary) WHERE
this.department.employees.contains(e)
Since the subquery does not have a FROM clause, 'this' refers to the
current Employee from the outer query.
BTW, this is the query as Java Persistence API Query (formerly EJB QL):
SELECT e FROM Employee e JOIN e.department d
WHERE e.salary > (SELECT AVG(e2.salary) FROM d.employees e2)
About the open issue: using the assignment operator ("=") versus the
JDOQL keyword "AS": I vote for the assignment operator. The "AS" keyword
is used in the result expression of the form 'expr AS name'. In a
variable declaration it would have the form 'type name AS expr'. It
might be confusing that the variable declaration swaps the order,
because here the expr is right of the keyword, where it is on the other
side in the result expression.
Regards Michael
> Hi everyone,
>
> Here are 2 proposals discussed in the Fri 9 Jun JDO conference call
> regarding support for subqueries in JDOQL, including single-string and Query
> API enhancements, inspired by JPOX's proposed enhancement, documented in
> JPOX JIRA issue CORE-2861
> (http://www.jpox.org/servlet/jira/browse/CORE-2861). The string and API
> enhancement proposals described here are designed to be used hand-in-hand,
> as the folks on the call wanted to continue to provide compatible
> string-based and API-based usages. In a nutshell, both proposals hinge on
> the use of the exising facility to declare JDOQL variables (not JDOQL
> parameters) to bind subqueries to superqueries.
>
> Please read thoroughly, consider, and comment.
>
> --matthew
>
> PS: Martin was on the hook to describe an alternative proposal based on a
> future object pattern. See separate proposal from him.
>
> <proposals>
>
> Query API support
> =================
> Proposal: Introduce new method Query.addSubquery(String variableName, Query
> subquery)
>
> This proposal entails utilizing the current Query API's declareVariables
> facility and adding a method Query.addSubquery(String,Query) to support
> subqueries. Essentially, a subquery is bound to a superquery via a variable
> declared for the superquery. The implementation handles coercing the
> subquery's result into the type of the variable(s) declared in the
> superquery. Queries can be nested to arbitrary levels.
>
> Example 1A: Find people with above average income
>
> Query superquery1a = pm.newQuery("SELECT FROM Person WHERE income >
> averageIncome");
> superquery1a.declareVariables("BigDecimal averageIncome;");
>
> Query subquery1a = pm.newQuery("SELECT avg(income) FROM Person");
> superquery1a.addSubquery("averageIncome", subquery1a); // binds subquery to
> superquery
>
>
> Example 2A: Find average income of fathers using subquery
>
> Query superquery2a = pm.newQuery("SELECT avg(income) FROM fathers");
> // in next line, Collection<Parent> derived from subquery
> superquery2a.declareVariables("Collection fathers;");
>
> Query subquery2a = pm.newQuery("SELECT FROM Person WHERE gender == 'M' &&
> children.size() > 0");
> superquery2a.addSubquery("fathers", subquery2a); // binds subquery to
> superquery
>
>
> Example 3A: Find average income of fathers using a single Query instance
> Note: this example's usage is required if the grammar specification of the
> variables clause remains the same (as it currently is) in the API and string
> forms (see JDO 2.0 spec sections 14.6.5 & 14.6.13).
>
> Query superquery3a = pm.newQuery("SELECT avg(income) FROM fathers");
> // in next line, Collection<Parent> derived from subquery
> superquery3a.declareVariables(
> "Collection fathers = SELECT FROM Person WHERE gender == 'M' &&
> children.size() > 0;");
>
> Pros:
> * Maintains backward compatibility.
> * Enhances performance by allowing for the deferral of query execution
> until entire query with subqueries is defined. Current Query API support
> requires the execution of the subquery, then execution of the superquery;
> current JDOQL string spec doesn't allow for subqueries at all.
> * Grammar of the variables clause undergoes the same enhancements in both
> the single-string and the API.
> * Compatible with single-string enhancement proposal below
>
> Cons:
> * Type coercion becomes more complicated than just autoboxing.
> * Requires that variables may always have to be explicitly defined.
> * Possibility that type of candidate collection of superquery must be
> derived (see example 2A above), or may not be known.
> * Possibility of using variables in place of both parameters and candidate
> collections.
>
>
>
> Single-string proposal
> ======================
> Proposal: Use the existing VARIABLES JDOQL keyword in order to be
> compatible with the Query API proposal above.
>
> This proposal is very similar to JPOX JIRA CORE-2861
> (http://www.jpox.org/servlet/jira/browse/CORE-2861), which proposes the
> introduction of a new JDOQL keyword "WITH" to introduce typed and named
> subquery results. To make this more compatible with the Query API proposal
> above and to avoid the need to introduce a new keyword to JDOQL, the
> existing JDOQL keyword "VARIABLES" would be used to introduce typed and
> named subqueries, except that the variable(s) would be intialized via the
> assignment operator, "=", or the "AS" keyword (TBD) at declaration time with
> a valid JDOQL expression. Variables would continue to be
> semicolon-delimited. Additionally, Query.toString(), for queries that
> employ subqueries, returns JDOQL strings that use this syntax.
>
> Example 1S: Find people with above average income using subquery (similar
> to example 1A)
>
> SELECT FROM Person WHERE income > averageIncome
> VARIABLES float averageIncome =
> SELECT avg(income) FROM Person;
>
> Example 2S: Find average income of fathers using a subquery (similar to
> example 2A)
>
> SELECT avg(income) FROM parents
> VARIABLES Collection parents =
> SELECT FROM Parent WHERE gender == 'M' && children.size() > 0;
>
> Pros:
> * Continues to use existing JDOQL keywords.
> * Grammar of the variables clause undergoes the same enhancements in both
> the single-string and the API.
> * Compatible with Query API proposal above.
>
> Cons:
> * More verbose than introducing "WITH" keyword. Implicit, unnamed
> variables would not be supported, unless the following syntax were supported
> (from example 2S), where the tokens "VARIABLES Collection parents" is
> implied.
> SELECT avg(income) FROM parents =
> SELECT FROM Parent WHERE gender == 'M' && children.size() > 0;
> This syntax is admittedly less verbose and more like SQL subqueries, but
> leaves open the typing and naming of the implicit, unnamed variables.
> * Possibility of using variables in place of both parameters and candidate
> collections.
>
>
> Open issues
> ===========
>
> * Can type derivation & coercion of JDOQL variables be performed in all
> cases?
> * These proposals use the assignment operator ("="). Should we use
> assignment via the JDOQL keyword "AS" instead or in addition to the
> assignment operator?
> * This proposal requires that JDOQL variables be allowed to substitute for
> both JDOQL parameters and candidate collections. Should this be allowed?
>
>
> </proposals>
>
>
>> -----Original Message-----
>> From: Erik Bengtson [mailto:erik@jpox.org]
>> Sent: Wednesday, June 07, 2006 7:57 AM
>> To: jdo-experts-ext@sun.com; jdo-dev@db.apache.org
>> Subject: any plans to support subqueries with similar concept as in sql
>>
>> Hi,
>>
>> We need the ability to work with multiple sets in the same
>> query. It includes
>> performing operations between sets, numeric functions like
>> average or sum,
>> etc..
>>
>> In JPOX it will implemented as exemplified here
>> http://www.jpox.org/servlet/jira/browse/CORE-2861
>>
>> Sadly, JDOQL 2 is not capable to compete with JPQL in this aspect.
>>
>> Regardless the above issue, are there plans to expand the
>> JDOQL or even JDO 2 in
>> general based on new user requests/requirements?
>>
>> Regards,
>>
>> Erik Bengtson
>>
>>
>
>
--
Michael Bouschen Tech@Spree Engineering GmbH
mailto:mbo.tech@spree.de http://www.tech.spree.de/
Tel.:++49/30/235 520-33 Buelowstr. 66
Fax.:++49/30/2175 2012 D-10783 Berlin
Re: JDOQL Subquery proposals
Posted by Craig L Russell <Cr...@Sun.COM>.
Hi Michael,
Looks good.
Craig
On Oct 23, 2007, at 2:35 AM, Michael Bouschen wrote:
> Hi,
>
> I looked at the sample queries we used when discussion the subquery
> proposal. I modified them such that they work with the company
> model used in the TCK and adapted them to the proposed API. I
> propose to add these queries to a new TCK query test class testing
> JDOQL subqueries.
>
> (1) Select employees who work more than the average of their
> department employees
>
> Single-string JDOQL:
> SELECT FROM Employee WHERE this.weeklyhours > (SELECT AVG
> (e.weeklyhours) FROM this.department.employees e)
>
> Query API:
> Query sub = pm.newQuery(Employee.class);
> sub.setResult("avg(this.weeklyhours)");
> Query q = pm.newQuery(Employee.class);
> q.setFilter("this.weeklyhours> averageWeeklyhours");
> q.addSubquery(sub, "double averageWeeklyhours",
> "this.department.employees");
>
> (2) Select employees who work more than the average of the
> employees in their department having the same manager?
>
> Single-string JDOQL:
> SELECT FROM Employee WHERE this.weeklyhours >
> (SELECT AVG(e.weeklyhours) FROM this.department.employees e
> WHERE e.manager == this.manager)
>
> Query API:
> Query sub = pm.newQuery(Employee.class);
> sub.setResult("avg(this.weeklyhours)");
> sub.setFilter("this.manager == :manager");
> Query q = pm.newQuery(Employee.class);
> q.setFilter("this.weeklyHours > averageWeeklyhours");
> q.addSubquery(sub, "double averageWeeklyhours",
> "this.department.employees", "this.manager");
>
> (3) select employees who work more than the average of all employees?
>
> Single-string JDOQL:
> SELECT FROM Employee WHERE this.weeklyhours > (SELECT AVG
> (e.weeklyhours) FROM Employee e)
>
> Query API:
> Query sub = pm.newQuery(Employee.class);
> sub.setResult("avg(this.weeklyhours)");
> Query q = pm.newQuery(Employee.class);
> q.setFilter("this.weeklyHours > averageWeeklyhours");
> q.addSubquery(sub, "double averageWeeklyhours", null);
> The null value passed as the candidateCollection expression
> indicates that we're not overriding the candidates for the subquery
> and thus it uses the entire extent of Employee.
>
> (4) Select employees hired after a particular date who work more
> than the average of all employees?
>
> Single-string JDOQL:
> SELECT FROM Employee WHERE this.hiredate > :hired &&
> this.weeklyhours> (SELECT AVG(e.weeklyhours) FROM Employee e)
>
> Query API:
> Query sub = pm.newQuery(Employee.class);
> sub.setResult("avg(this.weeklyhours)");
> Query q = pm.newQuery(Employee.class);
> q.setFilter("this.hiredate > :hired && this.weeklyhours >
> averageWeeklyhours");
> q.addSubquery(sub, "double averageWeeklyhours", null);
>
> (5) Select employees hired after a particular date who work more
> than the average of all employees of the same manager?
>
> Single-string JDOQL:
> SELECT FROM Employee WHERE this.hiredate > :hired &&
> this.weeklyhours >
> (SELECT AVG(e.weeklyhours) FROM Employee e WHERE e.manager ==
> this.manager)
>
> Query API:
> Query sub = pm.newQuery(Employee.class);
> sub.setResult("avg(this.weeklyhours)");
> sub.setFilter("this.manager == :manager");
> Query q = pm.newQuery(Employee.class);
> q.setFilter("this.hiredate > :hired && this.weeklyhours>
> averageWeeklyhours");
> q.addSubquery(sub, "double averageWeeklyhours", null,
> "this.manager");
>
> (6) Select employees who work more than the average of all
> employees of the same manager?
>
> Single-string JDOQL:
> SELECT FROM Employee WHERE this.weeklyhours >
> (SELECT AVG(e.weeklyhours) FROM Employee e WHERE e.manager ==
> this.manager)
>
> Query API:
> Query sub = pm.newQuery(Employee.class);
> sub.setResult("avg(this.weeklyhours)");
> sub.setFilter("this.manager == :manager");
> Query q = pm.newQuery(Employee.class);
> q.setFilter("this.weeklyHours > averageWeeklyhours");
> q.addSubquery(sub, "double averageWeeklyhours", null,
> "this.manager");
>
> Regards Michael
>
>> It looks like the spec update I just completed forgot to include
>> some of the APIs that we discussed here:
>>
>> addSubquery(Query subquery, String variableDeclaration, String
>> candidateCollectionExpr, String parameter);
>> addSubqueryWithArray(Query subquery, String variableDeclaration,
>> String candidateCollectionExpr, String[] parameters);
>>
>> In the api2 this becomes
>> addSubquery(Query subquery, String variableDeclaration, String
>> candidateCollectionExpr, String... parameters);
>>
>> addSubquery(Query subquery, String variableDeclaration, String
>> candidateCollectionExpr, Map parameters);
>>
>> We also need to describe what happens if the same
>> variableDeclaration is re-used. I think it makes sense that the
>> variableDeclaration is the key to a map of subqueries in the outer
>> query, and if addSubquery is used with the same
>> variableDeclaration then it replaces the previous query. And if
>> subquery is null, then that variableDeclaration is reset.
>>
>> We can explore some of these side issues when we see Michael's
>> test cases.
>>
>> Craig
>>
>> On Oct 3, 2006, at 9:49 AM, Craig L Russell wrote:
>>
>>> Hi Wes and Michael,
>>>
>>> I'm just now reviewing this. Very nice indeed.
>>>
>>> We need to consider what happens if a subquery is modified after
>>> being added to an outer query. I'd like to propose that we treat
>>> the subquery as supplying certain pieces and excluding others. We
>>> have a similar situation with serialization of a Query instance,
>>> that I think we can model for this purpose.
>>>
>>> <spec>
>>> The class implementing the Query interface must be serializable.
>>> The serialized fields in-
>>> clude the candidate class, the filter, parameter declarations,
>>> variable declarations, imports,
>>> ordering specification, uniqueness, result specification,
>>> grouping specification, and result
>>> class. The candidate collection, limits on size, and number of
>>> skipped instances are not se-
>>> rialized. If a serialized instance is restored, it loses its
>>> association with its former Persis-
>>> tenceManager.
>>> </spec>
>>>
>>> I'd suggest that we adapt this for subqueries as follows:
>>>
>>> The query parameter is unmodified as a result of the addSubquery
>>> or subsequent execution
>>> of the outer query. Only some of the query parts are copied for
>>> use as the subquery. The parts in-
>>> clude the candidate class, filter, parameter declarations,
>>> variable declarations, imports,
>>> ordering specification, uniqueness, result specification, and
>>> grouping specification.
>>> The association with a PersistenceManager, the candidate
>>> collection, result
>>> class, limits on size, and number of skipped instances are not used.
>>>
>>> The implications are:
>>>
>>> changes made to the subquery after addSubquery are not reflected
>>> in the outer query
>>>
>>> the subquery can be executed without affecting the outer query
>>>
>>> the same subquery can be used multiple times in the same or other
>>> outer queries
>>>
>>> the candidate instances of the subquery itself are not
>>> considered; either the candidates are identified in the
>>> addSubquery method or the extent of the candidate class is used
>>>
>>> serialized/restored queries can be used as parameters for
>>> addSubquery
>>>
>>> there is no limitation on the use of a query that is bound to a
>>> different PersistenceManager
>>>
>>> the range feature is not usable (this is probably a good thing
>>> for subqueries)
>>>
>>> A few more comments below.
>>>
>>> On Sep 26, 2006, at 1:30 PM, Michael Bouschen wrote:
>>>
>>>> Hi Wes,
>>>>
>>>> I agree with what you are proposing below. Some more comments
>>>> inline ...
>>>>> Hi Michael, thanks for reviewing.
>>>>>
>>>>> I agree, it is too much to overload the existing methods. I
>>>>> like your suggestion -- the only modification I would add is
>>>>> that the Query parameter come first as I think that would be
>>>>> slightly more readable/self-documenting.
>>>>>
>>>>> e.g. addSubquery(Query sub, String variableDeclaration, String
>>>>> candidateCollectionExpression)
>>>> Sounds good.
>>>>>
>>>>> Some minor points:
>>>>> addSubquery() in all its forms should throw an exception if ...
>>>>> -- sub.getPersistenceManager() != this.getPersistenceManager()
>>>
>>> no; see above
>>>
>>>>> -- sub has been closed
>>>
>>> queries are not closed; results are closed
>>>
>>>>> -- "this" has been closed (duh)
>>>
>>> queries are not closed; results are closed
>>>
>>>>> -- "this" has been made unmodifiable
>>>>> -- the derived type of the candidateCollectionExpression does
>>>>> not match the declared extent type of sub
>>>>> -- the variableDeclaration type does not match the declared
>>>>> return type of sub
>>>> I agree. Just a minor change: the element type of the
>>>> candidateCollectionExpression must be compatible with the
>>>> candidate class of the subquery.
>>>>>
>>>>> It would make things simpler if the IgnoreCache setting had to
>>>>> be the same for both as well. Or we might say the IgnoreCache
>>>>> value for the outer query overrides any subqueries.
>>>> Yes, the IgnoreCache value of the outer query should win.
>>>>>
>>>>> Also, while the candidates for the subquery will be overridden
>>>>> at the time when execute() is invoked on the outer query, it
>>>>> would be nice if that was non-mutating, though we should take
>>>>> some advice from implementers on what the following use case
>>>>> should do:
>>>>>
>>>>> Query avgSalary = pm.newQuery(Employee.class);
>>>>> avgSalary.setResult("avg(this.salary)");
>>>>> avgSalary.setCandidates(someEmployees); // for argument's sake
>>>>>
>>>>> // Invoke directly -- against someEmployees collection
>>>>> Float result1 = (Float) avgSalary.execute();
>>>>>
>>>>> Query aboveDeptAvg = pm.newQuery(Employee.class, "this.salary >
>>>>> avgSalary");
>>>>> aboveDeptAvg.addSubquery(avgSalary, "float avgSalary",
>>>>> "this.department.employees");
>>>>>
>>>>> // Invoke as subquery -- someEmployees collection is ignored
>>>>> Collection employees = (Collection) aboveDeptAvg.execute();
>>>>>
>>>>> // Now invoke the subquery directly again -- does this use
>>>>> someEmployees?
>>>
>>> Yes. When avgSalary was used as a subquery, the relevant parts of
>>> the query, not including the candidates, were used and avgSalary
>>> itself was not modified.
>>>
>>>>> Float result2 = (Float) avgSalary.execute();
>>>>>
>>>>> I would prefer that result1.equals(result2) -- this implies
>>>>> that the implementation must internally revert the candidate
>>>>> extent or collection for avgSalary after it is used as a
>>>>> subquery; for the spec it just means that using a Query as a
>>>>> subquery does not modify any of its own candidate settings.
>>>> Yes, I would prefer this, too. So let's see whether there are
>>>> any issues with this from the implementation point of view.
>>>
>>> I think it's easier for everyone to consider addSubquery to
>>> simply copy the parts of the query of interest and leave the
>>> query intact.
>>>
>>> Craig
>>>>
>>>> Thanks again for the feedback.
>>>>
>>>> Regards Michael
>>>>>
>>>>> Wes
>>>>>
>>>>>
>>>>> Michael Bouschen wrote:
>>>>>
>>>>>> Hi Wes,
>>>>>>
>>>>>> thanks for the feedback, it's definitely not too late.
>>>>>>
>>>>>> I like your proposal. It allows subqueries being supported in
>>>>>> both the SSJDOQL and the query API. I like the the idea of
>>>>>> explicitly setting subquery's candidate collection by passing
>>>>>> an expression of the outer query (and use the same mechanism
>>>>>> for the parameters of the subquery). This solves the biggest
>>>>>> problem I had with using a separate Query instance for the
>>>>>> subquery: now the subquery instance is self-contained and
>>>>>> compiles, because it does not explicitly use an expression
>>>>>> from the outer query.
>>>>>>
>>>>>> I'm just wondering whether we could find a more intuitive
>>>>>> syntax, because name(candidateExpression
>>>>>> [,parameterExpression...]) looks more like a method than a
>>>>>> variable. Furthermore, as a corner case, it might be possible
>>>>>> that a query uses more than one subquery. All the subqueries
>>>>>> would have to be defined in a single call of
>>>>>> outer.declareVariables. So how about, if we introduce a new
>>>>>> method called addSubquery to bind a single subquery to the
>>>>>> outer query. The method takes separate arguments for the
>>>>>> candidate collection expression and the parameters (if any).
>>>>>> Actually the parameter handling could be very similar to the
>>>>>> parameters of the execute call:
>>>>>> addSubquery(String variableDeclaration, Query subquery,
>>>>>> String candidateCollectionExpr);
>>>>>> addSubquery(String variableDeclaration, Query subquery,
>>>>>> String candidateCollectionExpr, String parameter);
>>>>>> addSubqueryWithArray(String variableDeclaration, Query
>>>>>> subquery, String candidateCollectionExpr, String[] parameters);
>>>>>> addSubqueryWithMap(String variableDeclaration, Query
>>>>>> subquery, String candidateCollectionExpr, Map parameters);
>>>>>>
>>>>>> Looking at the first example from below, the definition of the
>>>>>> subquery would be the same. The only line that changes is the
>>>>>> declareVariable call. It is replaced by:
>>>>>> q.addSubquery(""float averageSalary", sub,
>>>>>> "this.department.employees");
>>>>>>
>>>>>> Just for completeness we should add a method to clear the
>>>>>> subqueries, such that you can reuse the outer query and bind
>>>>>> new subqueries for another execute call:
>>>>>> clearSubqueries();
>>>>>>
>>>>>> What do you think?
>>>>>>
>>>>>> If we think the above is option I would come up with an
>>>>>> updated summary of JDOQL changes to support subqueries and
>>>>>> updated version of the sample queries.
>>>>>>
>>>>>> Regards Michael
>>>>>>
>>>>>>> I'm ridiculously late in responding to this thread but if I
>>>>>>> may be so bold, I'll make a further suggestion.
>>>>>>>
>>>>>>> I like everything about the proposed approach except the
>>>>>>> requirement that subquery definitions must resort to single-
>>>>>>> string JDOQL syntax, even when using the API-based methods.
>>>>>>> I think this introduces asymmetry and discourages reuse and
>>>>>>> modularity.
>>>>>>>
>>>>>>> I would really like to see the ability to map variables to
>>>>>>> (sub)Query objects. There are two new capabilities
>>>>>>> introduced in the SSJDOQL version, and my opinion is that the
>>>>>>> API should match these feature by feature. The two features
>>>>>>> are:
>>>>>>> (1) The ability for a subquery to use an expression defined
>>>>>>> on the outer query as its candidate set.
>>>>>>> (2) The ability for a subquery to use expressions defined on
>>>>>>> the outer query as parameters.
>>>>>>>
>>>>>>> Therefore, for parity, we need an API-based way to declare
>>>>>>> these mappings, so that subqueries can be assigned both their
>>>>>>> candidate collections and their parameters dynamically.
>>>>>>>
>>>>>>> I propose an overloaded version of declareVariables that
>>>>>>> allows mapping variable names used in the outer query to (sub)
>>>>>>> Query instances that are correlated with candidates and
>>>>>>> parameters.
>>>>>>>
>>>>>>> void declareVariables(String variableList, Query... subquery)
>>>>>>>
>>>>>>> The variable declaration syntax should be extended to allow
>>>>>>> parameterized variables of the form "name(candidateExpression
>>>>>>> [,parameterExpression...])". "name" defines a variable name
>>>>>>> in the query; "candidateExpression" defines an expression
>>>>>>> (rooted in the namespace of the outer query) for the
>>>>>>> candidate extent to be bound to the subquery, where "null"
>>>>>>> signifies that the subquery candidate set is not being
>>>>>>> limited. "parameterExpression" identifies dynamic values for
>>>>>>> parameters declared by the subquery, again rooted in the
>>>>>>> namespace of the outer query doing the binding.
>>>>>>>
>>>>>>> To touch up Michael's examples:
>>>>>>>
>>>>>>> Select employees who make more than the average of their
>>>>>>> department employees?
>>>>>>>
>>>>>>> Single-string JDOQL:
>>>>>>> SELECT FROM Employee WHERE this.salary > (SELECT AVG
>>>>>>> (e.salary) FROM this.department.employees e)
>>>>>>> Query API:
>>>>>>> Query q = pm.newQuery(Employee.class);
>>>>>>> q.setFilter("this.salary > averageSalary");
>>>>>>>
>>>>>>> // Subquery definition is generic: for a given set of
>>>>>>> Employees, return the average salary
>>>>>>> Query sub = pm.newQuery(Employee.class);
>>>>>>> sub.setResult("avg(salary)");
>>>>>>>
>>>>>>> // Bind the subquery to the master query by identifying the
>>>>>>> candidate set
>>>>>>> q.declareVariables("float averageSalary
>>>>>>> (this.department.employees)", sub);
>>>>>>>
>>>>>>>> Select employees who make more than the average of the
>>>>>>>> employees in their department at the same pay scale?
>>>>>>>
>>>>>>>
>>>>>>> Single-string JDOQL:
>>>>>>> SELECT FROM Employee WHERE this.salary >
>>>>>>> (SELECT AVG(e.salary) FROM this.department.employees e
>>>>>>> WHERE e.payScale == this.payScale)
>>>>>>>
>>>>>>> Query API:
>>>>>>> Query q = pm.newQuery(Employee.class);
>>>>>>> q.setFilter("this.salary > averageSalary");
>>>>>>>
>>>>>>> // This subquery generically defines the average salary of a
>>>>>>> set of Employees at a given PayScale
>>>>>>> Query sub = pm.newQuery(Employee.class);
>>>>>>> sub.setFilter("this.payScale == ps");
>>>>>>> sub.declareParameters("PayScale ps");
>>>>>>> sub.setResult("avg(salary)");
>>>>>>>
>>>>>>> // Bind both a candidate set and the payScale parameter.
>>>>>>> q.declareVariables("float averageSalary
>>>>>>> (this.department.employees, this.payScale)", sub);
>>>>>>>
>>>>>>>> Select employees who make more than the average of all
>>>>>>>> employees?
>>>>>>>
>>>>>>>
>>>>>>> Single-string JDOQL:
>>>>>>> SELECT FROM Employee WHERE this.salary > (SELECT AVG
>>>>>>> (e.salary) FROM Employee e)
>>>>>>> SELECT FROM Employee WHERE this.salary > (SELECT AVG
>>>>>>> (this.salary) FROM Employee)
>>>>>>>
>>>>>>> Query API:
>>>>>>> Query q = pm.newQuery(Employee.class);
>>>>>>> q.setFilter("this.salary > averageSalary");
>>>>>>> Query sub = pm.newQuery(Employee.class);
>>>>>>> sub.setResult("avg(salary)");
>>>>>>> // The null value indicates that we're not overriding the
>>>>>>> candidates for the subquery
>>>>>>> // and thus it uses the entire extent of Employee
>>>>>>> q.declareVariables("float averageSalary(null)", sub);
>>>>>>>
>>>>>>>
>>>>>>>> Select employees named Joe who make more than the average of
>>>>>>>> all employees?
>>>>>>>
>>>>>>>
>>>>>>> Single-string JDOQL:
>>>>>>> SELECT FROM Employee WHERE this.name == 'Joe' && this.salary
>>>>>>> > (SELECT AVG(e.salary) FROM Employee e)
>>>>>>>
>>>>>>> Query API:
>>>>>>> Query q = pm.newQuery(Employee.class);
>>>>>>> q.setFilter("this.name == 'Joe' && this.salary >
>>>>>>> averageSalary");
>>>>>>>
>>>>>>> // This subquery generically defines "the average of all
>>>>>>> employeees"
>>>>>>> Query sub = pm.newQuery(Employee.class);
>>>>>>> sub.setResult("avg(salary)");
>>>>>>>
>>>>>>> // Note we could have reused the query instance from the
>>>>>>> previous example.
>>>>>>> q.declareVariables("float averageSalary(null)", sub);
>>>>>>>
>>>>>>>>
>>>>>>>> Select employees named Joe who make more than the average of
>>>>>>>> all employees at the same pay scale?
>>>>>>>
>>>>>>>
>>>>>>> Single-string JDOQL:
>>>>>>> SELECT FROM Employee WHERE this.name == 'Joe' && this.salary >
>>>>>>> (SELECT AVG(e.salary) FROM Employee e WHERE e.payScale ==
>>>>>>> this.payScale)
>>>>>>>
>>>>>>> Query API:
>>>>>>> Query q = pm.newQuery(Employee.class);
>>>>>>> q.setFilter("this.name == 'Joe' && this.salary >
>>>>>>> averageSalary");
>>>>>>>
>>>>>>> // Note that this is the same subquery instance as the
>>>>>>> previous pay scale example
>>>>>>> Query sub = pm.newQuery(Employee.class);
>>>>>>> sub.setFilter("payScale == ps");
>>>>>>> sub.declareParameters("PayScale ps");
>>>>>>> sub.setResult("avg(salary)");
>>>>>>>
>>>>>>> q.declareVariables("float averageSalary(null)", sub);
>>>>>>>
>>>>>>>> Select employees who make more than the average of all
>>>>>>>> employees at the same pay scale?
>>>>>>>
>>>>>>>
>>>>>>> Single-string JDOQL:
>>>>>>> SELECT FROM Employee WHERE this.salary > (SELECT AVG
>>>>>>> (e.salary) FROM Employee e WHERE e.payScale == this.payScale)
>>>>>>>
>>>>>>> Query API:
>>>>>>> Query q = pm.newQuery(Employee.class);
>>>>>>> q.setFilter("this.salary > averageSalary");
>>>>>>>
>>>>>>> // Same again
>>>>>>> Query sub = pm.newQuery(Employee.class);
>>>>>>> sub.setFilter("ps == this.payScale");
>>>>>>> sub.declareParameters("PayScale ps");
>>>>>>>
>>>>>>> q.declareVariables("float averageSalary(null,
>>>>>>> this.payScale)", sub);
>>>>>>>
>>>>>>> I'd like to hear other ideas for the exact syntax, but what
>>>>>>> do you think of the general concept?
>>>>>>>
>>>>>>> Wes
>>>>>>>
>>>>>>>
>>>>> [chop]
>>>>
>>>>
>>>> --
>>>> Michael Bouschen Tech@Spree Engineering GmbH
>>>> mailto:mbo.tech@spree.de http://www.tech.spree.de/
>>>> Tel.:++49/30/235 520-33 Buelowstr. 66 Fax.:+
>>>> +49/30/2175 2012 D-10783 Berlin
>>>
>>> Craig Russell
>>> Architect, Sun Java Enterprise System http://java.sun.com/
>>> products/jdo
>>> 408 276-5638 mailto:Craig.Russell@sun.com
>>> P.S. A good JDO? O, Gasp!
>>>
>>
>> Craig Russell
>> Architect, Sun Java Enterprise System http://java.sun.com/products/
>> jdo
>> 408 276-5638 mailto:Craig.Russell@sun.com
>> P.S. A good JDO? O, Gasp!
>>
>
>
> --
> Tech@Spree Engineering GmbH Tel.: +49/(0)30/235 520-33
> Buelowstr. 66 Fax.: +49/(0)30/217 520-12
> 10783 Berlin mailto:mbo.tech@spree.de
> Geschaeftsfuehrung: Anna-Kristin Proefrock
> Sitz Berlin, Amtsgericht Charlottenburg, HRB 564 52
>
Craig Russell
Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
408 276-5638 mailto:Craig.Russell@sun.com
P.S. A good JDO? O, Gasp!
Re: JDOQL Subquery proposals
Posted by Michael Bouschen <mb...@spree.de>.
Hi,
I looked at the sample queries we used when discussion the subquery
proposal. I modified them such that they work with the company model
used in the TCK and adapted them to the proposed API. I propose to add
these queries to a new TCK query test class testing JDOQL subqueries.
(1) Select employees who work more than the average of their department
employees
Single-string JDOQL:
SELECT FROM Employee WHERE this.weeklyhours > (SELECT
AVG(e.weeklyhours) FROM this.department.employees e)
Query API:
Query sub = pm.newQuery(Employee.class);
sub.setResult("avg(this.weeklyhours)");
Query q = pm.newQuery(Employee.class);
q.setFilter("this.weeklyhours> averageWeeklyhours");
q.addSubquery(sub, "double averageWeeklyhours",
"this.department.employees");
(2) Select employees who work more than the average of the employees in
their department having the same manager?
Single-string JDOQL:
SELECT FROM Employee WHERE this.weeklyhours >
(SELECT AVG(e.weeklyhours) FROM this.department.employees e WHERE
e.manager == this.manager)
Query API:
Query sub = pm.newQuery(Employee.class);
sub.setResult("avg(this.weeklyhours)");
sub.setFilter("this.manager == :manager");
Query q = pm.newQuery(Employee.class);
q.setFilter("this.weeklyHours > averageWeeklyhours");
q.addSubquery(sub, "double averageWeeklyhours",
"this.department.employees", "this.manager");
(3) select employees who work more than the average of all employees?
Single-string JDOQL:
SELECT FROM Employee WHERE this.weeklyhours > (SELECT
AVG(e.weeklyhours) FROM Employee e)
Query API:
Query sub = pm.newQuery(Employee.class);
sub.setResult("avg(this.weeklyhours)");
Query q = pm.newQuery(Employee.class);
q.setFilter("this.weeklyHours > averageWeeklyhours");
q.addSubquery(sub, "double averageWeeklyhours", null);
The null value passed as the candidateCollection expression indicates
that we're not overriding the candidates for the subquery and thus it
uses the entire extent of Employee.
(4) Select employees hired after a particular date who work more than
the average of all employees?
Single-string JDOQL:
SELECT FROM Employee WHERE this.hiredate > :hired && this.weeklyhours>
(SELECT AVG(e.weeklyhours) FROM Employee e)
Query API:
Query sub = pm.newQuery(Employee.class);
sub.setResult("avg(this.weeklyhours)");
Query q = pm.newQuery(Employee.class);
q.setFilter("this.hiredate > :hired && this.weeklyhours >
averageWeeklyhours");
q.addSubquery(sub, "double averageWeeklyhours", null);
(5) Select employees hired after a particular date who work more than
the average of all employees of the same manager?
Single-string JDOQL:
SELECT FROM Employee WHERE this.hiredate > :hired && this.weeklyhours >
(SELECT AVG(e.weeklyhours) FROM Employee e WHERE e.manager ==
this.manager)
Query API:
Query sub = pm.newQuery(Employee.class);
sub.setResult("avg(this.weeklyhours)");
sub.setFilter("this.manager == :manager");
Query q = pm.newQuery(Employee.class);
q.setFilter("this.hiredate > :hired && this.weeklyhours>
averageWeeklyhours");
q.addSubquery(sub, "double averageWeeklyhours", null, "this.manager");
(6) Select employees who work more than the average of all employees of
the same manager?
Single-string JDOQL:
SELECT FROM Employee WHERE this.weeklyhours >
(SELECT AVG(e.weeklyhours) FROM Employee e WHERE e.manager ==
this.manager)
Query API:
Query sub = pm.newQuery(Employee.class);
sub.setResult("avg(this.weeklyhours)");
sub.setFilter("this.manager == :manager");
Query q = pm.newQuery(Employee.class);
q.setFilter("this.weeklyHours > averageWeeklyhours");
q.addSubquery(sub, "double averageWeeklyhours", null, "this.manager");
Regards Michael
> It looks like the spec update I just completed forgot to include some
> of the APIs that we discussed here:
>
> addSubquery(Query subquery, String variableDeclaration, String
> candidateCollectionExpr, String parameter);
> addSubqueryWithArray(Query subquery, String variableDeclaration,
> String candidateCollectionExpr, String[] parameters);
>
> In the api2 this becomes
> addSubquery(Query subquery, String variableDeclaration, String
> candidateCollectionExpr, String... parameters);
>
> addSubquery(Query subquery, String variableDeclaration, String
> candidateCollectionExpr, Map parameters);
>
> We also need to describe what happens if the same variableDeclaration
> is re-used. I think it makes sense that the variableDeclaration is the
> key to a map of subqueries in the outer query, and if addSubquery is
> used with the same variableDeclaration then it replaces the previous
> query. And if subquery is null, then that variableDeclaration is reset.
>
> We can explore some of these side issues when we see Michael's test
> cases.
>
> Craig
>
> On Oct 3, 2006, at 9:49 AM, Craig L Russell wrote:
>
>> Hi Wes and Michael,
>>
>> I'm just now reviewing this. Very nice indeed.
>>
>> We need to consider what happens if a subquery is modified after
>> being added to an outer query. I'd like to propose that we treat the
>> subquery as supplying certain pieces and excluding others. We have a
>> similar situation with serialization of a Query instance, that I
>> think we can model for this purpose.
>>
>> <spec>
>> The class implementing the Query interface must be serializable. The
>> serialized fields in-
>> clude the candidate class, the filter, parameter declarations,
>> variable declarations, imports,
>> ordering specification, uniqueness, result specification, grouping
>> specification, and result
>> class. The candidate collection, limits on size, and number of
>> skipped instances are not se-
>> rialized. If a serialized instance is restored, it loses its
>> association with its former Persis-
>> tenceManager.
>> </spec>
>>
>> I'd suggest that we adapt this for subqueries as follows:
>>
>> The query parameter is unmodified as a result of the addSubquery or
>> subsequent execution
>> of the outer query. Only some of the query parts are copied for use
>> as the subquery. The parts in-
>> clude the candidate class, filter, parameter declarations, variable
>> declarations, imports,
>> ordering specification, uniqueness, result specification, and
>> grouping specification.
>> The association with a PersistenceManager, the candidate collection,
>> result
>> class, limits on size, and number of skipped instances are not used.
>>
>> The implications are:
>>
>> changes made to the subquery after addSubquery are not reflected in
>> the outer query
>>
>> the subquery can be executed without affecting the outer query
>>
>> the same subquery can be used multiple times in the same or other
>> outer queries
>>
>> the candidate instances of the subquery itself are not considered;
>> either the candidates are identified in the addSubquery method or the
>> extent of the candidate class is used
>>
>> serialized/restored queries can be used as parameters for addSubquery
>>
>> there is no limitation on the use of a query that is bound to a
>> different PersistenceManager
>>
>> the range feature is not usable (this is probably a good thing for
>> subqueries)
>>
>> A few more comments below.
>>
>> On Sep 26, 2006, at 1:30 PM, Michael Bouschen wrote:
>>
>>> Hi Wes,
>>>
>>> I agree with what you are proposing below. Some more comments inline
>>> ...
>>>> Hi Michael, thanks for reviewing.
>>>>
>>>> I agree, it is too much to overload the existing methods. I like
>>>> your suggestion -- the only modification I would add is that the
>>>> Query parameter come first as I think that would be slightly more
>>>> readable/self-documenting.
>>>>
>>>> e.g. addSubquery(Query sub, String variableDeclaration, String
>>>> candidateCollectionExpression)
>>> Sounds good.
>>>>
>>>> Some minor points:
>>>> addSubquery() in all its forms should throw an exception if ...
>>>> -- sub.getPersistenceManager() != this.getPersistenceManager()
>>
>> no; see above
>>
>>>> -- sub has been closed
>>
>> queries are not closed; results are closed
>>
>>>> -- "this" has been closed (duh)
>>
>> queries are not closed; results are closed
>>
>>>> -- "this" has been made unmodifiable
>>>> -- the derived type of the candidateCollectionExpression does not
>>>> match the declared extent type of sub
>>>> -- the variableDeclaration type does not match the declared return
>>>> type of sub
>>> I agree. Just a minor change: the element type of the
>>> candidateCollectionExpression must be compatible with the candidate
>>> class of the subquery.
>>>>
>>>> It would make things simpler if the IgnoreCache setting had to be
>>>> the same for both as well. Or we might say the IgnoreCache value
>>>> for the outer query overrides any subqueries.
>>> Yes, the IgnoreCache value of the outer query should win.
>>>>
>>>> Also, while the candidates for the subquery will be overridden at
>>>> the time when execute() is invoked on the outer query, it would be
>>>> nice if that was non-mutating, though we should take some advice
>>>> from implementers on what the following use case should do:
>>>>
>>>> Query avgSalary = pm.newQuery(Employee.class);
>>>> avgSalary.setResult("avg(this.salary)");
>>>> avgSalary.setCandidates(someEmployees); // for argument's sake
>>>>
>>>> // Invoke directly -- against someEmployees collection
>>>> Float result1 = (Float) avgSalary.execute();
>>>>
>>>> Query aboveDeptAvg = pm.newQuery(Employee.class, "this.salary >
>>>> avgSalary");
>>>> aboveDeptAvg.addSubquery(avgSalary, "float avgSalary",
>>>> "this.department.employees");
>>>>
>>>> // Invoke as subquery -- someEmployees collection is ignored
>>>> Collection employees = (Collection) aboveDeptAvg.execute();
>>>>
>>>> // Now invoke the subquery directly again -- does this use
>>>> someEmployees?
>>
>> Yes. When avgSalary was used as a subquery, the relevant parts of the
>> query, not including the candidates, were used and avgSalary itself
>> was not modified.
>>
>>>> Float result2 = (Float) avgSalary.execute();
>>>>
>>>> I would prefer that result1.equals(result2) -- this implies that
>>>> the implementation must internally revert the candidate extent or
>>>> collection for avgSalary after it is used as a subquery; for the
>>>> spec it just means that using a Query as a subquery does not modify
>>>> any of its own candidate settings.
>>> Yes, I would prefer this, too. So let's see whether there are any
>>> issues with this from the implementation point of view.
>>
>> I think it's easier for everyone to consider addSubquery to simply
>> copy the parts of the query of interest and leave the query intact.
>>
>> Craig
>>>
>>> Thanks again for the feedback.
>>>
>>> Regards Michael
>>>>
>>>> Wes
>>>>
>>>>
>>>> Michael Bouschen wrote:
>>>>
>>>>> Hi Wes,
>>>>>
>>>>> thanks for the feedback, it's definitely not too late.
>>>>>
>>>>> I like your proposal. It allows subqueries being supported in both
>>>>> the SSJDOQL and the query API. I like the the idea of explicitly
>>>>> setting subquery's candidate collection by passing an expression
>>>>> of the outer query (and use the same mechanism for the parameters
>>>>> of the subquery). This solves the biggest problem I had with using
>>>>> a separate Query instance for the subquery: now the subquery
>>>>> instance is self-contained and compiles, because it does not
>>>>> explicitly use an expression from the outer query.
>>>>>
>>>>> I'm just wondering whether we could find a more intuitive syntax,
>>>>> because name(candidateExpression[,parameterExpression...]) looks
>>>>> more like a method than a variable. Furthermore, as a corner case,
>>>>> it might be possible that a query uses more than one subquery. All
>>>>> the subqueries would have to be defined in a single call of
>>>>> outer.declareVariables. So how about, if we introduce a new method
>>>>> called addSubquery to bind a single subquery to the outer query.
>>>>> The method takes separate arguments for the candidate collection
>>>>> expression and the parameters (if any). Actually the parameter
>>>>> handling could be very similar to the parameters of the execute call:
>>>>> addSubquery(String variableDeclaration, Query subquery, String
>>>>> candidateCollectionExpr);
>>>>> addSubquery(String variableDeclaration, Query subquery, String
>>>>> candidateCollectionExpr, String parameter);
>>>>> addSubqueryWithArray(String variableDeclaration, Query subquery,
>>>>> String candidateCollectionExpr, String[] parameters);
>>>>> addSubqueryWithMap(String variableDeclaration, Query subquery,
>>>>> String candidateCollectionExpr, Map parameters);
>>>>>
>>>>> Looking at the first example from below, the definition of the
>>>>> subquery would be the same. The only line that changes is the
>>>>> declareVariable call. It is replaced by:
>>>>> q.addSubquery(""float averageSalary", sub,
>>>>> "this.department.employees");
>>>>>
>>>>> Just for completeness we should add a method to clear the
>>>>> subqueries, such that you can reuse the outer query and bind new
>>>>> subqueries for another execute call:
>>>>> clearSubqueries();
>>>>>
>>>>> What do you think?
>>>>>
>>>>> If we think the above is option I would come up with an updated
>>>>> summary of JDOQL changes to support subqueries and updated version
>>>>> of the sample queries.
>>>>>
>>>>> Regards Michael
>>>>>
>>>>>> I'm ridiculously late in responding to this thread but if I may
>>>>>> be so bold, I'll make a further suggestion.
>>>>>>
>>>>>> I like everything about the proposed approach except the
>>>>>> requirement that subquery definitions must resort to
>>>>>> single-string JDOQL syntax, even when using the API-based
>>>>>> methods. I think this introduces asymmetry and discourages reuse
>>>>>> and modularity.
>>>>>>
>>>>>> I would really like to see the ability to map variables to
>>>>>> (sub)Query objects. There are two new capabilities introduced in
>>>>>> the SSJDOQL version, and my opinion is that the API should match
>>>>>> these feature by feature. The two features are:
>>>>>> (1) The ability for a subquery to use an expression defined on
>>>>>> the outer query as its candidate set.
>>>>>> (2) The ability for a subquery to use expressions defined on the
>>>>>> outer query as parameters.
>>>>>>
>>>>>> Therefore, for parity, we need an API-based way to declare these
>>>>>> mappings, so that subqueries can be assigned both their candidate
>>>>>> collections and their parameters dynamically.
>>>>>>
>>>>>> I propose an overloaded version of declareVariables that allows
>>>>>> mapping variable names used in the outer query to (sub)Query
>>>>>> instances that are correlated with candidates and parameters.
>>>>>>
>>>>>> void declareVariables(String variableList, Query... subquery)
>>>>>>
>>>>>> The variable declaration syntax should be extended to allow
>>>>>> parameterized variables of the form
>>>>>> "name(candidateExpression[,parameterExpression...])". "name"
>>>>>> defines a variable name in the query; "candidateExpression"
>>>>>> defines an expression (rooted in the namespace of the outer
>>>>>> query) for the candidate extent to be bound to the subquery,
>>>>>> where "null" signifies that the subquery candidate set is not
>>>>>> being limited. "parameterExpression" identifies dynamic values
>>>>>> for parameters declared by the subquery, again rooted in the
>>>>>> namespace of the outer query doing the binding.
>>>>>>
>>>>>> To touch up Michael's examples:
>>>>>>
>>>>>> Select employees who make more than the average of their
>>>>>> department employees?
>>>>>>
>>>>>> Single-string JDOQL:
>>>>>> SELECT FROM Employee WHERE this.salary > (SELECT AVG(e.salary)
>>>>>> FROM this.department.employees e)
>>>>>> Query API:
>>>>>> Query q = pm.newQuery(Employee.class);
>>>>>> q.setFilter("this.salary > averageSalary");
>>>>>>
>>>>>> // Subquery definition is generic: for a given set of Employees,
>>>>>> return the average salary
>>>>>> Query sub = pm.newQuery(Employee.class);
>>>>>> sub.setResult("avg(salary)");
>>>>>>
>>>>>> // Bind the subquery to the master query by identifying the
>>>>>> candidate set
>>>>>> q.declareVariables("float
>>>>>> averageSalary(this.department.employees)", sub);
>>>>>>
>>>>>>> Select employees who make more than the average of the employees
>>>>>>> in their department at the same pay scale?
>>>>>>
>>>>>>
>>>>>> Single-string JDOQL:
>>>>>> SELECT FROM Employee WHERE this.salary >
>>>>>> (SELECT AVG(e.salary) FROM this.department.employees e WHERE
>>>>>> e.payScale == this.payScale)
>>>>>>
>>>>>> Query API:
>>>>>> Query q = pm.newQuery(Employee.class);
>>>>>> q.setFilter("this.salary > averageSalary");
>>>>>>
>>>>>> // This subquery generically defines the average salary of a set
>>>>>> of Employees at a given PayScale
>>>>>> Query sub = pm.newQuery(Employee.class);
>>>>>> sub.setFilter("this.payScale == ps");
>>>>>> sub.declareParameters("PayScale ps");
>>>>>> sub.setResult("avg(salary)");
>>>>>>
>>>>>> // Bind both a candidate set and the payScale parameter.
>>>>>> q.declareVariables("float
>>>>>> averageSalary(this.department.employees, this.payScale)", sub);
>>>>>>
>>>>>>> Select employees who make more than the average of all employees?
>>>>>>
>>>>>>
>>>>>> Single-string JDOQL:
>>>>>> SELECT FROM Employee WHERE this.salary > (SELECT AVG(e.salary)
>>>>>> FROM Employee e)
>>>>>> SELECT FROM Employee WHERE this.salary > (SELECT
>>>>>> AVG(this.salary) FROM Employee)
>>>>>>
>>>>>> Query API:
>>>>>> Query q = pm.newQuery(Employee.class);
>>>>>> q.setFilter("this.salary > averageSalary");
>>>>>> Query sub = pm.newQuery(Employee.class);
>>>>>> sub.setResult("avg(salary)");
>>>>>> // The null value indicates that we're not overriding the
>>>>>> candidates for the subquery
>>>>>> // and thus it uses the entire extent of Employee
>>>>>> q.declareVariables("float averageSalary(null)", sub);
>>>>>>
>>>>>>
>>>>>>> Select employees named Joe who make more than the average of all
>>>>>>> employees?
>>>>>>
>>>>>>
>>>>>> Single-string JDOQL:
>>>>>> SELECT FROM Employee WHERE this.name == 'Joe' && this.salary >
>>>>>> (SELECT AVG(e.salary) FROM Employee e)
>>>>>>
>>>>>> Query API:
>>>>>> Query q = pm.newQuery(Employee.class);
>>>>>> q.setFilter("this.name == 'Joe' && this.salary > averageSalary");
>>>>>>
>>>>>> // This subquery generically defines "the average of all employeees"
>>>>>> Query sub = pm.newQuery(Employee.class);
>>>>>> sub.setResult("avg(salary)");
>>>>>>
>>>>>> // Note we could have reused the query instance from the previous
>>>>>> example.
>>>>>> q.declareVariables("float averageSalary(null)", sub);
>>>>>>
>>>>>>>
>>>>>>> Select employees named Joe who make more than the average of all
>>>>>>> employees at the same pay scale?
>>>>>>
>>>>>>
>>>>>> Single-string JDOQL:
>>>>>> SELECT FROM Employee WHERE this.name == 'Joe' && this.salary >
>>>>>> (SELECT AVG(e.salary) FROM Employee e WHERE e.payScale ==
>>>>>> this.payScale)
>>>>>>
>>>>>> Query API:
>>>>>> Query q = pm.newQuery(Employee.class);
>>>>>> q.setFilter("this.name == 'Joe' && this.salary > averageSalary");
>>>>>>
>>>>>> // Note that this is the same subquery instance as the previous
>>>>>> pay scale example
>>>>>> Query sub = pm.newQuery(Employee.class);
>>>>>> sub.setFilter("payScale == ps");
>>>>>> sub.declareParameters("PayScale ps");
>>>>>> sub.setResult("avg(salary)");
>>>>>>
>>>>>> q.declareVariables("float averageSalary(null)", sub);
>>>>>>
>>>>>>> Select employees who make more than the average of all employees
>>>>>>> at the same pay scale?
>>>>>>
>>>>>>
>>>>>> Single-string JDOQL:
>>>>>> SELECT FROM Employee WHERE this.salary > (SELECT AVG(e.salary)
>>>>>> FROM Employee e WHERE e.payScale == this.payScale)
>>>>>>
>>>>>> Query API:
>>>>>> Query q = pm.newQuery(Employee.class);
>>>>>> q.setFilter("this.salary > averageSalary");
>>>>>>
>>>>>> // Same again
>>>>>> Query sub = pm.newQuery(Employee.class);
>>>>>> sub.setFilter("ps == this.payScale");
>>>>>> sub.declareParameters("PayScale ps");
>>>>>>
>>>>>> q.declareVariables("float averageSalary(null, this.payScale)", sub);
>>>>>>
>>>>>> I'd like to hear other ideas for the exact syntax, but what do
>>>>>> you think of the general concept?
>>>>>>
>>>>>> Wes
>>>>>>
>>>>>>
>>>> [chop]
>>>
>>>
>>> --
>>> Michael Bouschen Tech@Spree Engineering GmbH
>>> mailto:mbo.tech@spree.de http://www.tech.spree.de/
>>> Tel.:++49/30/235 520-33 Buelowstr. 66
>>> Fax.:++49/30/2175 2012 D-10783 Berlin
>>>
>>
>> Craig Russell
>> Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
>> 408 276-5638 mailto:Craig.Russell@sun.com
>> P.S. A good JDO? O, Gasp!
>>
>
> Craig Russell
> Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
> 408 276-5638 mailto:Craig.Russell@sun.com
> P.S. A good JDO? O, Gasp!
>
--
Tech@Spree Engineering GmbH Tel.: +49/(0)30/235 520-33
Buelowstr. 66 Fax.: +49/(0)30/217 520-12
10783 Berlin mailto:mbo.tech@spree.de
Geschaeftsfuehrung: Anna-Kristin Proefrock
Sitz Berlin, Amtsgericht Charlottenburg, HRB 564 52
Re: JDOQL Subquery proposals
Posted by Craig L Russell <Cr...@Sun.COM>.
It looks like the spec update I just completed forgot to include some
of the APIs that we discussed here:
addSubquery(Query subquery, String variableDeclaration, String
candidateCollectionExpr, String parameter);
addSubqueryWithArray(Query subquery, String variableDeclaration,
String candidateCollectionExpr, String[] parameters);
In the api2 this becomes
addSubquery(Query subquery, String variableDeclaration, String
candidateCollectionExpr, String... parameters);
addSubquery(Query subquery, String variableDeclaration, String
candidateCollectionExpr, Map parameters);
We also need to describe what happens if the same variableDeclaration
is re-used. I think it makes sense that the variableDeclaration is
the key to a map of subqueries in the outer query, and if addSubquery
is used with the same variableDeclaration then it replaces the
previous query. And if subquery is null, then that
variableDeclaration is reset.
We can explore some of these side issues when we see Michael's test
cases.
Craig
On Oct 3, 2006, at 9:49 AM, Craig L Russell wrote:
> Hi Wes and Michael,
>
> I'm just now reviewing this. Very nice indeed.
>
> We need to consider what happens if a subquery is modified after
> being added to an outer query. I'd like to propose that we treat
> the subquery as supplying certain pieces and excluding others. We
> have a similar situation with serialization of a Query instance,
> that I think we can model for this purpose.
>
> <spec>
> The class implementing the Query interface must be serializable.
> The serialized fields in-
> clude the candidate class, the filter, parameter declarations,
> variable declarations, imports,
> ordering specification, uniqueness, result specification, grouping
> specification, and result
> class. The candidate collection, limits on size, and number of
> skipped instances are not se-
> rialized. If a serialized instance is restored, it loses its
> association with its former Persis-
> tenceManager.
> </spec>
>
> I'd suggest that we adapt this for subqueries as follows:
>
> The query parameter is unmodified as a result of the addSubquery or
> subsequent execution
> of the outer query. Only some of the query parts are copied for use
> as the subquery. The parts in-
> clude the candidate class, filter, parameter declarations, variable
> declarations, imports,
> ordering specification, uniqueness, result specification, and
> grouping specification.
> The association with a PersistenceManager, the candidate
> collection, result
> class, limits on size, and number of skipped instances are not used.
>
> The implications are:
>
> changes made to the subquery after addSubquery are not reflected in
> the outer query
>
> the subquery can be executed without affecting the outer query
>
> the same subquery can be used multiple times in the same or other
> outer queries
>
> the candidate instances of the subquery itself are not considered;
> either the candidates are identified in the addSubquery method or
> the extent of the candidate class is used
>
> serialized/restored queries can be used as parameters for addSubquery
>
> there is no limitation on the use of a query that is bound to a
> different PersistenceManager
>
> the range feature is not usable (this is probably a good thing for
> subqueries)
>
> A few more comments below.
>
> On Sep 26, 2006, at 1:30 PM, Michael Bouschen wrote:
>
>> Hi Wes,
>>
>> I agree with what you are proposing below. Some more comments
>> inline ...
>>> Hi Michael, thanks for reviewing.
>>>
>>> I agree, it is too much to overload the existing methods. I like
>>> your suggestion -- the only modification I would add is that the
>>> Query parameter come first as I think that would be slightly more
>>> readable/self-documenting.
>>>
>>> e.g. addSubquery(Query sub, String variableDeclaration, String
>>> candidateCollectionExpression)
>> Sounds good.
>>>
>>> Some minor points:
>>> addSubquery() in all its forms should throw an exception if ...
>>> -- sub.getPersistenceManager() != this.getPersistenceManager()
>
> no; see above
>
>>> -- sub has been closed
>
> queries are not closed; results are closed
>
>>> -- "this" has been closed (duh)
>
> queries are not closed; results are closed
>
>>> -- "this" has been made unmodifiable
>>> -- the derived type of the candidateCollectionExpression does not
>>> match the declared extent type of sub
>>> -- the variableDeclaration type does not match the declared
>>> return type of sub
>> I agree. Just a minor change: the element type of the
>> candidateCollectionExpression must be compatible with the
>> candidate class of the subquery.
>>>
>>> It would make things simpler if the IgnoreCache setting had to be
>>> the same for both as well. Or we might say the IgnoreCache value
>>> for the outer query overrides any subqueries.
>> Yes, the IgnoreCache value of the outer query should win.
>>>
>>> Also, while the candidates for the subquery will be overridden at
>>> the time when execute() is invoked on the outer query, it would
>>> be nice if that was non-mutating, though we should take some
>>> advice from implementers on what the following use case should do:
>>>
>>> Query avgSalary = pm.newQuery(Employee.class);
>>> avgSalary.setResult("avg(this.salary)");
>>> avgSalary.setCandidates(someEmployees); // for argument's sake
>>>
>>> // Invoke directly -- against someEmployees collection
>>> Float result1 = (Float) avgSalary.execute();
>>>
>>> Query aboveDeptAvg = pm.newQuery(Employee.class, "this.salary >
>>> avgSalary");
>>> aboveDeptAvg.addSubquery(avgSalary, "float avgSalary",
>>> "this.department.employees");
>>>
>>> // Invoke as subquery -- someEmployees collection is ignored
>>> Collection employees = (Collection) aboveDeptAvg.execute();
>>>
>>> // Now invoke the subquery directly again -- does this use
>>> someEmployees?
>
> Yes. When avgSalary was used as a subquery, the relevant parts of
> the query, not including the candidates, were used and avgSalary
> itself was not modified.
>
>>> Float result2 = (Float) avgSalary.execute();
>>>
>>> I would prefer that result1.equals(result2) -- this implies that
>>> the implementation must internally revert the candidate extent or
>>> collection for avgSalary after it is used as a subquery; for the
>>> spec it just means that using a Query as a subquery does not
>>> modify any of its own candidate settings.
>> Yes, I would prefer this, too. So let's see whether there are any
>> issues with this from the implementation point of view.
>
> I think it's easier for everyone to consider addSubquery to simply
> copy the parts of the query of interest and leave the query intact.
>
> Craig
>>
>> Thanks again for the feedback.
>>
>> Regards Michael
>>>
>>> Wes
>>>
>>>
>>> Michael Bouschen wrote:
>>>
>>>> Hi Wes,
>>>>
>>>> thanks for the feedback, it's definitely not too late.
>>>>
>>>> I like your proposal. It allows subqueries being supported in
>>>> both the SSJDOQL and the query API. I like the the idea of
>>>> explicitly setting subquery's candidate collection by passing an
>>>> expression of the outer query (and use the same mechanism for
>>>> the parameters of the subquery). This solves the biggest problem
>>>> I had with using a separate Query instance for the subquery: now
>>>> the subquery instance is self-contained and compiles, because it
>>>> does not explicitly use an expression from the outer query.
>>>>
>>>> I'm just wondering whether we could find a more intuitive
>>>> syntax, because name(candidateExpression
>>>> [,parameterExpression...]) looks more like a method than a
>>>> variable. Furthermore, as a corner case, it might be possible
>>>> that a query uses more than one subquery. All the subqueries
>>>> would have to be defined in a single call of
>>>> outer.declareVariables. So how about, if we introduce a new
>>>> method called addSubquery to bind a single subquery to the outer
>>>> query. The method takes separate arguments for the candidate
>>>> collection expression and the parameters (if any). Actually the
>>>> parameter handling could be very similar to the parameters of
>>>> the execute call:
>>>> addSubquery(String variableDeclaration, Query subquery, String
>>>> candidateCollectionExpr);
>>>> addSubquery(String variableDeclaration, Query subquery, String
>>>> candidateCollectionExpr, String parameter);
>>>> addSubqueryWithArray(String variableDeclaration, Query
>>>> subquery, String candidateCollectionExpr, String[] parameters);
>>>> addSubqueryWithMap(String variableDeclaration, Query subquery,
>>>> String candidateCollectionExpr, Map parameters);
>>>>
>>>> Looking at the first example from below, the definition of the
>>>> subquery would be the same. The only line that changes is the
>>>> declareVariable call. It is replaced by:
>>>> q.addSubquery(""float averageSalary", sub,
>>>> "this.department.employees");
>>>>
>>>> Just for completeness we should add a method to clear the
>>>> subqueries, such that you can reuse the outer query and bind new
>>>> subqueries for another execute call:
>>>> clearSubqueries();
>>>>
>>>> What do you think?
>>>>
>>>> If we think the above is option I would come up with an updated
>>>> summary of JDOQL changes to support subqueries and updated
>>>> version of the sample queries.
>>>>
>>>> Regards Michael
>>>>
>>>>> I'm ridiculously late in responding to this thread but if I may
>>>>> be so bold, I'll make a further suggestion.
>>>>>
>>>>> I like everything about the proposed approach except the
>>>>> requirement that subquery definitions must resort to single-
>>>>> string JDOQL syntax, even when using the API-based methods. I
>>>>> think this introduces asymmetry and discourages reuse and
>>>>> modularity.
>>>>>
>>>>> I would really like to see the ability to map variables to (sub)
>>>>> Query objects. There are two new capabilities introduced in
>>>>> the SSJDOQL version, and my opinion is that the API should
>>>>> match these feature by feature. The two features are:
>>>>> (1) The ability for a subquery to use an expression defined on
>>>>> the outer query as its candidate set.
>>>>> (2) The ability for a subquery to use expressions defined on
>>>>> the outer query as parameters.
>>>>>
>>>>> Therefore, for parity, we need an API-based way to declare
>>>>> these mappings, so that subqueries can be assigned both their
>>>>> candidate collections and their parameters dynamically.
>>>>>
>>>>> I propose an overloaded version of declareVariables that allows
>>>>> mapping variable names used in the outer query to (sub)Query
>>>>> instances that are correlated with candidates and parameters.
>>>>>
>>>>> void declareVariables(String variableList, Query... subquery)
>>>>>
>>>>> The variable declaration syntax should be extended to allow
>>>>> parameterized variables of the form "name(candidateExpression
>>>>> [,parameterExpression...])". "name" defines a variable name in
>>>>> the query; "candidateExpression" defines an expression (rooted
>>>>> in the namespace of the outer query) for the candidate extent
>>>>> to be bound to the subquery, where "null" signifies that the
>>>>> subquery candidate set is not being limited.
>>>>> "parameterExpression" identifies dynamic values for parameters
>>>>> declared by the subquery, again rooted in the namespace of the
>>>>> outer query doing the binding.
>>>>>
>>>>> To touch up Michael's examples:
>>>>>
>>>>> Select employees who make more than the average of their
>>>>> department employees?
>>>>>
>>>>> Single-string JDOQL:
>>>>> SELECT FROM Employee WHERE this.salary > (SELECT AVG(e.salary)
>>>>> FROM this.department.employees e)
>>>>> Query API:
>>>>> Query q = pm.newQuery(Employee.class);
>>>>> q.setFilter("this.salary > averageSalary");
>>>>>
>>>>> // Subquery definition is generic: for a given set of
>>>>> Employees, return the average salary
>>>>> Query sub = pm.newQuery(Employee.class);
>>>>> sub.setResult("avg(salary)");
>>>>>
>>>>> // Bind the subquery to the master query by identifying the
>>>>> candidate set
>>>>> q.declareVariables("float averageSalary
>>>>> (this.department.employees)", sub);
>>>>>
>>>>>> Select employees who make more than the average of the
>>>>>> employees in their department at the same pay scale?
>>>>>
>>>>>
>>>>> Single-string JDOQL:
>>>>> SELECT FROM Employee WHERE this.salary >
>>>>> (SELECT AVG(e.salary) FROM this.department.employees e WHERE
>>>>> e.payScale == this.payScale)
>>>>>
>>>>> Query API:
>>>>> Query q = pm.newQuery(Employee.class);
>>>>> q.setFilter("this.salary > averageSalary");
>>>>>
>>>>> // This subquery generically defines the average salary of a
>>>>> set of Employees at a given PayScale
>>>>> Query sub = pm.newQuery(Employee.class);
>>>>> sub.setFilter("this.payScale == ps");
>>>>> sub.declareParameters("PayScale ps");
>>>>> sub.setResult("avg(salary)");
>>>>>
>>>>> // Bind both a candidate set and the payScale parameter.
>>>>> q.declareVariables("float averageSalary
>>>>> (this.department.employees, this.payScale)", sub);
>>>>>
>>>>>> Select employees who make more than the average of all employees?
>>>>>
>>>>>
>>>>> Single-string JDOQL:
>>>>> SELECT FROM Employee WHERE this.salary > (SELECT AVG(e.salary)
>>>>> FROM Employee e)
>>>>> SELECT FROM Employee WHERE this.salary > (SELECT AVG
>>>>> (this.salary) FROM Employee)
>>>>>
>>>>> Query API:
>>>>> Query q = pm.newQuery(Employee.class);
>>>>> q.setFilter("this.salary > averageSalary");
>>>>> Query sub = pm.newQuery(Employee.class);
>>>>> sub.setResult("avg(salary)");
>>>>> // The null value indicates that we're not overriding the
>>>>> candidates for the subquery
>>>>> // and thus it uses the entire extent of Employee
>>>>> q.declareVariables("float averageSalary(null)", sub);
>>>>>
>>>>>
>>>>>> Select employees named Joe who make more than the average of
>>>>>> all employees?
>>>>>
>>>>>
>>>>> Single-string JDOQL:
>>>>> SELECT FROM Employee WHERE this.name == 'Joe' && this.salary >
>>>>> (SELECT AVG(e.salary) FROM Employee e)
>>>>>
>>>>> Query API:
>>>>> Query q = pm.newQuery(Employee.class);
>>>>> q.setFilter("this.name == 'Joe' && this.salary > averageSalary");
>>>>>
>>>>> // This subquery generically defines "the average of all
>>>>> employeees"
>>>>> Query sub = pm.newQuery(Employee.class);
>>>>> sub.setResult("avg(salary)");
>>>>>
>>>>> // Note we could have reused the query instance from the
>>>>> previous example.
>>>>> q.declareVariables("float averageSalary(null)", sub);
>>>>>
>>>>>>
>>>>>> Select employees named Joe who make more than the average of
>>>>>> all employees at the same pay scale?
>>>>>
>>>>>
>>>>> Single-string JDOQL:
>>>>> SELECT FROM Employee WHERE this.name == 'Joe' && this.salary >
>>>>> (SELECT AVG(e.salary) FROM Employee e WHERE e.payScale ==
>>>>> this.payScale)
>>>>>
>>>>> Query API:
>>>>> Query q = pm.newQuery(Employee.class);
>>>>> q.setFilter("this.name == 'Joe' && this.salary > averageSalary");
>>>>>
>>>>> // Note that this is the same subquery instance as the previous
>>>>> pay scale example
>>>>> Query sub = pm.newQuery(Employee.class);
>>>>> sub.setFilter("payScale == ps");
>>>>> sub.declareParameters("PayScale ps");
>>>>> sub.setResult("avg(salary)");
>>>>>
>>>>> q.declareVariables("float averageSalary(null)", sub);
>>>>>
>>>>>> Select employees who make more than the average of all
>>>>>> employees at the same pay scale?
>>>>>
>>>>>
>>>>> Single-string JDOQL:
>>>>> SELECT FROM Employee WHERE this.salary > (SELECT AVG(e.salary)
>>>>> FROM Employee e WHERE e.payScale == this.payScale)
>>>>>
>>>>> Query API:
>>>>> Query q = pm.newQuery(Employee.class);
>>>>> q.setFilter("this.salary > averageSalary");
>>>>>
>>>>> // Same again
>>>>> Query sub = pm.newQuery(Employee.class);
>>>>> sub.setFilter("ps == this.payScale");
>>>>> sub.declareParameters("PayScale ps");
>>>>>
>>>>> q.declareVariables("float averageSalary(null, this.payScale)",
>>>>> sub);
>>>>>
>>>>> I'd like to hear other ideas for the exact syntax, but what do
>>>>> you think of the general concept?
>>>>>
>>>>> Wes
>>>>>
>>>>>
>>> [chop]
>>
>>
>> --
>> Michael Bouschen Tech@Spree Engineering GmbH
>> mailto:mbo.tech@spree.de http://www.tech.spree.de/
>> Tel.:++49/30/235 520-33 Buelowstr. 66
>> Fax.:++49/30/2175 2012 D-10783 Berlin
>>
>
> Craig Russell
> Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
> 408 276-5638 mailto:Craig.Russell@sun.com
> P.S. A good JDO? O, Gasp!
>
Craig Russell
Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
408 276-5638 mailto:Craig.Russell@sun.com
P.S. A good JDO? O, Gasp!
Re: JDOQL Subquery proposals
Posted by Wesley Biggs <we...@tralfamadore.com>.
Craig,
This approach (adding subqueries by value) sounds good to me. Simple,
elegant and easier on the spec-writing committee. ;-)
Wes
Craig L Russell wrote:
> Hi Wes and Michael,
>
> I'm just now reviewing this. Very nice indeed.
>
> We need to consider what happens if a subquery is modified after being
> added to an outer query. I'd like to propose that we treat the
> subquery as supplying certain pieces and excluding others. We have a
> similar situation with serialization of a Query instance, that I think
> we can model for this purpose.
>
> <spec>
> The class implementing the Query interface must be serializable. The
> serialized fields in-
> clude the candidate class, the filter, parameter declarations,
> variable declarations, imports,
> ordering specification, uniqueness, result specification, grouping
> specification, and result
> class. The candidate collection, limits on size, and number of skipped
> instances are not se-
> rialized. If a serialized instance is restored, it loses its
> association with its former Persis-
> tenceManager.
> </spec>
>
> I'd suggest that we adapt this for subqueries as follows:
>
> The query parameter is unmodified as a result of the addSubquery or
> subsequent execution
> of the outer query. Only some of the query parts are copied for use as
> the subquery. The parts in-
> clude the candidate class, filter, parameter declarations, variable
> declarations, imports,
> ordering specification, uniqueness, result specification, and grouping
> specification.
> The association with a PersistenceManager, the candidate collection,
> result
> class, limits on size, and number of skipped instances are not used.
>
> The implications are:
>
> changes made to the subquery after addSubquery are not reflected in
> the outer query
>
> the subquery can be executed without affecting the outer query
>
> the same subquery can be used multiple times in the same or other
> outer queries
>
> the candidate instances of the subquery itself are not considered;
> either the candidates are identified in the addSubquery method or the
> extent of the candidate class is used
>
> serialized/restored queries can be used as parameters for addSubquery
>
> there is no limitation on the use of a query that is bound to a
> different PersistenceManager
>
> the range feature is not usable (this is probably a good thing for
> subqueries)
>
> A few more comments below.
>
> On Sep 26, 2006, at 1:30 PM, Michael Bouschen wrote:
>
>> Hi Wes,
>>
>> I agree with what you are proposing below. Some more comments inline ...
>>> Hi Michael, thanks for reviewing.
>>>
>>> I agree, it is too much to overload the existing methods. I like
>>> your suggestion -- the only modification I would add is that the
>>> Query parameter come first as I think that would be slightly more
>>> readable/self-documenting.
>>>
>>> e.g. addSubquery(Query sub, String variableDeclaration, String
>>> candidateCollectionExpression)
>> Sounds good.
>>>
>>> Some minor points:
>>> addSubquery() in all its forms should throw an exception if ...
>>> -- sub.getPersistenceManager() != this.getPersistenceManager()
>
> no; see above
>
>>> -- sub has been closed
>
> queries are not closed; results are closed
>
>>> -- "this" has been closed (duh)
>
> queries are not closed; results are closed
>
>>> -- "this" has been made unmodifiable
>>> -- the derived type of the candidateCollectionExpression does not
>>> match the declared extent type of sub
>>> -- the variableDeclaration type does not match the declared return
>>> type of sub
>> I agree. Just a minor change: the element type of the
>> candidateCollectionExpression must be compatible with the candidate
>> class of the subquery.
>>>
>>> It would make things simpler if the IgnoreCache setting had to be
>>> the same for both as well. Or we might say the IgnoreCache value
>>> for the outer query overrides any subqueries.
>> Yes, the IgnoreCache value of the outer query should win.
>>>
>>> Also, while the candidates for the subquery will be overridden at
>>> the time when execute() is invoked on the outer query, it would be
>>> nice if that was non-mutating, though we should take some advice
>>> from implementers on what the following use case should do:
>>>
>>> Query avgSalary = pm.newQuery(Employee.class);
>>> avgSalary.setResult("avg(this.salary)");
>>> avgSalary.setCandidates(someEmployees); // for argument's sake
>>>
>>> // Invoke directly -- against someEmployees collection
>>> Float result1 = (Float) avgSalary.execute();
>>>
>>> Query aboveDeptAvg = pm.newQuery(Employee.class, "this.salary >
>>> avgSalary");
>>> aboveDeptAvg.addSubquery(avgSalary, "float avgSalary",
>>> "this.department.employees");
>>>
>>> // Invoke as subquery -- someEmployees collection is ignored
>>> Collection employees = (Collection) aboveDeptAvg.execute();
>>>
>>> // Now invoke the subquery directly again -- does this use
>>> someEmployees?
>
> Yes. When avgSalary was used as a subquery, the relevant parts of the
> query, not including the candidates, were used and avgSalary itself
> was not modified.
>
>>> Float result2 = (Float) avgSalary.execute();
>>>
>>> I would prefer that result1.equals(result2) -- this implies that the
>>> implementation must internally revert the candidate extent or
>>> collection for avgSalary after it is used as a subquery; for the
>>> spec it just means that using a Query as a subquery does not modify
>>> any of its own candidate settings.
>> Yes, I would prefer this, too. So let's see whether there are any
>> issues with this from the implementation point of view.
>
> I think it's easier for everyone to consider addSubquery to simply
> copy the parts of the query of interest and leave the query intact.
>
> Craig
>>
>> Thanks again for the feedback.
>>
>> Regards Michael
>>>
>>> Wes
>>>
>>>
>>> Michael Bouschen wrote:
>>>
>>>> Hi Wes,
>>>>
>>>> thanks for the feedback, it's definitely not too late.
>>>>
>>>> I like your proposal. It allows subqueries being supported in both
>>>> the SSJDOQL and the query API. I like the the idea of explicitly
>>>> setting subquery's candidate collection by passing an expression of
>>>> the outer query (and use the same mechanism for the parameters of
>>>> the subquery). This solves the biggest problem I had with using a
>>>> separate Query instance for the subquery: now the subquery instance
>>>> is self-contained and compiles, because it does not explicitly use
>>>> an expression from the outer query.
>>>>
>>>> I'm just wondering whether we could find a more intuitive syntax,
>>>> because name(candidateExpression[,parameterExpression...]) looks
>>>> more like a method than a variable. Furthermore, as a corner case,
>>>> it might be possible that a query uses more than one subquery. All
>>>> the subqueries would have to be defined in a single call of
>>>> outer.declareVariables. So how about, if we introduce a new method
>>>> called addSubquery to bind a single subquery to the outer query.
>>>> The method takes separate arguments for the candidate collection
>>>> expression and the parameters (if any). Actually the parameter
>>>> handling could be very similar to the parameters of the execute call:
>>>> addSubquery(String variableDeclaration, Query subquery, String
>>>> candidateCollectionExpr);
>>>> addSubquery(String variableDeclaration, Query subquery, String
>>>> candidateCollectionExpr, String parameter);
>>>> addSubqueryWithArray(String variableDeclaration, Query subquery,
>>>> String candidateCollectionExpr, String[] parameters);
>>>> addSubqueryWithMap(String variableDeclaration, Query subquery,
>>>> String candidateCollectionExpr, Map parameters);
>>>>
>>>> Looking at the first example from below, the definition of the
>>>> subquery would be the same. The only line that changes is the
>>>> declareVariable call. It is replaced by:
>>>> q.addSubquery(""float averageSalary", sub,
>>>> "this.department.employees");
>>>>
>>>> Just for completeness we should add a method to clear the
>>>> subqueries, such that you can reuse the outer query and bind new
>>>> subqueries for another execute call:
>>>> clearSubqueries();
>>>>
>>>> What do you think?
>>>>
>>>> If we think the above is option I would come up with an updated
>>>> summary of JDOQL changes to support subqueries and updated version
>>>> of the sample queries.
>>>>
>>>> Regards Michael
>>>>
>>>>> I'm ridiculously late in responding to this thread but if I may be
>>>>> so bold, I'll make a further suggestion.
>>>>>
>>>>> I like everything about the proposed approach except the
>>>>> requirement that subquery definitions must resort to single-string
>>>>> JDOQL syntax, even when using the API-based methods. I think this
>>>>> introduces asymmetry and discourages reuse and modularity.
>>>>>
>>>>> I would really like to see the ability to map variables to
>>>>> (sub)Query objects. There are two new capabilities introduced in
>>>>> the SSJDOQL version, and my opinion is that the API should match
>>>>> these feature by feature. The two features are:
>>>>> (1) The ability for a subquery to use an expression defined on the
>>>>> outer query as its candidate set.
>>>>> (2) The ability for a subquery to use expressions defined on the
>>>>> outer query as parameters.
>>>>>
>>>>> Therefore, for parity, we need an API-based way to declare these
>>>>> mappings, so that subqueries can be assigned both their candidate
>>>>> collections and their parameters dynamically.
>>>>>
>>>>> I propose an overloaded version of declareVariables that allows
>>>>> mapping variable names used in the outer query to (sub)Query
>>>>> instances that are correlated with candidates and parameters.
>>>>>
>>>>> void declareVariables(String variableList, Query... subquery)
>>>>>
>>>>> The variable declaration syntax should be extended to allow
>>>>> parameterized variables of the form
>>>>> "name(candidateExpression[,parameterExpression...])". "name"
>>>>> defines a variable name in the query; "candidateExpression"
>>>>> defines an expression (rooted in the namespace of the outer query)
>>>>> for the candidate extent to be bound to the subquery, where "null"
>>>>> signifies that the subquery candidate set is not being limited.
>>>>> "parameterExpression" identifies dynamic values for parameters
>>>>> declared by the subquery, again rooted in the namespace of the
>>>>> outer query doing the binding.
>>>>>
>>>>> To touch up Michael's examples:
>>>>>
>>>>> Select employees who make more than the average of their
>>>>> department employees?
>>>>>
>>>>> Single-string JDOQL:
>>>>> SELECT FROM Employee WHERE this.salary > (SELECT AVG(e.salary)
>>>>> FROM this.department.employees e)
>>>>> Query API:
>>>>> Query q = pm.newQuery(Employee.class);
>>>>> q.setFilter("this.salary > averageSalary");
>>>>>
>>>>> // Subquery definition is generic: for a given set of Employees,
>>>>> return the average salary
>>>>> Query sub = pm.newQuery(Employee.class);
>>>>> sub.setResult("avg(salary)");
>>>>>
>>>>> // Bind the subquery to the master query by identifying the
>>>>> candidate set
>>>>> q.declareVariables("float
>>>>> averageSalary(this.department.employees)", sub);
>>>>>
>>>>>> Select employees who make more than the average of the employees
>>>>>> in their department at the same pay scale?
>>>>>
>>>>>
>>>>> Single-string JDOQL:
>>>>> SELECT FROM Employee WHERE this.salary >
>>>>> (SELECT AVG(e.salary) FROM this.department.employees e WHERE
>>>>> e.payScale == this.payScale)
>>>>>
>>>>> Query API:
>>>>> Query q = pm.newQuery(Employee.class);
>>>>> q.setFilter("this.salary > averageSalary");
>>>>>
>>>>> // This subquery generically defines the average salary of a set
>>>>> of Employees at a given PayScale
>>>>> Query sub = pm.newQuery(Employee.class);
>>>>> sub.setFilter("this.payScale == ps");
>>>>> sub.declareParameters("PayScale ps");
>>>>> sub.setResult("avg(salary)");
>>>>>
>>>>> // Bind both a candidate set and the payScale parameter.
>>>>> q.declareVariables("float averageSalary(this.department.employees,
>>>>> this.payScale)", sub);
>>>>>
>>>>>> Select employees who make more than the average of all employees?
>>>>>
>>>>>
>>>>> Single-string JDOQL:
>>>>> SELECT FROM Employee WHERE this.salary > (SELECT AVG(e.salary)
>>>>> FROM Employee e)
>>>>> SELECT FROM Employee WHERE this.salary > (SELECT AVG(this.salary)
>>>>> FROM Employee)
>>>>>
>>>>> Query API:
>>>>> Query q = pm.newQuery(Employee.class);
>>>>> q.setFilter("this.salary > averageSalary");
>>>>> Query sub = pm.newQuery(Employee.class);
>>>>> sub.setResult("avg(salary)");
>>>>> // The null value indicates that we're not overriding the
>>>>> candidates for the subquery
>>>>> // and thus it uses the entire extent of Employee
>>>>> q.declareVariables("float averageSalary(null)", sub);
>>>>>
>>>>>
>>>>>> Select employees named Joe who make more than the average of all
>>>>>> employees?
>>>>>
>>>>>
>>>>> Single-string JDOQL:
>>>>> SELECT FROM Employee WHERE this.name == 'Joe' && this.salary >
>>>>> (SELECT AVG(e.salary) FROM Employee e)
>>>>>
>>>>> Query API:
>>>>> Query q = pm.newQuery(Employee.class);
>>>>> q.setFilter("this.name == 'Joe' && this.salary > averageSalary");
>>>>>
>>>>> // This subquery generically defines "the average of all employeees"
>>>>> Query sub = pm.newQuery(Employee.class);
>>>>> sub.setResult("avg(salary)");
>>>>>
>>>>> // Note we could have reused the query instance from the previous
>>>>> example.
>>>>> q.declareVariables("float averageSalary(null)", sub);
>>>>>
>>>>>>
>>>>>> Select employees named Joe who make more than the average of all
>>>>>> employees at the same pay scale?
>>>>>
>>>>>
>>>>> Single-string JDOQL:
>>>>> SELECT FROM Employee WHERE this.name == 'Joe' && this.salary >
>>>>> (SELECT AVG(e.salary) FROM Employee e WHERE e.payScale ==
>>>>> this.payScale)
>>>>>
>>>>> Query API:
>>>>> Query q = pm.newQuery(Employee.class);
>>>>> q.setFilter("this.name == 'Joe' && this.salary > averageSalary");
>>>>>
>>>>> // Note that this is the same subquery instance as the previous
>>>>> pay scale example
>>>>> Query sub = pm.newQuery(Employee.class);
>>>>> sub.setFilter("payScale == ps");
>>>>> sub.declareParameters("PayScale ps");
>>>>> sub.setResult("avg(salary)");
>>>>>
>>>>> q.declareVariables("float averageSalary(null)", sub);
>>>>>
>>>>>> Select employees who make more than the average of all employees
>>>>>> at the same pay scale?
>>>>>
>>>>>
>>>>> Single-string JDOQL:
>>>>> SELECT FROM Employee WHERE this.salary > (SELECT AVG(e.salary)
>>>>> FROM Employee e WHERE e.payScale == this.payScale)
>>>>>
>>>>> Query API:
>>>>> Query q = pm.newQuery(Employee.class);
>>>>> q.setFilter("this.salary > averageSalary");
>>>>>
>>>>> // Same again
>>>>> Query sub = pm.newQuery(Employee.class);
>>>>> sub.setFilter("ps == this.payScale");
>>>>> sub.declareParameters("PayScale ps");
>>>>>
>>>>> q.declareVariables("float averageSalary(null, this.payScale)", sub);
>>>>>
>>>>> I'd like to hear other ideas for the exact syntax, but what do you
>>>>> think of the general concept?
>>>>>
>>>>> Wes
>>>>>
>>>>>
>>> [chop]
>>
>>
>> --Michael Bouschen Tech@Spree Engineering GmbH
>> mailto:mbo.tech@spree.de http://www.tech.spree.de/
>> Tel.:++49/30/235 520-33 Buelowstr. 66
>> Fax.:++49/30/2175 2012 D-10783 Berlin
>>
>
> Craig Russell
> Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
> 408 276-5638 mailto:Craig.Russell@sun.com
> P.S. A good JDO? O, Gasp!
>
Re: JDOQL Subquery proposals
Posted by Craig L Russell <Cr...@Sun.COM>.
Hi Wes and Michael,
I'm just now reviewing this. Very nice indeed.
We need to consider what happens if a subquery is modified after
being added to an outer query. I'd like to propose that we treat the
subquery as supplying certain pieces and excluding others. We have a
similar situation with serialization of a Query instance, that I
think we can model for this purpose.
<spec>
The class implementing the Query interface must be serializable. The
serialized fields in-
clude the candidate class, the filter, parameter declarations,
variable declarations, imports,
ordering specification, uniqueness, result specification, grouping
specification, and result
class. The candidate collection, limits on size, and number of
skipped instances are not se-
rialized. If a serialized instance is restored, it loses its
association with its former Persis-
tenceManager.
</spec>
I'd suggest that we adapt this for subqueries as follows:
The query parameter is unmodified as a result of the addSubquery or
subsequent execution
of the outer query. Only some of the query parts are copied for use
as the subquery. The parts in-
clude the candidate class, filter, parameter declarations, variable
declarations, imports,
ordering specification, uniqueness, result specification, and
grouping specification.
The association with a PersistenceManager, the candidate collection,
result
class, limits on size, and number of skipped instances are not used.
The implications are:
changes made to the subquery after addSubquery are not reflected in
the outer query
the subquery can be executed without affecting the outer query
the same subquery can be used multiple times in the same or other
outer queries
the candidate instances of the subquery itself are not considered;
either the candidates are identified in the addSubquery method or the
extent of the candidate class is used
serialized/restored queries can be used as parameters for addSubquery
there is no limitation on the use of a query that is bound to a
different PersistenceManager
the range feature is not usable (this is probably a good thing for
subqueries)
A few more comments below.
On Sep 26, 2006, at 1:30 PM, Michael Bouschen wrote:
> Hi Wes,
>
> I agree with what you are proposing below. Some more comments
> inline ...
>> Hi Michael, thanks for reviewing.
>>
>> I agree, it is too much to overload the existing methods. I like
>> your suggestion -- the only modification I would add is that the
>> Query parameter come first as I think that would be slightly more
>> readable/self-documenting.
>>
>> e.g. addSubquery(Query sub, String variableDeclaration, String
>> candidateCollectionExpression)
> Sounds good.
>>
>> Some minor points:
>> addSubquery() in all its forms should throw an exception if ...
>> -- sub.getPersistenceManager() != this.getPersistenceManager()
no; see above
>> -- sub has been closed
queries are not closed; results are closed
>> -- "this" has been closed (duh)
queries are not closed; results are closed
>> -- "this" has been made unmodifiable
>> -- the derived type of the candidateCollectionExpression does not
>> match the declared extent type of sub
>> -- the variableDeclaration type does not match the declared return
>> type of sub
> I agree. Just a minor change: the element type of the
> candidateCollectionExpression must be compatible with the candidate
> class of the subquery.
>>
>> It would make things simpler if the IgnoreCache setting had to be
>> the same for both as well. Or we might say the IgnoreCache value
>> for the outer query overrides any subqueries.
> Yes, the IgnoreCache value of the outer query should win.
>>
>> Also, while the candidates for the subquery will be overridden at
>> the time when execute() is invoked on the outer query, it would be
>> nice if that was non-mutating, though we should take some advice
>> from implementers on what the following use case should do:
>>
>> Query avgSalary = pm.newQuery(Employee.class);
>> avgSalary.setResult("avg(this.salary)");
>> avgSalary.setCandidates(someEmployees); // for argument's sake
>>
>> // Invoke directly -- against someEmployees collection
>> Float result1 = (Float) avgSalary.execute();
>>
>> Query aboveDeptAvg = pm.newQuery(Employee.class, "this.salary >
>> avgSalary");
>> aboveDeptAvg.addSubquery(avgSalary, "float avgSalary",
>> "this.department.employees");
>>
>> // Invoke as subquery -- someEmployees collection is ignored
>> Collection employees = (Collection) aboveDeptAvg.execute();
>>
>> // Now invoke the subquery directly again -- does this use
>> someEmployees?
Yes. When avgSalary was used as a subquery, the relevant parts of the
query, not including the candidates, were used and avgSalary itself
was not modified.
>> Float result2 = (Float) avgSalary.execute();
>>
>> I would prefer that result1.equals(result2) -- this implies that
>> the implementation must internally revert the candidate extent or
>> collection for avgSalary after it is used as a subquery; for the
>> spec it just means that using a Query as a subquery does not
>> modify any of its own candidate settings.
> Yes, I would prefer this, too. So let's see whether there are any
> issues with this from the implementation point of view.
I think it's easier for everyone to consider addSubquery to simply
copy the parts of the query of interest and leave the query intact.
Craig
>
> Thanks again for the feedback.
>
> Regards Michael
>>
>> Wes
>>
>>
>> Michael Bouschen wrote:
>>
>>> Hi Wes,
>>>
>>> thanks for the feedback, it's definitely not too late.
>>>
>>> I like your proposal. It allows subqueries being supported in
>>> both the SSJDOQL and the query API. I like the the idea of
>>> explicitly setting subquery's candidate collection by passing an
>>> expression of the outer query (and use the same mechanism for the
>>> parameters of the subquery). This solves the biggest problem I
>>> had with using a separate Query instance for the subquery: now
>>> the subquery instance is self-contained and compiles, because it
>>> does not explicitly use an expression from the outer query.
>>>
>>> I'm just wondering whether we could find a more intuitive syntax,
>>> because name(candidateExpression[,parameterExpression...]) looks
>>> more like a method than a variable. Furthermore, as a corner
>>> case, it might be possible that a query uses more than one
>>> subquery. All the subqueries would have to be defined in a single
>>> call of outer.declareVariables. So how about, if we introduce a
>>> new method called addSubquery to bind a single subquery to the
>>> outer query. The method takes separate arguments for the
>>> candidate collection expression and the parameters (if any).
>>> Actually the parameter handling could be very similar to the
>>> parameters of the execute call:
>>> addSubquery(String variableDeclaration, Query subquery, String
>>> candidateCollectionExpr);
>>> addSubquery(String variableDeclaration, Query subquery, String
>>> candidateCollectionExpr, String parameter);
>>> addSubqueryWithArray(String variableDeclaration, Query subquery,
>>> String candidateCollectionExpr, String[] parameters);
>>> addSubqueryWithMap(String variableDeclaration, Query subquery,
>>> String candidateCollectionExpr, Map parameters);
>>>
>>> Looking at the first example from below, the definition of the
>>> subquery would be the same. The only line that changes is the
>>> declareVariable call. It is replaced by:
>>> q.addSubquery(""float averageSalary", sub,
>>> "this.department.employees");
>>>
>>> Just for completeness we should add a method to clear the
>>> subqueries, such that you can reuse the outer query and bind new
>>> subqueries for another execute call:
>>> clearSubqueries();
>>>
>>> What do you think?
>>>
>>> If we think the above is option I would come up with an updated
>>> summary of JDOQL changes to support subqueries and updated
>>> version of the sample queries.
>>>
>>> Regards Michael
>>>
>>>> I'm ridiculously late in responding to this thread but if I may
>>>> be so bold, I'll make a further suggestion.
>>>>
>>>> I like everything about the proposed approach except the
>>>> requirement that subquery definitions must resort to single-
>>>> string JDOQL syntax, even when using the API-based methods. I
>>>> think this introduces asymmetry and discourages reuse and
>>>> modularity.
>>>>
>>>> I would really like to see the ability to map variables to (sub)
>>>> Query objects. There are two new capabilities introduced in the
>>>> SSJDOQL version, and my opinion is that the API should match
>>>> these feature by feature. The two features are:
>>>> (1) The ability for a subquery to use an expression defined on
>>>> the outer query as its candidate set.
>>>> (2) The ability for a subquery to use expressions defined on the
>>>> outer query as parameters.
>>>>
>>>> Therefore, for parity, we need an API-based way to declare these
>>>> mappings, so that subqueries can be assigned both their
>>>> candidate collections and their parameters dynamically.
>>>>
>>>> I propose an overloaded version of declareVariables that allows
>>>> mapping variable names used in the outer query to (sub)Query
>>>> instances that are correlated with candidates and parameters.
>>>>
>>>> void declareVariables(String variableList, Query... subquery)
>>>>
>>>> The variable declaration syntax should be extended to allow
>>>> parameterized variables of the form "name(candidateExpression
>>>> [,parameterExpression...])". "name" defines a variable name in
>>>> the query; "candidateExpression" defines an expression (rooted
>>>> in the namespace of the outer query) for the candidate extent to
>>>> be bound to the subquery, where "null" signifies that the
>>>> subquery candidate set is not being limited.
>>>> "parameterExpression" identifies dynamic values for parameters
>>>> declared by the subquery, again rooted in the namespace of the
>>>> outer query doing the binding.
>>>>
>>>> To touch up Michael's examples:
>>>>
>>>> Select employees who make more than the average of their
>>>> department employees?
>>>>
>>>> Single-string JDOQL:
>>>> SELECT FROM Employee WHERE this.salary > (SELECT AVG(e.salary)
>>>> FROM this.department.employees e)
>>>> Query API:
>>>> Query q = pm.newQuery(Employee.class);
>>>> q.setFilter("this.salary > averageSalary");
>>>>
>>>> // Subquery definition is generic: for a given set of Employees,
>>>> return the average salary
>>>> Query sub = pm.newQuery(Employee.class);
>>>> sub.setResult("avg(salary)");
>>>>
>>>> // Bind the subquery to the master query by identifying the
>>>> candidate set
>>>> q.declareVariables("float averageSalary
>>>> (this.department.employees)", sub);
>>>>
>>>>> Select employees who make more than the average of the
>>>>> employees in their department at the same pay scale?
>>>>
>>>>
>>>> Single-string JDOQL:
>>>> SELECT FROM Employee WHERE this.salary >
>>>> (SELECT AVG(e.salary) FROM this.department.employees e WHERE
>>>> e.payScale == this.payScale)
>>>>
>>>> Query API:
>>>> Query q = pm.newQuery(Employee.class);
>>>> q.setFilter("this.salary > averageSalary");
>>>>
>>>> // This subquery generically defines the average salary of a set
>>>> of Employees at a given PayScale
>>>> Query sub = pm.newQuery(Employee.class);
>>>> sub.setFilter("this.payScale == ps");
>>>> sub.declareParameters("PayScale ps");
>>>> sub.setResult("avg(salary)");
>>>>
>>>> // Bind both a candidate set and the payScale parameter.
>>>> q.declareVariables("float averageSalary
>>>> (this.department.employees, this.payScale)", sub);
>>>>
>>>>> Select employees who make more than the average of all employees?
>>>>
>>>>
>>>> Single-string JDOQL:
>>>> SELECT FROM Employee WHERE this.salary > (SELECT AVG(e.salary)
>>>> FROM Employee e)
>>>> SELECT FROM Employee WHERE this.salary > (SELECT AVG
>>>> (this.salary) FROM Employee)
>>>>
>>>> Query API:
>>>> Query q = pm.newQuery(Employee.class);
>>>> q.setFilter("this.salary > averageSalary");
>>>> Query sub = pm.newQuery(Employee.class);
>>>> sub.setResult("avg(salary)");
>>>> // The null value indicates that we're not overriding the
>>>> candidates for the subquery
>>>> // and thus it uses the entire extent of Employee
>>>> q.declareVariables("float averageSalary(null)", sub);
>>>>
>>>>
>>>>> Select employees named Joe who make more than the average of
>>>>> all employees?
>>>>
>>>>
>>>> Single-string JDOQL:
>>>> SELECT FROM Employee WHERE this.name == 'Joe' && this.salary >
>>>> (SELECT AVG(e.salary) FROM Employee e)
>>>>
>>>> Query API:
>>>> Query q = pm.newQuery(Employee.class);
>>>> q.setFilter("this.name == 'Joe' && this.salary > averageSalary");
>>>>
>>>> // This subquery generically defines "the average of all
>>>> employeees"
>>>> Query sub = pm.newQuery(Employee.class);
>>>> sub.setResult("avg(salary)");
>>>>
>>>> // Note we could have reused the query instance from the
>>>> previous example.
>>>> q.declareVariables("float averageSalary(null)", sub);
>>>>
>>>>>
>>>>> Select employees named Joe who make more than the average of
>>>>> all employees at the same pay scale?
>>>>
>>>>
>>>> Single-string JDOQL:
>>>> SELECT FROM Employee WHERE this.name == 'Joe' && this.salary >
>>>> (SELECT AVG(e.salary) FROM Employee e WHERE e.payScale ==
>>>> this.payScale)
>>>>
>>>> Query API:
>>>> Query q = pm.newQuery(Employee.class);
>>>> q.setFilter("this.name == 'Joe' && this.salary > averageSalary");
>>>>
>>>> // Note that this is the same subquery instance as the previous
>>>> pay scale example
>>>> Query sub = pm.newQuery(Employee.class);
>>>> sub.setFilter("payScale == ps");
>>>> sub.declareParameters("PayScale ps");
>>>> sub.setResult("avg(salary)");
>>>>
>>>> q.declareVariables("float averageSalary(null)", sub);
>>>>
>>>>> Select employees who make more than the average of all
>>>>> employees at the same pay scale?
>>>>
>>>>
>>>> Single-string JDOQL:
>>>> SELECT FROM Employee WHERE this.salary > (SELECT AVG(e.salary)
>>>> FROM Employee e WHERE e.payScale == this.payScale)
>>>>
>>>> Query API:
>>>> Query q = pm.newQuery(Employee.class);
>>>> q.setFilter("this.salary > averageSalary");
>>>>
>>>> // Same again
>>>> Query sub = pm.newQuery(Employee.class);
>>>> sub.setFilter("ps == this.payScale");
>>>> sub.declareParameters("PayScale ps");
>>>>
>>>> q.declareVariables("float averageSalary(null, this.payScale)",
>>>> sub);
>>>>
>>>> I'd like to hear other ideas for the exact syntax, but what do
>>>> you think of the general concept?
>>>>
>>>> Wes
>>>>
>>>>
>> [chop]
>
>
> --
> Michael Bouschen Tech@Spree Engineering GmbH
> mailto:mbo.tech@spree.de http://www.tech.spree.de/
> Tel.:++49/30/235 520-33 Buelowstr. 66
> Fax.:++49/30/2175 2012 D-10783 Berlin
>
Craig Russell
Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
408 276-5638 mailto:Craig.Russell@sun.com
P.S. A good JDO? O, Gasp!
Re: JDOQL Subquery proposals
Posted by Michael Bouschen <mb...@spree.de>.
Hi Wes,
I agree with what you are proposing below. Some more comments inline ...
> Hi Michael, thanks for reviewing.
>
> I agree, it is too much to overload the existing methods. I like your
> suggestion -- the only modification I would add is that the Query
> parameter come first as I think that would be slightly more
> readable/self-documenting.
>
> e.g. addSubquery(Query sub, String variableDeclaration, String
> candidateCollectionExpression)
Sounds good.
>
> Some minor points:
> addSubquery() in all its forms should throw an exception if ...
> -- sub.getPersistenceManager() != this.getPersistenceManager()
> -- sub has been closed
> -- "this" has been closed (duh)
> -- "this" has been made unmodifiable
> -- the derived type of the candidateCollectionExpression does not
> match the declared extent type of sub
> -- the variableDeclaration type does not match the declared return
> type of sub
I agree. Just a minor change: the element type of the
candidateCollectionExpression must be compatible with the candidate
class of the subquery.
>
> It would make things simpler if the IgnoreCache setting had to be the
> same for both as well. Or we might say the IgnoreCache value for the
> outer query overrides any subqueries.
Yes, the IgnoreCache value of the outer query should win.
>
> Also, while the candidates for the subquery will be overridden at the
> time when execute() is invoked on the outer query, it would be nice if
> that was non-mutating, though we should take some advice from
> implementers on what the following use case should do:
>
> Query avgSalary = pm.newQuery(Employee.class);
> avgSalary.setResult("avg(this.salary)");
> avgSalary.setCandidates(someEmployees); // for argument's sake
>
> // Invoke directly -- against someEmployees collection
> Float result1 = (Float) avgSalary.execute();
>
> Query aboveDeptAvg = pm.newQuery(Employee.class, "this.salary >
> avgSalary");
> aboveDeptAvg.addSubquery(avgSalary, "float avgSalary",
> "this.department.employees");
>
> // Invoke as subquery -- someEmployees collection is ignored
> Collection employees = (Collection) aboveDeptAvg.execute();
>
> // Now invoke the subquery directly again -- does this use someEmployees?
> Float result2 = (Float) avgSalary.execute();
>
> I would prefer that result1.equals(result2) -- this implies that the
> implementation must internally revert the candidate extent or
> collection for avgSalary after it is used as a subquery; for the spec
> it just means that using a Query as a subquery does not modify any of
> its own candidate settings.
Yes, I would prefer this, too. So let's see whether there are any issues
with this from the implementation point of view.
Thanks again for the feedback.
Regards Michael
>
> Wes
>
>
> Michael Bouschen wrote:
>
>> Hi Wes,
>>
>> thanks for the feedback, it's definitely not too late.
>>
>> I like your proposal. It allows subqueries being supported in both
>> the SSJDOQL and the query API. I like the the idea of explicitly
>> setting subquery's candidate collection by passing an expression of
>> the outer query (and use the same mechanism for the parameters of the
>> subquery). This solves the biggest problem I had with using a
>> separate Query instance for the subquery: now the subquery instance
>> is self-contained and compiles, because it does not explicitly use an
>> expression from the outer query.
>>
>> I'm just wondering whether we could find a more intuitive syntax,
>> because name(candidateExpression[,parameterExpression...]) looks more
>> like a method than a variable. Furthermore, as a corner case, it
>> might be possible that a query uses more than one subquery. All the
>> subqueries would have to be defined in a single call of
>> outer.declareVariables. So how about, if we introduce a new method
>> called addSubquery to bind a single subquery to the outer query. The
>> method takes separate arguments for the candidate collection
>> expression and the parameters (if any). Actually the parameter
>> handling could be very similar to the parameters of the execute call:
>> addSubquery(String variableDeclaration, Query subquery, String
>> candidateCollectionExpr);
>> addSubquery(String variableDeclaration, Query subquery, String
>> candidateCollectionExpr, String parameter);
>> addSubqueryWithArray(String variableDeclaration, Query subquery,
>> String candidateCollectionExpr, String[] parameters);
>> addSubqueryWithMap(String variableDeclaration, Query subquery,
>> String candidateCollectionExpr, Map parameters);
>>
>> Looking at the first example from below, the definition of the
>> subquery would be the same. The only line that changes is the
>> declareVariable call. It is replaced by:
>> q.addSubquery(""float averageSalary", sub,
>> "this.department.employees");
>>
>> Just for completeness we should add a method to clear the subqueries,
>> such that you can reuse the outer query and bind new subqueries for
>> another execute call:
>> clearSubqueries();
>>
>> What do you think?
>>
>> If we think the above is option I would come up with an updated
>> summary of JDOQL changes to support subqueries and updated version of
>> the sample queries.
>>
>> Regards Michael
>>
>>> I'm ridiculously late in responding to this thread but if I may be
>>> so bold, I'll make a further suggestion.
>>>
>>> I like everything about the proposed approach except the requirement
>>> that subquery definitions must resort to single-string JDOQL syntax,
>>> even when using the API-based methods. I think this introduces
>>> asymmetry and discourages reuse and modularity.
>>>
>>> I would really like to see the ability to map variables to
>>> (sub)Query objects. There are two new capabilities introduced in
>>> the SSJDOQL version, and my opinion is that the API should match
>>> these feature by feature. The two features are:
>>> (1) The ability for a subquery to use an expression defined on the
>>> outer query as its candidate set.
>>> (2) The ability for a subquery to use expressions defined on the
>>> outer query as parameters.
>>>
>>> Therefore, for parity, we need an API-based way to declare these
>>> mappings, so that subqueries can be assigned both their candidate
>>> collections and their parameters dynamically.
>>>
>>> I propose an overloaded version of declareVariables that allows
>>> mapping variable names used in the outer query to (sub)Query
>>> instances that are correlated with candidates and parameters.
>>>
>>> void declareVariables(String variableList, Query... subquery)
>>>
>>> The variable declaration syntax should be extended to allow
>>> parameterized variables of the form
>>> "name(candidateExpression[,parameterExpression...])". "name"
>>> defines a variable name in the query; "candidateExpression" defines
>>> an expression (rooted in the namespace of the outer query) for the
>>> candidate extent to be bound to the subquery, where "null" signifies
>>> that the subquery candidate set is not being limited.
>>> "parameterExpression" identifies dynamic values for parameters
>>> declared by the subquery, again rooted in the namespace of the outer
>>> query doing the binding.
>>>
>>> To touch up Michael's examples:
>>>
>>> Select employees who make more than the average of their department
>>> employees?
>>>
>>> Single-string JDOQL:
>>> SELECT FROM Employee WHERE this.salary > (SELECT AVG(e.salary) FROM
>>> this.department.employees e)
>>> Query API:
>>> Query q = pm.newQuery(Employee.class);
>>> q.setFilter("this.salary > averageSalary");
>>>
>>> // Subquery definition is generic: for a given set of Employees,
>>> return the average salary
>>> Query sub = pm.newQuery(Employee.class);
>>> sub.setResult("avg(salary)");
>>>
>>> // Bind the subquery to the master query by identifying the
>>> candidate set
>>> q.declareVariables("float averageSalary(this.department.employees)",
>>> sub);
>>>
>>>> Select employees who make more than the average of the employees in
>>>> their department at the same pay scale?
>>>
>>>
>>> Single-string JDOQL:
>>> SELECT FROM Employee WHERE this.salary >
>>> (SELECT AVG(e.salary) FROM this.department.employees e WHERE
>>> e.payScale == this.payScale)
>>>
>>> Query API:
>>> Query q = pm.newQuery(Employee.class);
>>> q.setFilter("this.salary > averageSalary");
>>>
>>> // This subquery generically defines the average salary of a set of
>>> Employees at a given PayScale
>>> Query sub = pm.newQuery(Employee.class);
>>> sub.setFilter("this.payScale == ps");
>>> sub.declareParameters("PayScale ps");
>>> sub.setResult("avg(salary)");
>>>
>>> // Bind both a candidate set and the payScale parameter.
>>> q.declareVariables("float averageSalary(this.department.employees,
>>> this.payScale)", sub);
>>>
>>>> Select employees who make more than the average of all employees?
>>>
>>>
>>> Single-string JDOQL:
>>> SELECT FROM Employee WHERE this.salary > (SELECT AVG(e.salary) FROM
>>> Employee e)
>>> SELECT FROM Employee WHERE this.salary > (SELECT AVG(this.salary)
>>> FROM Employee)
>>>
>>> Query API:
>>> Query q = pm.newQuery(Employee.class);
>>> q.setFilter("this.salary > averageSalary");
>>> Query sub = pm.newQuery(Employee.class);
>>> sub.setResult("avg(salary)");
>>> // The null value indicates that we're not overriding the candidates
>>> for the subquery
>>> // and thus it uses the entire extent of Employee
>>> q.declareVariables("float averageSalary(null)", sub);
>>>
>>>
>>>> Select employees named Joe who make more than the average of all
>>>> employees?
>>>
>>>
>>> Single-string JDOQL:
>>> SELECT FROM Employee WHERE this.name == 'Joe' && this.salary >
>>> (SELECT AVG(e.salary) FROM Employee e)
>>>
>>> Query API:
>>> Query q = pm.newQuery(Employee.class);
>>> q.setFilter("this.name == 'Joe' && this.salary > averageSalary");
>>>
>>> // This subquery generically defines "the average of all employeees"
>>> Query sub = pm.newQuery(Employee.class);
>>> sub.setResult("avg(salary)");
>>>
>>> // Note we could have reused the query instance from the previous
>>> example.
>>> q.declareVariables("float averageSalary(null)", sub);
>>>
>>>>
>>>> Select employees named Joe who make more than the average of all
>>>> employees at the same pay scale?
>>>
>>>
>>> Single-string JDOQL:
>>> SELECT FROM Employee WHERE this.name == 'Joe' && this.salary >
>>> (SELECT AVG(e.salary) FROM Employee e WHERE e.payScale ==
>>> this.payScale)
>>>
>>> Query API:
>>> Query q = pm.newQuery(Employee.class);
>>> q.setFilter("this.name == 'Joe' && this.salary > averageSalary");
>>>
>>> // Note that this is the same subquery instance as the previous pay
>>> scale example
>>> Query sub = pm.newQuery(Employee.class);
>>> sub.setFilter("payScale == ps");
>>> sub.declareParameters("PayScale ps");
>>> sub.setResult("avg(salary)");
>>>
>>> q.declareVariables("float averageSalary(null)", sub);
>>>
>>>> Select employees who make more than the average of all employees at
>>>> the same pay scale?
>>>
>>>
>>> Single-string JDOQL:
>>> SELECT FROM Employee WHERE this.salary > (SELECT AVG(e.salary) FROM
>>> Employee e WHERE e.payScale == this.payScale)
>>>
>>> Query API:
>>> Query q = pm.newQuery(Employee.class);
>>> q.setFilter("this.salary > averageSalary");
>>>
>>> // Same again
>>> Query sub = pm.newQuery(Employee.class);
>>> sub.setFilter("ps == this.payScale");
>>> sub.declareParameters("PayScale ps");
>>>
>>> q.declareVariables("float averageSalary(null, this.payScale)", sub);
>>>
>>> I'd like to hear other ideas for the exact syntax, but what do you
>>> think of the general concept?
>>>
>>> Wes
>>>
>>>
> [chop]
--
Michael Bouschen Tech@Spree Engineering GmbH
mailto:mbo.tech@spree.de http://www.tech.spree.de/
Tel.:++49/30/235 520-33 Buelowstr. 66
Fax.:++49/30/2175 2012 D-10783 Berlin
Re: JDOQL Subquery proposals
Posted by Wes Biggs <we...@tralfamadore.com>.
Hi Michael, thanks for reviewing.
I agree, it is too much to overload the existing methods. I like your
suggestion -- the only modification I would add is that the Query
parameter come first as I think that would be slightly more
readable/self-documenting.
e.g. addSubquery(Query sub, String variableDeclaration, String
candidateCollectionExpression)
Some minor points:
addSubquery() in all its forms should throw an exception if ...
-- sub.getPersistenceManager() != this.getPersistenceManager()
-- sub has been closed
-- "this" has been closed (duh)
-- "this" has been made unmodifiable
-- the derived type of the candidateCollectionExpression does not match
the declared extent type of sub
-- the variableDeclaration type does not match the declared return type
of sub
It would make things simpler if the IgnoreCache setting had to be the
same for both as well. Or we might say the IgnoreCache value for the
outer query overrides any subqueries.
Also, while the candidates for the subquery will be overridden at the
time when execute() is invoked on the outer query, it would be nice if
that was non-mutating, though we should take some advice from
implementers on what the following use case should do:
Query avgSalary = pm.newQuery(Employee.class);
avgSalary.setResult("avg(this.salary)");
avgSalary.setCandidates(someEmployees); // for argument's sake
// Invoke directly -- against someEmployees collection
Float result1 = (Float) avgSalary.execute();
Query aboveDeptAvg = pm.newQuery(Employee.class, "this.salary > avgSalary");
aboveDeptAvg.addSubquery(avgSalary, "float avgSalary",
"this.department.employees");
// Invoke as subquery -- someEmployees collection is ignored
Collection employees = (Collection) aboveDeptAvg.execute();
// Now invoke the subquery directly again -- does this use someEmployees?
Float result2 = (Float) avgSalary.execute();
I would prefer that result1.equals(result2) -- this implies that the
implementation must internally revert the candidate extent or collection
for avgSalary after it is used as a subquery; for the spec it just means
that using a Query as a subquery does not modify any of its own
candidate settings.
Wes
Michael Bouschen wrote:
> Hi Wes,
>
> thanks for the feedback, it's definitely not too late.
>
> I like your proposal. It allows subqueries being supported in both the
> SSJDOQL and the query API. I like the the idea of explicitly setting
> subquery's candidate collection by passing an expression of the outer
> query (and use the same mechanism for the parameters of the subquery).
> This solves the biggest problem I had with using a separate Query
> instance for the subquery: now the subquery instance is self-contained
> and compiles, because it does not explicitly use an expression from
> the outer query.
>
> I'm just wondering whether we could find a more intuitive syntax,
> because name(candidateExpression[,parameterExpression...]) looks more
> like a method than a variable. Furthermore, as a corner case, it might
> be possible that a query uses more than one subquery. All the
> subqueries would have to be defined in a single call of
> outer.declareVariables. So how about, if we introduce a new method
> called addSubquery to bind a single subquery to the outer query. The
> method takes separate arguments for the candidate collection
> expression and the parameters (if any). Actually the parameter
> handling could be very similar to the parameters of the execute call:
> addSubquery(String variableDeclaration, Query subquery, String
> candidateCollectionExpr);
> addSubquery(String variableDeclaration, Query subquery, String
> candidateCollectionExpr, String parameter);
> addSubqueryWithArray(String variableDeclaration, Query subquery,
> String candidateCollectionExpr, String[] parameters);
> addSubqueryWithMap(String variableDeclaration, Query subquery, String
> candidateCollectionExpr, Map parameters);
>
> Looking at the first example from below, the definition of the
> subquery would be the same. The only line that changes is the
> declareVariable call. It is replaced by:
> q.addSubquery(""float averageSalary", sub, "this.department.employees");
>
> Just for completeness we should add a method to clear the subqueries,
> such that you can reuse the outer query and bind new subqueries for
> another execute call:
> clearSubqueries();
>
> What do you think?
>
> If we think the above is option I would come up with an updated
> summary of JDOQL changes to support subqueries and updated version of
> the sample queries.
>
> Regards Michael
>
>> I'm ridiculously late in responding to this thread but if I may be so
>> bold, I'll make a further suggestion.
>>
>> I like everything about the proposed approach except the requirement
>> that subquery definitions must resort to single-string JDOQL syntax,
>> even when using the API-based methods. I think this introduces
>> asymmetry and discourages reuse and modularity.
>>
>> I would really like to see the ability to map variables to (sub)Query
>> objects. There are two new capabilities introduced in the SSJDOQL
>> version, and my opinion is that the API should match these feature by
>> feature. The two features are:
>> (1) The ability for a subquery to use an expression defined on the
>> outer query as its candidate set.
>> (2) The ability for a subquery to use expressions defined on the
>> outer query as parameters.
>>
>> Therefore, for parity, we need an API-based way to declare these
>> mappings, so that subqueries can be assigned both their candidate
>> collections and their parameters dynamically.
>>
>> I propose an overloaded version of declareVariables that allows
>> mapping variable names used in the outer query to (sub)Query
>> instances that are correlated with candidates and parameters.
>>
>> void declareVariables(String variableList, Query... subquery)
>>
>> The variable declaration syntax should be extended to allow
>> parameterized variables of the form
>> "name(candidateExpression[,parameterExpression...])". "name" defines
>> a variable name in the query; "candidateExpression" defines an
>> expression (rooted in the namespace of the outer query) for the
>> candidate extent to be bound to the subquery, where "null" signifies
>> that the subquery candidate set is not being limited.
>> "parameterExpression" identifies dynamic values for parameters
>> declared by the subquery, again rooted in the namespace of the outer
>> query doing the binding.
>>
>> To touch up Michael's examples:
>>
>> Select employees who make more than the average of their department
>> employees?
>>
>> Single-string JDOQL:
>> SELECT FROM Employee WHERE this.salary > (SELECT AVG(e.salary) FROM
>> this.department.employees e)
>> Query API:
>> Query q = pm.newQuery(Employee.class);
>> q.setFilter("this.salary > averageSalary");
>>
>> // Subquery definition is generic: for a given set of Employees,
>> return the average salary
>> Query sub = pm.newQuery(Employee.class);
>> sub.setResult("avg(salary)");
>>
>> // Bind the subquery to the master query by identifying the candidate
>> set
>> q.declareVariables("float averageSalary(this.department.employees)",
>> sub);
>>
>>> Select employees who make more than the average of the employees in
>>> their department at the same pay scale?
>>
>>
>> Single-string JDOQL:
>> SELECT FROM Employee WHERE this.salary >
>> (SELECT AVG(e.salary) FROM this.department.employees e WHERE
>> e.payScale == this.payScale)
>>
>> Query API:
>> Query q = pm.newQuery(Employee.class);
>> q.setFilter("this.salary > averageSalary");
>>
>> // This subquery generically defines the average salary of a set of
>> Employees at a given PayScale
>> Query sub = pm.newQuery(Employee.class);
>> sub.setFilter("this.payScale == ps");
>> sub.declareParameters("PayScale ps");
>> sub.setResult("avg(salary)");
>>
>> // Bind both a candidate set and the payScale parameter.
>> q.declareVariables("float averageSalary(this.department.employees,
>> this.payScale)", sub);
>>
>>> Select employees who make more than the average of all employees?
>>
>>
>> Single-string JDOQL:
>> SELECT FROM Employee WHERE this.salary > (SELECT AVG(e.salary) FROM
>> Employee e)
>> SELECT FROM Employee WHERE this.salary > (SELECT AVG(this.salary)
>> FROM Employee)
>>
>> Query API:
>> Query q = pm.newQuery(Employee.class);
>> q.setFilter("this.salary > averageSalary");
>> Query sub = pm.newQuery(Employee.class);
>> sub.setResult("avg(salary)");
>> // The null value indicates that we're not overriding the candidates
>> for the subquery
>> // and thus it uses the entire extent of Employee
>> q.declareVariables("float averageSalary(null)", sub);
>>
>>
>>> Select employees named Joe who make more than the average of all
>>> employees?
>>
>>
>> Single-string JDOQL:
>> SELECT FROM Employee WHERE this.name == 'Joe' && this.salary >
>> (SELECT AVG(e.salary) FROM Employee e)
>>
>> Query API:
>> Query q = pm.newQuery(Employee.class);
>> q.setFilter("this.name == 'Joe' && this.salary > averageSalary");
>>
>> // This subquery generically defines "the average of all employeees"
>> Query sub = pm.newQuery(Employee.class);
>> sub.setResult("avg(salary)");
>>
>> // Note we could have reused the query instance from the previous
>> example.
>> q.declareVariables("float averageSalary(null)", sub);
>>
>>>
>>> Select employees named Joe who make more than the average of all
>>> employees at the same pay scale?
>>
>>
>> Single-string JDOQL:
>> SELECT FROM Employee WHERE this.name == 'Joe' && this.salary >
>> (SELECT AVG(e.salary) FROM Employee e WHERE e.payScale ==
>> this.payScale)
>>
>> Query API:
>> Query q = pm.newQuery(Employee.class);
>> q.setFilter("this.name == 'Joe' && this.salary > averageSalary");
>>
>> // Note that this is the same subquery instance as the previous pay
>> scale example
>> Query sub = pm.newQuery(Employee.class);
>> sub.setFilter("payScale == ps");
>> sub.declareParameters("PayScale ps");
>> sub.setResult("avg(salary)");
>>
>> q.declareVariables("float averageSalary(null)", sub);
>>
>>> Select employees who make more than the average of all employees at
>>> the same pay scale?
>>
>>
>> Single-string JDOQL:
>> SELECT FROM Employee WHERE this.salary > (SELECT AVG(e.salary) FROM
>> Employee e WHERE e.payScale == this.payScale)
>>
>> Query API:
>> Query q = pm.newQuery(Employee.class);
>> q.setFilter("this.salary > averageSalary");
>>
>> // Same again
>> Query sub = pm.newQuery(Employee.class);
>> sub.setFilter("ps == this.payScale");
>> sub.declareParameters("PayScale ps");
>>
>> q.declareVariables("float averageSalary(null, this.payScale)", sub);
>>
>> I'd like to hear other ideas for the exact syntax, but what do you
>> think of the general concept?
>>
>> Wes
>>
>>
[chop]
Re: JDOQL Subquery proposals
Posted by Michael Bouschen <mb...@spree.de>.
Hi Wes,
thanks for the feedback, it's definitely not too late.
I like your proposal. It allows subqueries being supported in both the
SSJDOQL and the query API. I like the the idea of explicitly setting
subquery's candidate collection by passing an expression of the outer
query (and use the same mechanism for the parameters of the subquery).
This solves the biggest problem I had with using a separate Query
instance for the subquery: now the subquery instance is self-contained
and compiles, because it does not explicitly use an expression from the
outer query.
I'm just wondering whether we could find a more intuitive syntax,
because name(candidateExpression[,parameterExpression...]) looks more
like a method than a variable. Furthermore, as a corner case, it might
be possible that a query uses more than one subquery. All the subqueries
would have to be defined in a single call of outer.declareVariables. So
how about, if we introduce a new method called addSubquery to bind a
single subquery to the outer query. The method takes separate arguments
for the candidate collection expression and the parameters (if any).
Actually the parameter handling could be very similar to the parameters
of the execute call:
addSubquery(String variableDeclaration, Query subquery, String
candidateCollectionExpr);
addSubquery(String variableDeclaration, Query subquery, String
candidateCollectionExpr, String parameter);
addSubqueryWithArray(String variableDeclaration, Query subquery,
String candidateCollectionExpr, String[] parameters);
addSubqueryWithMap(String variableDeclaration, Query subquery, String
candidateCollectionExpr, Map parameters);
Looking at the first example from below, the definition of the subquery
would be the same. The only line that changes is the declareVariable
call. It is replaced by:
q.addSubquery(""float averageSalary", sub, "this.department.employees");
Just for completeness we should add a method to clear the subqueries,
such that you can reuse the outer query and bind new subqueries for
another execute call:
clearSubqueries();
What do you think?
If we think the above is option I would come up with an updated summary
of JDOQL changes to support subqueries and updated version of the sample
queries.
Regards Michael
> I'm ridiculously late in responding to this thread but if I may be so
> bold, I'll make a further suggestion.
>
> I like everything about the proposed approach except the requirement
> that subquery definitions must resort to single-string JDOQL syntax,
> even when using the API-based methods. I think this introduces
> asymmetry and discourages reuse and modularity.
>
> I would really like to see the ability to map variables to (sub)Query
> objects. There are two new capabilities introduced in the SSJDOQL
> version, and my opinion is that the API should match these feature by
> feature. The two features are:
> (1) The ability for a subquery to use an expression defined on the
> outer query as its candidate set.
> (2) The ability for a subquery to use expressions defined on the outer
> query as parameters.
>
> Therefore, for parity, we need an API-based way to declare these
> mappings, so that subqueries can be assigned both their candidate
> collections and their parameters dynamically.
>
> I propose an overloaded version of declareVariables that allows
> mapping variable names used in the outer query to (sub)Query instances
> that are correlated with candidates and parameters.
>
> void declareVariables(String variableList, Query... subquery)
>
> The variable declaration syntax should be extended to allow
> parameterized variables of the form
> "name(candidateExpression[,parameterExpression...])". "name" defines
> a variable name in the query; "candidateExpression" defines an
> expression (rooted in the namespace of the outer query) for the
> candidate extent to be bound to the subquery, where "null" signifies
> that the subquery candidate set is not being limited.
> "parameterExpression" identifies dynamic values for parameters
> declared by the subquery, again rooted in the namespace of the outer
> query doing the binding.
>
> To touch up Michael's examples:
>
> Select employees who make more than the average of their department
> employees?
>
> Single-string JDOQL:
> SELECT FROM Employee WHERE this.salary > (SELECT AVG(e.salary) FROM
> this.department.employees e)
> Query API:
> Query q = pm.newQuery(Employee.class);
> q.setFilter("this.salary > averageSalary");
>
> // Subquery definition is generic: for a given set of Employees,
> return the average salary
> Query sub = pm.newQuery(Employee.class);
> sub.setResult("avg(salary)");
>
> // Bind the subquery to the master query by identifying the candidate set
> q.declareVariables("float averageSalary(this.department.employees)",
> sub);
>
>> Select employees who make more than the average of the employees in
>> their department at the same pay scale?
>
> Single-string JDOQL:
> SELECT FROM Employee WHERE this.salary >
> (SELECT AVG(e.salary) FROM this.department.employees e WHERE
> e.payScale == this.payScale)
>
> Query API:
> Query q = pm.newQuery(Employee.class);
> q.setFilter("this.salary > averageSalary");
>
> // This subquery generically defines the average salary of a set of
> Employees at a given PayScale
> Query sub = pm.newQuery(Employee.class);
> sub.setFilter("this.payScale == ps");
> sub.declareParameters("PayScale ps");
> sub.setResult("avg(salary)");
>
> // Bind both a candidate set and the payScale parameter.
> q.declareVariables("float averageSalary(this.department.employees,
> this.payScale)", sub);
>
>> Select employees who make more than the average of all employees?
>
> Single-string JDOQL:
> SELECT FROM Employee WHERE this.salary > (SELECT AVG(e.salary) FROM
> Employee e)
> SELECT FROM Employee WHERE this.salary > (SELECT AVG(this.salary)
> FROM Employee)
>
> Query API:
> Query q = pm.newQuery(Employee.class);
> q.setFilter("this.salary > averageSalary");
> Query sub = pm.newQuery(Employee.class);
> sub.setResult("avg(salary)");
> // The null value indicates that we're not overriding the candidates
> for the subquery
> // and thus it uses the entire extent of Employee
> q.declareVariables("float averageSalary(null)", sub);
>
>
>> Select employees named Joe who make more than the average of all
>> employees?
>
> Single-string JDOQL:
> SELECT FROM Employee WHERE this.name == 'Joe' && this.salary > (SELECT
> AVG(e.salary) FROM Employee e)
>
> Query API:
> Query q = pm.newQuery(Employee.class);
> q.setFilter("this.name == 'Joe' && this.salary > averageSalary");
>
> // This subquery generically defines "the average of all employeees"
> Query sub = pm.newQuery(Employee.class);
> sub.setResult("avg(salary)");
>
> // Note we could have reused the query instance from the previous
> example.
> q.declareVariables("float averageSalary(null)", sub);
>
>>
>> Select employees named Joe who make more than the average of all
>> employees at the same pay scale?
>
> Single-string JDOQL:
> SELECT FROM Employee WHERE this.name == 'Joe' && this.salary >
> (SELECT AVG(e.salary) FROM Employee e WHERE e.payScale ==
> this.payScale)
>
> Query API:
> Query q = pm.newQuery(Employee.class);
> q.setFilter("this.name == 'Joe' && this.salary > averageSalary");
>
> // Note that this is the same subquery instance as the previous pay
> scale example
> Query sub = pm.newQuery(Employee.class);
> sub.setFilter("payScale == ps");
> sub.declareParameters("PayScale ps");
> sub.setResult("avg(salary)");
>
> q.declareVariables("float averageSalary(null)", sub);
>
>> Select employees who make more than the average of all employees at
>> the same pay scale?
>
> Single-string JDOQL:
> SELECT FROM Employee WHERE this.salary > (SELECT AVG(e.salary) FROM
> Employee e WHERE e.payScale == this.payScale)
>
> Query API:
> Query q = pm.newQuery(Employee.class);
> q.setFilter("this.salary > averageSalary");
>
> // Same again
> Query sub = pm.newQuery(Employee.class);
> sub.setFilter("ps == this.payScale");
> sub.declareParameters("PayScale ps");
>
> q.declareVariables("float averageSalary(null, this.payScale)", sub);
>
> I'd like to hear other ideas for the exact syntax, but what do you
> think of the general concept?
>
> Wes
>
>
> Michael Bouschen wrote:
>
>> Hi Craig,
>>
>> below I try summarize my understanding of what needs to be added in
>> order to support subqueries:
>>
>> - Adding support for defining an alias for the elements of the
>> candidate collection. If there is no explicit alias defined, 'this'
>> is the default. This allows to disambiguate the candidate collection
>> elements from the outer query and the subquery. In single-string
>> JDOQL the alias follows the candidate class name in the FROM clause:
>> 'FROM Employee e'. For the Query API I propose to add a new
>> overloaded method Query.setClass(Class candidateClass, String alias).
>> - The variable declaration supports adding an initializer expression:
>> q.declareVariables("type name = expr"). This allows using a subquery
>> to define the value of a variable.
>> - The FROM clause of the subquery allows a collection relationship
>> field or a collection variable as candidate collection.
>> - Subquery enclosed in parenthesis is a regular expression and can
>> can appear everywhere as long as the typing is correct. So some
>> subqueries might need to include a UNIQUE clause such that the typing
>> is correct, e.g. if the result of a subquery is compared with a
>> regular field:
>> ... WHERE this.salary > (SELECT UNIQUE e.salary FROM Employee e
>> WHERE e.pk == param)
>>
>>> Hi Michael,
>>>
>>> I came up with some more queries that I'd like to discuss as we
>>> consider how to do subqueries. I haven't found a use case for
>>> subqueries that themselves contain subqueries...
>>
>> Below you find my ideas for the single-string and Query API JDOQL for
>> the queries you mentioned.
>>
>>>
>>> Select employees who make more than the average of their department
>>> employees?
>>
>> Single-string JDOQL:
>> SELECT FROM Employee WHERE this.salary > (SELECT AVG(e.salary) FROM
>> this.department.employees e)
>> Query API:
>> Query q = pm.newQuery(Employee.class);
>> q.setFilter("this.salary > averageSalary");
>> q.declareVariables("float averageSalary = SELECT AVG(e.salary) FROM
>> this.department.employees e");
>>
>>> Select employees who make more than the average of the employees in
>>> their department at the same pay scale?
>>
>> Single-string JDOQL:
>> SELECT FROM Employee WHERE this.salary >
>> (SELECT AVG(e.salary) FROM this.department.employees e WHERE
>> e.payScale == this.payScale)
>>
>> Query API:
>> Query q = pm.newQuery(Employee.class);
>> q.setFilter("this.salary > averageSalary");
>> q.declareVariables("float averageSalary = SELECT AVG(e.salary) FROM
>> this.department.employees e WHERE e.payScale == this.payScale");
>>
>>> Select employees who make more than the average of all employees?
>>
>> Single-string JDOQL:
>> SELECT FROM Employee WHERE this.salary > (SELECT AVG(e.salary) FROM
>> Employee e)
>> SELECT FROM Employee WHERE this.salary > (SELECT AVG(this.salary)
>> FROM Employee)
>>
>> Query API:
>> Query q = pm.newQuery(Employee.class);
>> q.setFilter("this.salary > averageSalary");
>> q.declareVariables("float averageSalary = SELECT AVG(this.salary)
>> FROM Employee");
>>
>>> Select employees named Joe who make more than the average of all
>>> employees?
>>
>> Single-string JDOQL:
>> SELECT FROM Employee WHERE this.name == 'Joe' && this.salary >
>> (SELECT AVG(e.salary) FROM Employee e)
>>
>> Query API:
>> Query q = pm.newQuery(Employee.class);
>> q.setFilter("this.name == 'Joe' && this.salary > averageSalary");
>> q.declareVariables("float averageSalary = SELECT AVG(this.salary)
>> FROM Employee");
>>
>>>
>>> Select employees named Joe who make more than the average of all
>>> employees at the same pay scale?
>>
>> Single-string JDOQL:
>> SELECT FROM Employee WHERE this.name == 'Joe' && this.salary >
>> (SELECT AVG(e.salary) FROM Employee e WHERE e.payScale ==
>> this.payScale)
>>
>> Query API:
>> Query q = pm.newQuery(Employee.class);
>> q.setFilter("this.name == 'Joe' && this.salary > averageSalary");
>> q.declareVariables("float averageSalary = SELECT AVG(e.salary) FROM
>> Employee e WHERE e.payScale == this.payScale");
>>
>>> Select employees who make more than the average of all employees at
>>> the same pay scale?
>>
>> Single-string JDOQL:
>> SELECT FROM Employee WHERE this.salary > (SELECT AVG(e.salary) FROM
>> Employee e WHERE e.payScale == this.payScale)
>>
>> Query API:
>> Query q = pm.newQuery(Employee.class);
>> q.setFilter("this.salary > averageSalary");
>> q.declareVariables("float averageSalary = SELECT AVG(e.salary) FROM
>> Employee e WHERE e.payScale == this.payScale");
>>
>> Regards Michael
>>
>>>
>>> Craig
>>>
>>> On Jun 22, 2006, at 3:18 PM, Michael Bouschen wrote:
>>>
>>>> Hi,
>>>>
>>>> I agree to what Craig mentioned in the JDO TCK minutes: method
>>>> addSubquery separates the outer from the subquery. So it looks like
>>>> that this approach cannot support correlated subqueries where the
>>>> subquery iterates a collection field defined in the outer query. My
>>>> preference is using variables declaration to support subqueries
>>>> even in the Query API case.
>>>>
>>>> A query selecting employees earning more than the average salary
>>>> could be:
>>>> SELECT FROM Employee WHERE this.salary > averageSalary
>>>> VARIABLES float averageSalary = SELECT AVG(this.salary) FROM
>>>> Employee
>>>>
>>>> But I still have problems with a correlated subquery. I'm looking
>>>> for a query selecting employees earning more than the average
>>>> salary of their department. The difference to the query above is
>>>> that the candidates collection of the subquery: the employees
>>>> collection of the department of the current employee and not the
>>>> entire Employee extent. Here are some ideas to stimulate the
>>>> discussion, but I'm not quite happy with these.
>>>> (1) Use a collection variable in the FROM clause of the subquery:
>>>> SELECT FROM Employee WHERE this.salary > averageSalary
>>>> VARIABLES Collection emps = this.department.employees;
>>>> float averageSalary = SELECT AVG(this.salary) FROM emps
>>>> One issue is that 'this' is ambiguous: I would think that 'this'
>>>> always refers to an instance of the inner most scope. But this
>>>> means the subquery cannot directly access a field of the outer
>>>> query. So the outer query declares a variable emps that may be used
>>>> in the inner query. Maybe we need a special syntax to access the
>>>> 'this' from the outer query.
>>>>
>>>> (2) The second form does not define any FROM clause, instead it
>>>> uses a variable bound to a collection field of the outer query:
>>>> SELECT FROM Employee WHERE this.salary > averageSalary
>>>> VARIABLES Employee e;
>>>> float averageSalary = SELECT AVG(e.salary) WHERE
>>>> this.department.employees.contains(e)
>>>> Since the subquery does not have a FROM clause, 'this' refers to
>>>> the current Employee from the outer query.
>>>>
>>>> BTW, this is the query as Java Persistence API Query (formerly EJB
>>>> QL):
>>>> SELECT e FROM Employee e JOIN e.department d
>>>> WHERE e.salary > (SELECT AVG(e2.salary) FROM d.employees e2)
>>>>
>>>> About the open issue: using the assignment operator ("=") versus
>>>> the JDOQL keyword "AS": I vote for the assignment operator. The
>>>> "AS" keyword is used in the result expression of the form 'expr AS
>>>> name'. In a variable declaration it would have the form 'type name
>>>> AS expr'. It might be confusing that the variable declaration swaps
>>>> the order, because here the expr is right of the keyword, where it
>>>> is on the other side in the result expression.
>>>>
>>>> Regards Michael
>>>>
>>>>> Hi everyone,
>>>>>
>>>>> Here are 2 proposals discussed in the Fri 9 Jun JDO conference call
>>>>> regarding support for subqueries in JDOQL, including single-string
>>>>> and Query
>>>>> API enhancements, inspired by JPOX's proposed enhancement,
>>>>> documented in
>>>>> JPOX JIRA issue CORE-2861
>>>>> (http://www.jpox.org/servlet/jira/browse/CORE-2861). The string
>>>>> and API
>>>>> enhancement proposals described here are designed to be used
>>>>> hand-in-hand,
>>>>> as the folks on the call wanted to continue to provide compatible
>>>>> string-based and API-based usages. In a nutshell, both proposals
>>>>> hinge on
>>>>> the use of the exising facility to declare JDOQL variables (not JDOQL
>>>>> parameters) to bind subqueries to superqueries.
>>>>>
>>>>> Please read thoroughly, consider, and comment.
>>>>>
>>>>> --matthew
>>>>>
>>>>> PS: Martin was on the hook to describe an alternative proposal
>>>>> based on a
>>>>> future object pattern. See separate proposal from him.
>>>>>
>>>>> <proposals>
>>>>>
>>>>> Query API support
>>>>> =================
>>>>> Proposal: Introduce new method Query.addSubquery(String
>>>>> variableName, Query
>>>>> subquery)
>>>>>
>>>>> This proposal entails utilizing the current Query API's
>>>>> declareVariables
>>>>> facility and adding a method Query.addSubquery(String,Query) to
>>>>> support
>>>>> subqueries. Essentially, a subquery is bound to a superquery via
>>>>> a variable
>>>>> declared for the superquery. The implementation handles coercing the
>>>>> subquery's result into the type of the variable(s) declared in the
>>>>> superquery. Queries can be nested to arbitrary levels.
>>>>>
>>>>> Example 1A: Find people with above average income
>>>>>
>>>>> Query superquery1a = pm.newQuery("SELECT FROM Person WHERE income >
>>>>> averageIncome");
>>>>> superquery1a.declareVariables("BigDecimal averageIncome;");
>>>>>
>>>>> Query subquery1a = pm.newQuery("SELECT avg(income) FROM Person");
>>>>> superquery1a.addSubquery("averageIncome", subquery1a); // binds
>>>>> subquery to
>>>>> superquery
>>>>>
>>>>>
>>>>> Example 2A: Find average income of fathers using subquery
>>>>>
>>>>> Query superquery2a = pm.newQuery("SELECT avg(income) FROM fathers");
>>>>> // in next line, Collection<Parent> derived from subquery
>>>>> superquery2a.declareVariables("Collection fathers;");
>>>>>
>>>>> Query subquery2a = pm.newQuery("SELECT FROM Person WHERE gender ==
>>>>> 'M' &&
>>>>> children.size() > 0");
>>>>> superquery2a.addSubquery("fathers", subquery2a); // binds subquery to
>>>>> superquery
>>>>>
>>>>>
>>>>> Example 3A: Find average income of fathers using a single Query
>>>>> instance
>>>>> Note: this example's usage is required if the grammar
>>>>> specification of the
>>>>> variables clause remains the same (as it currently is) in the API
>>>>> and string
>>>>> forms (see JDO 2.0 spec sections 14.6.5 & 14.6.13).
>>>>>
>>>>> Query superquery3a = pm.newQuery("SELECT avg(income) FROM fathers");
>>>>> // in next line, Collection<Parent> derived from subquery
>>>>> superquery3a.declareVariables(
>>>>> "Collection fathers = SELECT FROM Person WHERE gender == 'M' &&
>>>>> children.size() > 0;");
>>>>>
>>>>> Pros:
>>>>> * Maintains backward compatibility.
>>>>> * Enhances performance by allowing for the deferral of query
>>>>> execution
>>>>> until entire query with subqueries is defined. Current Query API
>>>>> support
>>>>> requires the execution of the subquery, then execution of the
>>>>> superquery;
>>>>> current JDOQL string spec doesn't allow for subqueries at all.
>>>>> * Grammar of the variables clause undergoes the same
>>>>> enhancements in both
>>>>> the single-string and the API.
>>>>> * Compatible with single-string enhancement proposal below
>>>>>
>>>>> Cons:
>>>>> * Type coercion becomes more complicated than just autoboxing.
>>>>> * Requires that variables may always have to be explicitly defined.
>>>>> * Possibility that type of candidate collection of superquery
>>>>> must be
>>>>> derived (see example 2A above), or may not be known.
>>>>> * Possibility of using variables in place of both parameters and
>>>>> candidate
>>>>> collections.
>>>>>
>>>>>
>>>>>
>>>>> Single-string proposal
>>>>> ======================
>>>>> Proposal: Use the existing VARIABLES JDOQL keyword in order to be
>>>>> compatible with the Query API proposal above.
>>>>>
>>>>> This proposal is very similar to JPOX JIRA CORE-2861
>>>>> (http://www.jpox.org/servlet/jira/browse/CORE-2861), which
>>>>> proposes the
>>>>> introduction of a new JDOQL keyword "WITH" to introduce typed and
>>>>> named
>>>>> subquery results. To make this more compatible with the Query API
>>>>> proposal
>>>>> above and to avoid the need to introduce a new keyword to JDOQL, the
>>>>> existing JDOQL keyword "VARIABLES" would be used to introduce
>>>>> typed and
>>>>> named subqueries, except that the variable(s) would be intialized
>>>>> via the
>>>>> assignment operator, "=", or the "AS" keyword (TBD) at declaration
>>>>> time with
>>>>> a valid JDOQL expression. Variables would continue to be
>>>>> semicolon-delimited. Additionally, Query.toString(), for queries
>>>>> that
>>>>> employ subqueries, returns JDOQL strings that use this syntax.
>>>>>
>>>>> Example 1S: Find people with above average income using subquery
>>>>> (similar
>>>>> to example 1A)
>>>>>
>>>>> SELECT FROM Person WHERE income > averageIncome
>>>>> VARIABLES float averageIncome =
>>>>> SELECT avg(income) FROM Person;
>>>>>
>>>>> Example 2S: Find average income of fathers using a subquery
>>>>> (similar to
>>>>> example 2A)
>>>>>
>>>>> SELECT avg(income) FROM parents
>>>>> VARIABLES Collection parents =
>>>>> SELECT FROM Parent WHERE gender == 'M' && children.size() > 0;
>>>>>
>>>>> Pros:
>>>>> * Continues to use existing JDOQL keywords.
>>>>> * Grammar of the variables clause undergoes the same
>>>>> enhancements in both
>>>>> the single-string and the API.
>>>>> * Compatible with Query API proposal above.
>>>>>
>>>>> Cons:
>>>>> * More verbose than introducing "WITH" keyword. Implicit, unnamed
>>>>> variables would not be supported, unless the following syntax were
>>>>> supported
>>>>> (from example 2S), where the tokens "VARIABLES Collection parents" is
>>>>> implied.
>>>>> SELECT avg(income) FROM parents =
>>>>> SELECT FROM Parent WHERE gender == 'M' && children.size() > 0;
>>>>> This syntax is admittedly less verbose and more like SQL
>>>>> subqueries, but
>>>>> leaves open the typing and naming of the implicit, unnamed variables.
>>>>> * Possibility of using variables in place of both parameters and
>>>>> candidate
>>>>> collections.
>>>>>
>>>>>
>>>>> Open issues
>>>>> ===========
>>>>>
>>>>> * Can type derivation & coercion of JDOQL variables be performed
>>>>> in all
>>>>> cases?
>>>>> * These proposals use the assignment operator ("="). Should we use
>>>>> assignment via the JDOQL keyword "AS" instead or in addition to the
>>>>> assignment operator?
>>>>> * This proposal requires that JDOQL variables be allowed to
>>>>> substitute for
>>>>> both JDOQL parameters and candidate collections. Should this be
>>>>> allowed?
>>>>>
>>>>>
>>>>> </proposals>
>>>>>
>>>>>
>>>>>> -----Original Message-----
>>>>>> From: Erik Bengtson [mailto:erik@jpox.org] Sent: Wednesday, June
>>>>>> 07, 2006 7:57 AM
>>>>>> To: jdo-experts-ext@sun.com; jdo-dev@db.apache.org
>>>>>> Subject: any plans to support subqueries with similar concept as
>>>>>> in sql
>>>>>>
>>>>>> Hi,
>>>>>>
>>>>>> We need the ability to work with multiple sets in the same query.
>>>>>> It includes
>>>>>> performing operations between sets, numeric functions like
>>>>>> average or sum,
>>>>>> etc..
>>>>>>
>>>>>> In JPOX it will implemented as exemplified here
>>>>>> http://www.jpox.org/servlet/jira/browse/CORE-2861
>>>>>>
>>>>>> Sadly, JDOQL 2 is not capable to compete with JPQL in this aspect.
>>>>>>
>>>>>> Regardless the above issue, are there plans to expand the JDOQL
>>>>>> or even JDO 2 in
>>>>>> general based on new user requests/requirements?
>>>>>>
>>>>>> Regards,
>>>>>>
>>>>>> Erik Bengtson
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>> --Michael Bouschen Tech@Spree Engineering GmbH
>>>> mailto:mbo.tech@spree.de http://www.tech.spree.de/
>>>> Tel.:++49/30/235 520-33 Buelowstr. 66
>>>> Fax.:++49/30/2175 2012 D-10783 Berlin
>>>
>>>
>>> Craig Russell
>>> Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
>>> 408 276-5638 mailto:Craig.Russell@sun.com
>>> P.S. A good JDO? O, Gasp!
>>>
>>
>>
--
Michael Bouschen Tech@Spree Engineering GmbH
mailto:mbo.tech@spree.de http://www.tech.spree.de/
Tel.:++49/30/235 520-33 Buelowstr. 66
Fax.:++49/30/2175 2012 D-10783 Berlin
Re: JDOQL Subquery proposals
Posted by Wes Biggs <we...@tralfamadore.com>.
I'm ridiculously late in responding to this thread but if I may be so
bold, I'll make a further suggestion.
I like everything about the proposed approach except the requirement
that subquery definitions must resort to single-string JDOQL syntax,
even when using the API-based methods. I think this introduces
asymmetry and discourages reuse and modularity.
I would really like to see the ability to map variables to (sub)Query
objects. There are two new capabilities introduced in the SSJDOQL
version, and my opinion is that the API should match these feature by
feature. The two features are:
(1) The ability for a subquery to use an expression defined on the outer
query as its candidate set.
(2) The ability for a subquery to use expressions defined on the outer
query as parameters.
Therefore, for parity, we need an API-based way to declare these
mappings, so that subqueries can be assigned both their candidate
collections and their parameters dynamically.
I propose an overloaded version of declareVariables that allows mapping
variable names used in the outer query to (sub)Query instances that are
correlated with candidates and parameters.
void declareVariables(String variableList, Query... subquery)
The variable declaration syntax should be extended to allow
parameterized variables of the form
"name(candidateExpression[,parameterExpression...])". "name" defines a
variable name in the query; "candidateExpression" defines an expression
(rooted in the namespace of the outer query) for the candidate extent to
be bound to the subquery, where "null" signifies that the subquery
candidate set is not being limited. "parameterExpression" identifies
dynamic values for parameters declared by the subquery, again rooted in
the namespace of the outer query doing the binding.
To touch up Michael's examples:
Select employees who make more than the average of their department
employees?
Single-string JDOQL:
SELECT FROM Employee WHERE this.salary > (SELECT AVG(e.salary) FROM
this.department.employees e)
Query API:
Query q = pm.newQuery(Employee.class);
q.setFilter("this.salary > averageSalary");
// Subquery definition is generic: for a given set of Employees, return
the average salary
Query sub = pm.newQuery(Employee.class);
sub.setResult("avg(salary)");
// Bind the subquery to the master query by identifying the candidate set
q.declareVariables("float averageSalary(this.department.employees)", sub);
> Select employees who make more than the average of the employees in
> their department at the same pay scale?
Single-string JDOQL:
SELECT FROM Employee WHERE this.salary >
(SELECT AVG(e.salary) FROM this.department.employees e WHERE
e.payScale == this.payScale)
Query API:
Query q = pm.newQuery(Employee.class);
q.setFilter("this.salary > averageSalary");
// This subquery generically defines the average salary of a set of
Employees at a given PayScale
Query sub = pm.newQuery(Employee.class);
sub.setFilter("this.payScale == ps");
sub.declareParameters("PayScale ps");
sub.setResult("avg(salary)");
// Bind both a candidate set and the payScale parameter.
q.declareVariables("float averageSalary(this.department.employees,
this.payScale)", sub);
> Select employees who make more than the average of all employees?
Single-string JDOQL:
SELECT FROM Employee WHERE this.salary > (SELECT AVG(e.salary) FROM
Employee e)
SELECT FROM Employee WHERE this.salary > (SELECT AVG(this.salary) FROM
Employee)
Query API:
Query q = pm.newQuery(Employee.class);
q.setFilter("this.salary > averageSalary");
Query sub = pm.newQuery(Employee.class);
sub.setResult("avg(salary)");
// The null value indicates that we're not overriding the candidates
for the subquery
// and thus it uses the entire extent of Employee
q.declareVariables("float averageSalary(null)", sub);
> Select employees named Joe who make more than the average of all
> employees?
Single-string JDOQL:
SELECT FROM Employee WHERE this.name == 'Joe' && this.salary > (SELECT
AVG(e.salary) FROM Employee e)
Query API:
Query q = pm.newQuery(Employee.class);
q.setFilter("this.name == 'Joe' && this.salary > averageSalary");
// This subquery generically defines "the average of all employeees"
Query sub = pm.newQuery(Employee.class);
sub.setResult("avg(salary)");
// Note we could have reused the query instance from the previous example.
q.declareVariables("float averageSalary(null)", sub);
>
> Select employees named Joe who make more than the average of all
> employees at the same pay scale?
Single-string JDOQL:
SELECT FROM Employee WHERE this.name == 'Joe' && this.salary >
(SELECT AVG(e.salary) FROM Employee e WHERE e.payScale == this.payScale)
Query API:
Query q = pm.newQuery(Employee.class);
q.setFilter("this.name == 'Joe' && this.salary > averageSalary");
// Note that this is the same subquery instance as the previous pay
scale example
Query sub = pm.newQuery(Employee.class);
sub.setFilter("payScale == ps");
sub.declareParameters("PayScale ps");
sub.setResult("avg(salary)");
q.declareVariables("float averageSalary(null)", sub);
> Select employees who make more than the average of all employees at
> the same pay scale?
Single-string JDOQL:
SELECT FROM Employee WHERE this.salary > (SELECT AVG(e.salary) FROM
Employee e WHERE e.payScale == this.payScale)
Query API:
Query q = pm.newQuery(Employee.class);
q.setFilter("this.salary > averageSalary");
// Same again
Query sub = pm.newQuery(Employee.class);
sub.setFilter("ps == this.payScale");
sub.declareParameters("PayScale ps");
q.declareVariables("float averageSalary(null, this.payScale)", sub);
I'd like to hear other ideas for the exact syntax, but what do you think
of the general concept?
Wes
Michael Bouschen wrote:
> Hi Craig,
>
> below I try summarize my understanding of what needs to be added in
> order to support subqueries:
>
> - Adding support for defining an alias for the elements of the
> candidate collection. If there is no explicit alias defined, 'this' is
> the default. This allows to disambiguate the candidate collection
> elements from the outer query and the subquery. In single-string JDOQL
> the alias follows the candidate class name in the FROM clause: 'FROM
> Employee e'. For the Query API I propose to add a new overloaded
> method Query.setClass(Class candidateClass, String alias).
> - The variable declaration supports adding an initializer expression:
> q.declareVariables("type name = expr"). This allows using a subquery
> to define the value of a variable.
> - The FROM clause of the subquery allows a collection relationship
> field or a collection variable as candidate collection.
> - Subquery enclosed in parenthesis is a regular expression and can can
> appear everywhere as long as the typing is correct. So some subqueries
> might need to include a UNIQUE clause such that the typing is correct,
> e.g. if the result of a subquery is compared with a regular field:
> ... WHERE this.salary > (SELECT UNIQUE e.salary FROM Employee e WHERE
> e.pk == param)
>
>> Hi Michael,
>>
>> I came up with some more queries that I'd like to discuss as we
>> consider how to do subqueries. I haven't found a use case for
>> subqueries that themselves contain subqueries...
>
> Below you find my ideas for the single-string and Query API JDOQL for
> the queries you mentioned.
>
>>
>> Select employees who make more than the average of their department
>> employees?
>
> Single-string JDOQL:
> SELECT FROM Employee WHERE this.salary > (SELECT AVG(e.salary) FROM
> this.department.employees e)
> Query API:
> Query q = pm.newQuery(Employee.class);
> q.setFilter("this.salary > averageSalary");
> q.declareVariables("float averageSalary = SELECT AVG(e.salary) FROM
> this.department.employees e");
>
>> Select employees who make more than the average of the employees in
>> their department at the same pay scale?
>
> Single-string JDOQL:
> SELECT FROM Employee WHERE this.salary >
> (SELECT AVG(e.salary) FROM this.department.employees e WHERE
> e.payScale == this.payScale)
>
> Query API:
> Query q = pm.newQuery(Employee.class);
> q.setFilter("this.salary > averageSalary");
> q.declareVariables("float averageSalary = SELECT AVG(e.salary) FROM
> this.department.employees e WHERE e.payScale == this.payScale");
>
>> Select employees who make more than the average of all employees?
>
> Single-string JDOQL:
> SELECT FROM Employee WHERE this.salary > (SELECT AVG(e.salary) FROM
> Employee e)
> SELECT FROM Employee WHERE this.salary > (SELECT AVG(this.salary)
> FROM Employee)
>
> Query API:
> Query q = pm.newQuery(Employee.class);
> q.setFilter("this.salary > averageSalary");
> q.declareVariables("float averageSalary = SELECT AVG(this.salary)
> FROM Employee");
>
>> Select employees named Joe who make more than the average of all
>> employees?
>
> Single-string JDOQL:
> SELECT FROM Employee WHERE this.name == 'Joe' && this.salary >
> (SELECT AVG(e.salary) FROM Employee e)
>
> Query API:
> Query q = pm.newQuery(Employee.class);
> q.setFilter("this.name == 'Joe' && this.salary > averageSalary");
> q.declareVariables("float averageSalary = SELECT AVG(this.salary)
> FROM Employee");
>
>>
>> Select employees named Joe who make more than the average of all
>> employees at the same pay scale?
>
> Single-string JDOQL:
> SELECT FROM Employee WHERE this.name == 'Joe' && this.salary >
> (SELECT AVG(e.salary) FROM Employee e WHERE e.payScale ==
> this.payScale)
>
> Query API:
> Query q = pm.newQuery(Employee.class);
> q.setFilter("this.name == 'Joe' && this.salary > averageSalary");
> q.declareVariables("float averageSalary = SELECT AVG(e.salary) FROM
> Employee e WHERE e.payScale == this.payScale");
>
>> Select employees who make more than the average of all employees at
>> the same pay scale?
>
> Single-string JDOQL:
> SELECT FROM Employee WHERE this.salary > (SELECT AVG(e.salary) FROM
> Employee e WHERE e.payScale == this.payScale)
>
> Query API:
> Query q = pm.newQuery(Employee.class);
> q.setFilter("this.salary > averageSalary");
> q.declareVariables("float averageSalary = SELECT AVG(e.salary) FROM
> Employee e WHERE e.payScale == this.payScale");
>
> Regards Michael
>
>>
>> Craig
>>
>> On Jun 22, 2006, at 3:18 PM, Michael Bouschen wrote:
>>
>>> Hi,
>>>
>>> I agree to what Craig mentioned in the JDO TCK minutes: method
>>> addSubquery separates the outer from the subquery. So it looks like
>>> that this approach cannot support correlated subqueries where the
>>> subquery iterates a collection field defined in the outer query. My
>>> preference is using variables declaration to support subqueries even
>>> in the Query API case.
>>>
>>> A query selecting employees earning more than the average salary
>>> could be:
>>> SELECT FROM Employee WHERE this.salary > averageSalary
>>> VARIABLES float averageSalary = SELECT AVG(this.salary) FROM Employee
>>>
>>> But I still have problems with a correlated subquery. I'm looking
>>> for a query selecting employees earning more than the average salary
>>> of their department. The difference to the query above is that the
>>> candidates collection of the subquery: the employees collection of
>>> the department of the current employee and not the entire Employee
>>> extent. Here are some ideas to stimulate the discussion, but I'm not
>>> quite happy with these.
>>> (1) Use a collection variable in the FROM clause of the subquery:
>>> SELECT FROM Employee WHERE this.salary > averageSalary
>>> VARIABLES Collection emps = this.department.employees;
>>> float averageSalary = SELECT AVG(this.salary) FROM emps
>>> One issue is that 'this' is ambiguous: I would think that 'this'
>>> always refers to an instance of the inner most scope. But this means
>>> the subquery cannot directly access a field of the outer query. So
>>> the outer query declares a variable emps that may be used in the
>>> inner query. Maybe we need a special syntax to access the 'this'
>>> from the outer query.
>>>
>>> (2) The second form does not define any FROM clause, instead it uses
>>> a variable bound to a collection field of the outer query:
>>> SELECT FROM Employee WHERE this.salary > averageSalary
>>> VARIABLES Employee e;
>>> float averageSalary = SELECT AVG(e.salary) WHERE
>>> this.department.employees.contains(e)
>>> Since the subquery does not have a FROM clause, 'this' refers to the
>>> current Employee from the outer query.
>>>
>>> BTW, this is the query as Java Persistence API Query (formerly EJB QL):
>>> SELECT e FROM Employee e JOIN e.department d
>>> WHERE e.salary > (SELECT AVG(e2.salary) FROM d.employees e2)
>>>
>>> About the open issue: using the assignment operator ("=") versus the
>>> JDOQL keyword "AS": I vote for the assignment operator. The "AS"
>>> keyword is used in the result expression of the form 'expr AS name'.
>>> In a variable declaration it would have the form 'type name AS
>>> expr'. It might be confusing that the variable declaration swaps the
>>> order, because here the expr is right of the keyword, where it is on
>>> the other side in the result expression.
>>>
>>> Regards Michael
>>>
>>>> Hi everyone,
>>>>
>>>> Here are 2 proposals discussed in the Fri 9 Jun JDO conference call
>>>> regarding support for subqueries in JDOQL, including single-string
>>>> and Query
>>>> API enhancements, inspired by JPOX's proposed enhancement,
>>>> documented in
>>>> JPOX JIRA issue CORE-2861
>>>> (http://www.jpox.org/servlet/jira/browse/CORE-2861). The string
>>>> and API
>>>> enhancement proposals described here are designed to be used
>>>> hand-in-hand,
>>>> as the folks on the call wanted to continue to provide compatible
>>>> string-based and API-based usages. In a nutshell, both proposals
>>>> hinge on
>>>> the use of the exising facility to declare JDOQL variables (not JDOQL
>>>> parameters) to bind subqueries to superqueries.
>>>>
>>>> Please read thoroughly, consider, and comment.
>>>>
>>>> --matthew
>>>>
>>>> PS: Martin was on the hook to describe an alternative proposal
>>>> based on a
>>>> future object pattern. See separate proposal from him.
>>>>
>>>> <proposals>
>>>>
>>>> Query API support
>>>> =================
>>>> Proposal: Introduce new method Query.addSubquery(String
>>>> variableName, Query
>>>> subquery)
>>>>
>>>> This proposal entails utilizing the current Query API's
>>>> declareVariables
>>>> facility and adding a method Query.addSubquery(String,Query) to
>>>> support
>>>> subqueries. Essentially, a subquery is bound to a superquery via a
>>>> variable
>>>> declared for the superquery. The implementation handles coercing the
>>>> subquery's result into the type of the variable(s) declared in the
>>>> superquery. Queries can be nested to arbitrary levels.
>>>>
>>>> Example 1A: Find people with above average income
>>>>
>>>> Query superquery1a = pm.newQuery("SELECT FROM Person WHERE income >
>>>> averageIncome");
>>>> superquery1a.declareVariables("BigDecimal averageIncome;");
>>>>
>>>> Query subquery1a = pm.newQuery("SELECT avg(income) FROM Person");
>>>> superquery1a.addSubquery("averageIncome", subquery1a); // binds
>>>> subquery to
>>>> superquery
>>>>
>>>>
>>>> Example 2A: Find average income of fathers using subquery
>>>>
>>>> Query superquery2a = pm.newQuery("SELECT avg(income) FROM fathers");
>>>> // in next line, Collection<Parent> derived from subquery
>>>> superquery2a.declareVariables("Collection fathers;");
>>>>
>>>> Query subquery2a = pm.newQuery("SELECT FROM Person WHERE gender ==
>>>> 'M' &&
>>>> children.size() > 0");
>>>> superquery2a.addSubquery("fathers", subquery2a); // binds subquery to
>>>> superquery
>>>>
>>>>
>>>> Example 3A: Find average income of fathers using a single Query
>>>> instance
>>>> Note: this example's usage is required if the grammar
>>>> specification of the
>>>> variables clause remains the same (as it currently is) in the API
>>>> and string
>>>> forms (see JDO 2.0 spec sections 14.6.5 & 14.6.13).
>>>>
>>>> Query superquery3a = pm.newQuery("SELECT avg(income) FROM fathers");
>>>> // in next line, Collection<Parent> derived from subquery
>>>> superquery3a.declareVariables(
>>>> "Collection fathers = SELECT FROM Person WHERE gender == 'M' &&
>>>> children.size() > 0;");
>>>>
>>>> Pros:
>>>> * Maintains backward compatibility.
>>>> * Enhances performance by allowing for the deferral of query
>>>> execution
>>>> until entire query with subqueries is defined. Current Query API
>>>> support
>>>> requires the execution of the subquery, then execution of the
>>>> superquery;
>>>> current JDOQL string spec doesn't allow for subqueries at all.
>>>> * Grammar of the variables clause undergoes the same enhancements
>>>> in both
>>>> the single-string and the API.
>>>> * Compatible with single-string enhancement proposal below
>>>>
>>>> Cons:
>>>> * Type coercion becomes more complicated than just autoboxing.
>>>> * Requires that variables may always have to be explicitly defined.
>>>> * Possibility that type of candidate collection of superquery
>>>> must be
>>>> derived (see example 2A above), or may not be known.
>>>> * Possibility of using variables in place of both parameters and
>>>> candidate
>>>> collections.
>>>>
>>>>
>>>>
>>>> Single-string proposal
>>>> ======================
>>>> Proposal: Use the existing VARIABLES JDOQL keyword in order to be
>>>> compatible with the Query API proposal above.
>>>>
>>>> This proposal is very similar to JPOX JIRA CORE-2861
>>>> (http://www.jpox.org/servlet/jira/browse/CORE-2861), which proposes
>>>> the
>>>> introduction of a new JDOQL keyword "WITH" to introduce typed and
>>>> named
>>>> subquery results. To make this more compatible with the Query API
>>>> proposal
>>>> above and to avoid the need to introduce a new keyword to JDOQL, the
>>>> existing JDOQL keyword "VARIABLES" would be used to introduce typed
>>>> and
>>>> named subqueries, except that the variable(s) would be intialized
>>>> via the
>>>> assignment operator, "=", or the "AS" keyword (TBD) at declaration
>>>> time with
>>>> a valid JDOQL expression. Variables would continue to be
>>>> semicolon-delimited. Additionally, Query.toString(), for queries that
>>>> employ subqueries, returns JDOQL strings that use this syntax.
>>>>
>>>> Example 1S: Find people with above average income using subquery
>>>> (similar
>>>> to example 1A)
>>>>
>>>> SELECT FROM Person WHERE income > averageIncome
>>>> VARIABLES float averageIncome =
>>>> SELECT avg(income) FROM Person;
>>>>
>>>> Example 2S: Find average income of fathers using a subquery
>>>> (similar to
>>>> example 2A)
>>>>
>>>> SELECT avg(income) FROM parents
>>>> VARIABLES Collection parents =
>>>> SELECT FROM Parent WHERE gender == 'M' && children.size() > 0;
>>>>
>>>> Pros:
>>>> * Continues to use existing JDOQL keywords.
>>>> * Grammar of the variables clause undergoes the same enhancements
>>>> in both
>>>> the single-string and the API.
>>>> * Compatible with Query API proposal above.
>>>>
>>>> Cons:
>>>> * More verbose than introducing "WITH" keyword. Implicit, unnamed
>>>> variables would not be supported, unless the following syntax were
>>>> supported
>>>> (from example 2S), where the tokens "VARIABLES Collection parents" is
>>>> implied.
>>>> SELECT avg(income) FROM parents =
>>>> SELECT FROM Parent WHERE gender == 'M' && children.size() > 0;
>>>> This syntax is admittedly less verbose and more like SQL
>>>> subqueries, but
>>>> leaves open the typing and naming of the implicit, unnamed variables.
>>>> * Possibility of using variables in place of both parameters and
>>>> candidate
>>>> collections.
>>>>
>>>>
>>>> Open issues
>>>> ===========
>>>>
>>>> * Can type derivation & coercion of JDOQL variables be performed in
>>>> all
>>>> cases?
>>>> * These proposals use the assignment operator ("="). Should we use
>>>> assignment via the JDOQL keyword "AS" instead or in addition to the
>>>> assignment operator?
>>>> * This proposal requires that JDOQL variables be allowed to
>>>> substitute for
>>>> both JDOQL parameters and candidate collections. Should this be
>>>> allowed?
>>>>
>>>>
>>>> </proposals>
>>>>
>>>>
>>>>> -----Original Message-----
>>>>> From: Erik Bengtson [mailto:erik@jpox.org] Sent: Wednesday, June
>>>>> 07, 2006 7:57 AM
>>>>> To: jdo-experts-ext@sun.com; jdo-dev@db.apache.org
>>>>> Subject: any plans to support subqueries with similar concept as
>>>>> in sql
>>>>>
>>>>> Hi,
>>>>>
>>>>> We need the ability to work with multiple sets in the same query.
>>>>> It includes
>>>>> performing operations between sets, numeric functions like average
>>>>> or sum,
>>>>> etc..
>>>>>
>>>>> In JPOX it will implemented as exemplified here
>>>>> http://www.jpox.org/servlet/jira/browse/CORE-2861
>>>>>
>>>>> Sadly, JDOQL 2 is not capable to compete with JPQL in this aspect.
>>>>>
>>>>> Regardless the above issue, are there plans to expand the JDOQL or
>>>>> even JDO 2 in
>>>>> general based on new user requests/requirements?
>>>>>
>>>>> Regards,
>>>>>
>>>>> Erik Bengtson
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>> --Michael Bouschen Tech@Spree Engineering GmbH
>>> mailto:mbo.tech@spree.de http://www.tech.spree.de/
>>> Tel.:++49/30/235 520-33 Buelowstr. 66
>>> Fax.:++49/30/2175 2012 D-10783 Berlin
>>
>>
>> Craig Russell
>> Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
>> 408 276-5638 mailto:Craig.Russell@sun.com
>> P.S. A good JDO? O, Gasp!
>>
>
>
Re: JDOQL Subquery proposals
Posted by Michael Bouschen <mb...@spree.de>.
Hi Craig,
below I try summarize my understanding of what needs to be added in
order to support subqueries:
- Adding support for defining an alias for the elements of the candidate
collection. If there is no explicit alias defined, 'this' is the
default. This allows to disambiguate the candidate collection elements
from the outer query and the subquery. In single-string JDOQL the alias
follows the candidate class name in the FROM clause: 'FROM Employee e'.
For the Query API I propose to add a new overloaded method
Query.setClass(Class candidateClass, String alias).
- The variable declaration supports adding an initializer expression:
q.declareVariables("type name = expr"). This allows using a subquery to
define the value of a variable.
- The FROM clause of the subquery allows a collection relationship field
or a collection variable as candidate collection.
- Subquery enclosed in parenthesis is a regular expression and can can
appear everywhere as long as the typing is correct. So some subqueries
might need to include a UNIQUE clause such that the typing is correct,
e.g. if the result of a subquery is compared with a regular field:
... WHERE this.salary > (SELECT UNIQUE e.salary FROM Employee e WHERE
e.pk == param)
> Hi Michael,
>
> I came up with some more queries that I'd like to discuss as we
> consider how to do subqueries. I haven't found a use case for
> subqueries that themselves contain subqueries...
Below you find my ideas for the single-string and Query API JDOQL for
the queries you mentioned.
>
> Select employees who make more than the average of their department
> employees?
Single-string JDOQL:
SELECT FROM Employee WHERE this.salary > (SELECT AVG(e.salary) FROM
this.department.employees e)
Query API:
Query q = pm.newQuery(Employee.class);
q.setFilter("this.salary > averageSalary");
q.declareVariables("float averageSalary = SELECT AVG(e.salary) FROM
this.department.employees e");
> Select employees who make more than the average of the employees in
> their department at the same pay scale?
Single-string JDOQL:
SELECT FROM Employee WHERE this.salary >
(SELECT AVG(e.salary) FROM this.department.employees e WHERE
e.payScale == this.payScale)
Query API:
Query q = pm.newQuery(Employee.class);
q.setFilter("this.salary > averageSalary");
q.declareVariables("float averageSalary = SELECT AVG(e.salary) FROM
this.department.employees e WHERE e.payScale == this.payScale");
> Select employees who make more than the average of all employees?
Single-string JDOQL:
SELECT FROM Employee WHERE this.salary > (SELECT AVG(e.salary) FROM
Employee e)
SELECT FROM Employee WHERE this.salary > (SELECT AVG(this.salary)
FROM Employee)
Query API:
Query q = pm.newQuery(Employee.class);
q.setFilter("this.salary > averageSalary");
q.declareVariables("float averageSalary = SELECT AVG(this.salary) FROM
Employee");
> Select employees named Joe who make more than the average of all
> employees?
Single-string JDOQL:
SELECT FROM Employee WHERE this.name == 'Joe' && this.salary > (SELECT
AVG(e.salary) FROM Employee e)
Query API:
Query q = pm.newQuery(Employee.class);
q.setFilter("this.name == 'Joe' && this.salary > averageSalary");
q.declareVariables("float averageSalary = SELECT AVG(this.salary) FROM
Employee");
>
> Select employees named Joe who make more than the average of all
> employees at the same pay scale?
Single-string JDOQL:
SELECT FROM Employee WHERE this.name == 'Joe' && this.salary >
(SELECT AVG(e.salary) FROM Employee e WHERE e.payScale ==
this.payScale)
Query API:
Query q = pm.newQuery(Employee.class);
q.setFilter("this.name == 'Joe' && this.salary > averageSalary");
q.declareVariables("float averageSalary = SELECT AVG(e.salary) FROM
Employee e WHERE e.payScale == this.payScale");
> Select employees who make more than the average of all employees at
> the same pay scale?
Single-string JDOQL:
SELECT FROM Employee WHERE this.salary > (SELECT AVG(e.salary) FROM
Employee e WHERE e.payScale == this.payScale)
Query API:
Query q = pm.newQuery(Employee.class);
q.setFilter("this.salary > averageSalary");
q.declareVariables("float averageSalary = SELECT AVG(e.salary) FROM
Employee e WHERE e.payScale == this.payScale");
Regards Michael
>
> Craig
>
> On Jun 22, 2006, at 3:18 PM, Michael Bouschen wrote:
>
>> Hi,
>>
>> I agree to what Craig mentioned in the JDO TCK minutes: method
>> addSubquery separates the outer from the subquery. So it looks like
>> that this approach cannot support correlated subqueries where the
>> subquery iterates a collection field defined in the outer query. My
>> preference is using variables declaration to support subqueries even
>> in the Query API case.
>>
>> A query selecting employees earning more than the average salary
>> could be:
>> SELECT FROM Employee WHERE this.salary > averageSalary
>> VARIABLES float averageSalary = SELECT AVG(this.salary) FROM Employee
>>
>> But I still have problems with a correlated subquery. I'm looking for
>> a query selecting employees earning more than the average salary of
>> their department. The difference to the query above is that the
>> candidates collection of the subquery: the employees collection of
>> the department of the current employee and not the entire Employee
>> extent. Here are some ideas to stimulate the discussion, but I'm not
>> quite happy with these.
>> (1) Use a collection variable in the FROM clause of the subquery:
>> SELECT FROM Employee WHERE this.salary > averageSalary
>> VARIABLES Collection emps = this.department.employees;
>> float averageSalary = SELECT AVG(this.salary) FROM emps
>> One issue is that 'this' is ambiguous: I would think that 'this'
>> always refers to an instance of the inner most scope. But this means
>> the subquery cannot directly access a field of the outer query. So
>> the outer query declares a variable emps that may be used in the
>> inner query. Maybe we need a special syntax to access the 'this' from
>> the outer query.
>>
>> (2) The second form does not define any FROM clause, instead it uses
>> a variable bound to a collection field of the outer query:
>> SELECT FROM Employee WHERE this.salary > averageSalary
>> VARIABLES Employee e;
>> float averageSalary = SELECT AVG(e.salary) WHERE
>> this.department.employees.contains(e)
>> Since the subquery does not have a FROM clause, 'this' refers to the
>> current Employee from the outer query.
>>
>> BTW, this is the query as Java Persistence API Query (formerly EJB QL):
>> SELECT e FROM Employee e JOIN e.department d
>> WHERE e.salary > (SELECT AVG(e2.salary) FROM d.employees e2)
>>
>> About the open issue: using the assignment operator ("=") versus the
>> JDOQL keyword "AS": I vote for the assignment operator. The "AS"
>> keyword is used in the result expression of the form 'expr AS name'.
>> In a variable declaration it would have the form 'type name AS expr'.
>> It might be confusing that the variable declaration swaps the order,
>> because here the expr is right of the keyword, where it is on the
>> other side in the result expression.
>>
>> Regards Michael
>>
>>> Hi everyone,
>>>
>>> Here are 2 proposals discussed in the Fri 9 Jun JDO conference call
>>> regarding support for subqueries in JDOQL, including single-string
>>> and Query
>>> API enhancements, inspired by JPOX's proposed enhancement,
>>> documented in
>>> JPOX JIRA issue CORE-2861
>>> (http://www.jpox.org/servlet/jira/browse/CORE-2861). The string and
>>> API
>>> enhancement proposals described here are designed to be used
>>> hand-in-hand,
>>> as the folks on the call wanted to continue to provide compatible
>>> string-based and API-based usages. In a nutshell, both proposals
>>> hinge on
>>> the use of the exising facility to declare JDOQL variables (not JDOQL
>>> parameters) to bind subqueries to superqueries.
>>>
>>> Please read thoroughly, consider, and comment.
>>>
>>> --matthew
>>>
>>> PS: Martin was on the hook to describe an alternative proposal
>>> based on a
>>> future object pattern. See separate proposal from him.
>>>
>>> <proposals>
>>>
>>> Query API support
>>> =================
>>> Proposal: Introduce new method Query.addSubquery(String
>>> variableName, Query
>>> subquery)
>>>
>>> This proposal entails utilizing the current Query API's
>>> declareVariables
>>> facility and adding a method Query.addSubquery(String,Query) to support
>>> subqueries. Essentially, a subquery is bound to a superquery via a
>>> variable
>>> declared for the superquery. The implementation handles coercing the
>>> subquery's result into the type of the variable(s) declared in the
>>> superquery. Queries can be nested to arbitrary levels.
>>>
>>> Example 1A: Find people with above average income
>>>
>>> Query superquery1a = pm.newQuery("SELECT FROM Person WHERE income >
>>> averageIncome");
>>> superquery1a.declareVariables("BigDecimal averageIncome;");
>>>
>>> Query subquery1a = pm.newQuery("SELECT avg(income) FROM Person");
>>> superquery1a.addSubquery("averageIncome", subquery1a); // binds
>>> subquery to
>>> superquery
>>>
>>>
>>> Example 2A: Find average income of fathers using subquery
>>>
>>> Query superquery2a = pm.newQuery("SELECT avg(income) FROM fathers");
>>> // in next line, Collection<Parent> derived from subquery
>>> superquery2a.declareVariables("Collection fathers;");
>>>
>>> Query subquery2a = pm.newQuery("SELECT FROM Person WHERE gender ==
>>> 'M' &&
>>> children.size() > 0");
>>> superquery2a.addSubquery("fathers", subquery2a); // binds subquery to
>>> superquery
>>>
>>>
>>> Example 3A: Find average income of fathers using a single Query
>>> instance
>>> Note: this example's usage is required if the grammar specification
>>> of the
>>> variables clause remains the same (as it currently is) in the API
>>> and string
>>> forms (see JDO 2.0 spec sections 14.6.5 & 14.6.13).
>>>
>>> Query superquery3a = pm.newQuery("SELECT avg(income) FROM fathers");
>>> // in next line, Collection<Parent> derived from subquery
>>> superquery3a.declareVariables(
>>> "Collection fathers = SELECT FROM Person WHERE gender == 'M' &&
>>> children.size() > 0;");
>>>
>>> Pros:
>>> * Maintains backward compatibility.
>>> * Enhances performance by allowing for the deferral of query
>>> execution
>>> until entire query with subqueries is defined. Current Query API
>>> support
>>> requires the execution of the subquery, then execution of the
>>> superquery;
>>> current JDOQL string spec doesn't allow for subqueries at all.
>>> * Grammar of the variables clause undergoes the same enhancements
>>> in both
>>> the single-string and the API.
>>> * Compatible with single-string enhancement proposal below
>>>
>>> Cons:
>>> * Type coercion becomes more complicated than just autoboxing.
>>> * Requires that variables may always have to be explicitly defined.
>>> * Possibility that type of candidate collection of superquery must be
>>> derived (see example 2A above), or may not be known.
>>> * Possibility of using variables in place of both parameters and
>>> candidate
>>> collections.
>>>
>>>
>>>
>>> Single-string proposal
>>> ======================
>>> Proposal: Use the existing VARIABLES JDOQL keyword in order to be
>>> compatible with the Query API proposal above.
>>>
>>> This proposal is very similar to JPOX JIRA CORE-2861
>>> (http://www.jpox.org/servlet/jira/browse/CORE-2861), which proposes the
>>> introduction of a new JDOQL keyword "WITH" to introduce typed and named
>>> subquery results. To make this more compatible with the Query API
>>> proposal
>>> above and to avoid the need to introduce a new keyword to JDOQL, the
>>> existing JDOQL keyword "VARIABLES" would be used to introduce typed and
>>> named subqueries, except that the variable(s) would be intialized
>>> via the
>>> assignment operator, "=", or the "AS" keyword (TBD) at declaration
>>> time with
>>> a valid JDOQL expression. Variables would continue to be
>>> semicolon-delimited. Additionally, Query.toString(), for queries that
>>> employ subqueries, returns JDOQL strings that use this syntax.
>>>
>>> Example 1S: Find people with above average income using subquery
>>> (similar
>>> to example 1A)
>>>
>>> SELECT FROM Person WHERE income > averageIncome
>>> VARIABLES float averageIncome =
>>> SELECT avg(income) FROM Person;
>>>
>>> Example 2S: Find average income of fathers using a subquery
>>> (similar to
>>> example 2A)
>>>
>>> SELECT avg(income) FROM parents
>>> VARIABLES Collection parents =
>>> SELECT FROM Parent WHERE gender == 'M' && children.size() > 0;
>>>
>>> Pros:
>>> * Continues to use existing JDOQL keywords.
>>> * Grammar of the variables clause undergoes the same enhancements
>>> in both
>>> the single-string and the API.
>>> * Compatible with Query API proposal above.
>>>
>>> Cons:
>>> * More verbose than introducing "WITH" keyword. Implicit, unnamed
>>> variables would not be supported, unless the following syntax were
>>> supported
>>> (from example 2S), where the tokens "VARIABLES Collection parents" is
>>> implied.
>>> SELECT avg(income) FROM parents =
>>> SELECT FROM Parent WHERE gender == 'M' && children.size() > 0;
>>> This syntax is admittedly less verbose and more like SQL
>>> subqueries, but
>>> leaves open the typing and naming of the implicit, unnamed variables.
>>> * Possibility of using variables in place of both parameters and
>>> candidate
>>> collections.
>>>
>>>
>>> Open issues
>>> ===========
>>>
>>> * Can type derivation & coercion of JDOQL variables be performed in all
>>> cases?
>>> * These proposals use the assignment operator ("="). Should we use
>>> assignment via the JDOQL keyword "AS" instead or in addition to the
>>> assignment operator?
>>> * This proposal requires that JDOQL variables be allowed to
>>> substitute for
>>> both JDOQL parameters and candidate collections. Should this be
>>> allowed?
>>>
>>>
>>> </proposals>
>>>
>>>
>>>> -----Original Message-----
>>>> From: Erik Bengtson [mailto:erik@jpox.org] Sent: Wednesday, June
>>>> 07, 2006 7:57 AM
>>>> To: jdo-experts-ext@sun.com; jdo-dev@db.apache.org
>>>> Subject: any plans to support subqueries with similar concept as in
>>>> sql
>>>>
>>>> Hi,
>>>>
>>>> We need the ability to work with multiple sets in the same query.
>>>> It includes
>>>> performing operations between sets, numeric functions like average
>>>> or sum,
>>>> etc..
>>>>
>>>> In JPOX it will implemented as exemplified here
>>>> http://www.jpox.org/servlet/jira/browse/CORE-2861
>>>>
>>>> Sadly, JDOQL 2 is not capable to compete with JPQL in this aspect.
>>>>
>>>> Regardless the above issue, are there plans to expand the JDOQL or
>>>> even JDO 2 in
>>>> general based on new user requests/requirements?
>>>>
>>>> Regards,
>>>>
>>>> Erik Bengtson
>>>>
>>>>
>>>
>>>
>>
>>
>> --Michael Bouschen Tech@Spree Engineering GmbH
>> mailto:mbo.tech@spree.de http://www.tech.spree.de/
>> Tel.:++49/30/235 520-33 Buelowstr. 66
>> Fax.:++49/30/2175 2012 D-10783 Berlin
>>
>
> Craig Russell
> Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
> 408 276-5638 mailto:Craig.Russell@sun.com
> P.S. A good JDO? O, Gasp!
>
--
Michael Bouschen Tech@Spree Engineering GmbH
mailto:mbo.tech@spree.de http://www.tech.spree.de/
Tel.:++49/30/235 520-33 Buelowstr. 66
Fax.:++49/30/2175 2012 D-10783 Berlin
Re: JDOQL Subquery proposals
Posted by Craig L Russell <Cr...@Sun.COM>.
Hi Michael,
I came up with some more queries that I'd like to discuss as we
consider how to do subqueries. I haven't found a use case for
subqueries that themselves contain subqueries...
Select employees who make more than the average of their department
employees?
Select employees who make more than the average of the employees in
their department at the same pay scale?
Select employees who make more than the average of all employees?
Select employees named Joe who make more than the average of all
employees?
Select employees named Joe who make more than the average of all
employees at the same pay scale?
Select employees who make more than the average of all employees at
the same pay scale?
Craig
On Jun 22, 2006, at 3:18 PM, Michael Bouschen wrote:
> Hi,
>
> I agree to what Craig mentioned in the JDO TCK minutes: method
> addSubquery separates the outer from the subquery. So it looks like
> that this approach cannot support correlated subqueries where the
> subquery iterates a collection field defined in the outer query. My
> preference is using variables declaration to support subqueries
> even in the Query API case.
>
> A query selecting employees earning more than the average salary
> could be:
> SELECT FROM Employee WHERE this.salary > averageSalary
> VARIABLES float averageSalary = SELECT AVG(this.salary) FROM
> Employee
>
> But I still have problems with a correlated subquery. I'm looking
> for a query selecting employees earning more than the average
> salary of their department. The difference to the query above is
> that the candidates collection of the subquery: the employees
> collection of the department of the current employee and not the
> entire Employee extent. Here are some ideas to stimulate the
> discussion, but I'm not quite happy with these.
> (1) Use a collection variable in the FROM clause of the subquery:
> SELECT FROM Employee WHERE this.salary > averageSalary
> VARIABLES Collection emps = this.department.employees;
> float averageSalary = SELECT AVG(this.salary) FROM emps
> One issue is that 'this' is ambiguous: I would think that 'this'
> always refers to an instance of the inner most scope. But this
> means the subquery cannot directly access a field of the outer
> query. So the outer query declares a variable emps that may be used
> in the inner query. Maybe we need a special syntax to access the
> 'this' from the outer query.
>
> (2) The second form does not define any FROM clause, instead it
> uses a variable bound to a collection field of the outer query:
> SELECT FROM Employee WHERE this.salary > averageSalary
> VARIABLES Employee e;
> float averageSalary = SELECT AVG(e.salary) WHERE
> this.department.employees.contains(e)
> Since the subquery does not have a FROM clause, 'this' refers to
> the current Employee from the outer query.
>
> BTW, this is the query as Java Persistence API Query (formerly EJB
> QL):
> SELECT e FROM Employee e JOIN e.department d
> WHERE e.salary > (SELECT AVG(e2.salary) FROM d.employees e2)
>
> About the open issue: using the assignment operator ("=") versus
> the JDOQL keyword "AS": I vote for the assignment operator. The
> "AS" keyword is used in the result expression of the form 'expr AS
> name'. In a variable declaration it would have the form 'type name
> AS expr'. It might be confusing that the variable declaration swaps
> the order, because here the expr is right of the keyword, where it
> is on the other side in the result expression.
>
> Regards Michael
>
>> Hi everyone,
>>
>> Here are 2 proposals discussed in the Fri 9 Jun JDO conference call
>> regarding support for subqueries in JDOQL, including single-string
>> and Query
>> API enhancements, inspired by JPOX's proposed enhancement,
>> documented in
>> JPOX JIRA issue CORE-2861
>> (http://www.jpox.org/servlet/jira/browse/CORE-2861). The string
>> and API
>> enhancement proposals described here are designed to be used hand-
>> in-hand,
>> as the folks on the call wanted to continue to provide compatible
>> string-based and API-based usages. In a nutshell, both proposals
>> hinge on
>> the use of the exising facility to declare JDOQL variables (not JDOQL
>> parameters) to bind subqueries to superqueries.
>>
>> Please read thoroughly, consider, and comment.
>>
>> --matthew
>>
>> PS: Martin was on the hook to describe an alternative proposal
>> based on a
>> future object pattern. See separate proposal from him.
>>
>> <proposals>
>>
>> Query API support
>> =================
>> Proposal: Introduce new method Query.addSubquery(String
>> variableName, Query
>> subquery)
>>
>> This proposal entails utilizing the current Query API's
>> declareVariables
>> facility and adding a method Query.addSubquery(String,Query) to
>> support
>> subqueries. Essentially, a subquery is bound to a superquery via
>> a variable
>> declared for the superquery. The implementation handles coercing the
>> subquery's result into the type of the variable(s) declared in the
>> superquery. Queries can be nested to arbitrary levels.
>>
>> Example 1A: Find people with above average income
>>
>> Query superquery1a = pm.newQuery("SELECT FROM Person WHERE income >
>> averageIncome");
>> superquery1a.declareVariables("BigDecimal averageIncome;");
>>
>> Query subquery1a = pm.newQuery("SELECT avg(income) FROM Person");
>> superquery1a.addSubquery("averageIncome", subquery1a); // binds
>> subquery to
>> superquery
>>
>>
>> Example 2A: Find average income of fathers using subquery
>>
>> Query superquery2a = pm.newQuery("SELECT avg(income) FROM fathers");
>> // in next line, Collection<Parent> derived from subquery
>> superquery2a.declareVariables("Collection fathers;");
>>
>> Query subquery2a = pm.newQuery("SELECT FROM Person WHERE gender ==
>> 'M' &&
>> children.size() > 0");
>> superquery2a.addSubquery("fathers", subquery2a); // binds subquery to
>> superquery
>>
>>
>> Example 3A: Find average income of fathers using a single Query
>> instance
>> Note: this example's usage is required if the grammar
>> specification of the
>> variables clause remains the same (as it currently is) in the API
>> and string
>> forms (see JDO 2.0 spec sections 14.6.5 & 14.6.13).
>>
>> Query superquery3a = pm.newQuery("SELECT avg(income) FROM fathers");
>> // in next line, Collection<Parent> derived from subquery
>> superquery3a.declareVariables(
>> "Collection fathers = SELECT FROM Person WHERE gender == 'M' &&
>> children.size() > 0;");
>>
>> Pros:
>> * Maintains backward compatibility.
>> * Enhances performance by allowing for the deferral of query
>> execution
>> until entire query with subqueries is defined. Current Query API
>> support
>> requires the execution of the subquery, then execution of the
>> superquery;
>> current JDOQL string spec doesn't allow for subqueries at all.
>> * Grammar of the variables clause undergoes the same
>> enhancements in both
>> the single-string and the API.
>> * Compatible with single-string enhancement proposal below
>>
>> Cons:
>> * Type coercion becomes more complicated than just autoboxing.
>> * Requires that variables may always have to be explicitly defined.
>> * Possibility that type of candidate collection of superquery
>> must be
>> derived (see example 2A above), or may not be known.
>> * Possibility of using variables in place of both parameters and
>> candidate
>> collections.
>>
>>
>>
>> Single-string proposal
>> ======================
>> Proposal: Use the existing VARIABLES JDOQL keyword in order to be
>> compatible with the Query API proposal above.
>>
>> This proposal is very similar to JPOX JIRA CORE-2861
>> (http://www.jpox.org/servlet/jira/browse/CORE-2861), which
>> proposes the
>> introduction of a new JDOQL keyword "WITH" to introduce typed and
>> named
>> subquery results. To make this more compatible with the Query API
>> proposal
>> above and to avoid the need to introduce a new keyword to JDOQL, the
>> existing JDOQL keyword "VARIABLES" would be used to introduce
>> typed and
>> named subqueries, except that the variable(s) would be intialized
>> via the
>> assignment operator, "=", or the "AS" keyword (TBD) at declaration
>> time with
>> a valid JDOQL expression. Variables would continue to be
>> semicolon-delimited. Additionally, Query.toString(), for queries
>> that
>> employ subqueries, returns JDOQL strings that use this syntax.
>>
>> Example 1S: Find people with above average income using subquery
>> (similar
>> to example 1A)
>>
>> SELECT FROM Person WHERE income > averageIncome
>> VARIABLES float averageIncome =
>> SELECT avg(income) FROM Person;
>>
>> Example 2S: Find average income of fathers using a subquery
>> (similar to
>> example 2A)
>>
>> SELECT avg(income) FROM parents
>> VARIABLES Collection parents =
>> SELECT FROM Parent WHERE gender == 'M' && children.size() > 0;
>>
>> Pros:
>> * Continues to use existing JDOQL keywords.
>> * Grammar of the variables clause undergoes the same
>> enhancements in both
>> the single-string and the API.
>> * Compatible with Query API proposal above.
>>
>> Cons:
>> * More verbose than introducing "WITH" keyword. Implicit, unnamed
>> variables would not be supported, unless the following syntax were
>> supported
>> (from example 2S), where the tokens "VARIABLES Collection parents" is
>> implied.
>> SELECT avg(income) FROM parents =
>> SELECT FROM Parent WHERE gender == 'M' && children.size() > 0;
>> This syntax is admittedly less verbose and more like SQL
>> subqueries, but
>> leaves open the typing and naming of the implicit, unnamed variables.
>> * Possibility of using variables in place of both parameters and
>> candidate
>> collections.
>>
>>
>> Open issues
>> ===========
>>
>> * Can type derivation & coercion of JDOQL variables be performed
>> in all
>> cases?
>> * These proposals use the assignment operator ("="). Should we use
>> assignment via the JDOQL keyword "AS" instead or in addition to the
>> assignment operator?
>> * This proposal requires that JDOQL variables be allowed to
>> substitute for
>> both JDOQL parameters and candidate collections. Should this be
>> allowed?
>>
>>
>> </proposals>
>>
>>
>>> -----Original Message-----
>>> From: Erik Bengtson [mailto:erik@jpox.org] Sent: Wednesday, June
>>> 07, 2006 7:57 AM
>>> To: jdo-experts-ext@sun.com; jdo-dev@db.apache.org
>>> Subject: any plans to support subqueries with similar concept as
>>> in sql
>>>
>>> Hi,
>>>
>>> We need the ability to work with multiple sets in the same query.
>>> It includes
>>> performing operations between sets, numeric functions like
>>> average or sum,
>>> etc..
>>>
>>> In JPOX it will implemented as exemplified here
>>> http://www.jpox.org/servlet/jira/browse/CORE-2861
>>>
>>> Sadly, JDOQL 2 is not capable to compete with JPQL in this aspect.
>>>
>>> Regardless the above issue, are there plans to expand the JDOQL
>>> or even JDO 2 in
>>> general based on new user requests/requirements?
>>>
>>> Regards,
>>>
>>> Erik Bengtson
>>>
>>>
>>
>>
>
>
> --
> Michael Bouschen Tech@Spree Engineering GmbH
> mailto:mbo.tech@spree.de http://www.tech.spree.de/
> Tel.:++49/30/235 520-33 Buelowstr. 66
> Fax.:++49/30/2175 2012 D-10783 Berlin
>
Craig Russell
Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
408 276-5638 mailto:Craig.Russell@sun.com
P.S. A good JDO? O, Gasp!
Re: JDOQL Subquery proposals
Posted by Abe White <aw...@bea.com>.
I haven't been following this discussion, and that's not likely to
change. I want to pop in, however, to point out that Kodo has had
JDOQL subqueries for a long time. They're likely too limited and
under-specified for the spec group's purposes, but perhaps they'll
give you some ideas:
http://solarmetric.com/Software/Documentation/4.0.0/docs/full/html/
ref_guide_enterprise_queryext.html#ref_guide_subqueries
_______________________________________________________________________
Notice: This email message, together with any attachments, may contain
information of BEA Systems, Inc., its subsidiaries and affiliated
entities, that may be confidential, proprietary, copyrighted and/or
legally privileged, and is intended solely for the use of the individual
or entity named in this message. If you are not the intended recipient,
and have received this message in error, please immediately return this
by email and then delete it.