| Wes Biggs 2006-09-17, 7:11 pm |
| 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)
>
>
> Below you find my ideas for the single-string and Query API JDOQL for
> the queries you mentioned.
>
>
> 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");
>
>
> 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");
>
>
> 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");
>
>
> 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");
>
>
> 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");
>
>
> 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
>
>
>
|