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:[EMAIL PROTECTED] Sent: Wednesday, June
07, 2006 7:57 AM
To: [EMAIL PROTECTED]; [email protected]
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 [EMAIL PROTECTED] Engineering GmbH
mailto:[EMAIL PROTECTED] 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:[EMAIL PROTECTED]
P.S. A good JDO? O, Gasp!
