In the last tutorial we examined a query that retrieved a list of staff members under the age of 22. One of the main benefits of having a formal Condition specification is that developers can create reusable queries that are dynamically filterable. Let's refactor the previous example a bit by creating a retrieveStaffQuery method that returns a From instance.
function retrieveStaffQuery(dataContext) {
return dataContext
.from('staff s')
.leftOuterJoin('s.bonuses b')
.innerJoin('s.bike_shops bs');
}
After making this small change, the query is more reusable. The filter and order are no longer hard coded, so developers can filter and order the staff list on the fly.
In the previous tutorial we mentioned that the Conditions adhere to a formally-defined language. Let's take a look at the language grammar.
<condition> ::= "{" <comparison> | <null-comparison> | <in-comparison> | <logical-condition> "}"
<comparison> ::= <comparison-operator> ":" "{" <column> ":" <value> "}"
<null-comparison> ::= <null-comparison-operator> ":" "{" <column> ":" <nullable> "}"
<in-comparison> ::= <in-comparison-operator> ":" "{" <column> ":" "[" <value> {"," <value>} "]" "}"
<logical-condition> ::= <boolean-operator> ":" "[" <condition> {"," <condition>} "]"
<comparison-operator> ::= "$eq" | "$neq" | "$lt" | "$lte" | "$gt" | "$gte" | "$like" | "$notlike"
<in-comparison-operator> ::= "$in" | "$notIn"
<null-comparison-operator> ::= "$is" | "$isnt"
<boolean-operator> ::= "$and" | "$or"
<nullable> ::= null | <parameter>
<value> ::= <parameter> | <column> | <number> | null
<column> ::= <string>
<parameter> ::= :<string>
That's a bit hard to follow, so let's move forward by way of example and create a complex Condition object. Let's say we want to find all staff members that are either:
- Male and over the age of 25, or
- female and over the age of 23.
This Condition requires two ANDs and an OR. Working from the inside out, create two comparisons:
{$eq: {'staff.sex': ':maleSex'}}
{$gt: {'staff.age': ':maleAge'}}
Now, use a logical-condition to AND the two comparisons together.
{
// Male and over the age of 25.
$and: [
{$eq: {'staff.sex': ':maleSex'}},
{$gt: {'staff.age': ':maleAge'}}
]
}
Next create a similar logical-condition for females over the age of 23.
{
// Female and over the age of 23.
$and: [
{$eq: {'staff.sex': ':femaleSex'}},
{$gt: {'staff.age': ':femaleAge'}}
]
}
And lastly, use one final logical-condition to OR the conditions together. Take a look at the following example, which illustrates this complex Condition.
'use strict';
const MySQLDriver = require('node-data-mapper-mysql').MySQLDriver;
const driver = new MySQLDriver(require('../bikeShopConOpts.json'));
const util = require('util');
driver
.initialize()
.then(runQuery)
.then(printResult)
.catch(console.error)
.finally(() => driver.end());
/**
* Get a From instance which is a combination of staff, staff bonuses,
* and place of employment.
*/
function retrieveStaffQuery(dataContext) {
return dataContext
.from('staff')
.leftOuterJoin('staff.bonuses')
.innerJoin('staff.bike_shops');
}
function runQuery(dataContext) {
// Condition object to find all staff that are either:
// 1) male and over the age of 25, or
// 2) female and over the age of 23.
const cond = {
$or: [
{
// Male and over the age of 25.
$and: [
{$eq: {'staff.sex': ':maleSex'}},
{$gt: {'staff.age': ':maleAge'}}
]
},
{
// Female and over the age of 23.
$and: [
{$eq: {'staff.sex': ':femaleSex'}},
{$gt: {'staff.age': ':femaleAge'}}
]
}
]
};
const params = {
maleSex: 'male',
maleAge: 25,
femaleSex: 'female',
femaleAge: 23
};
const query = retrieveStaffQuery(dataContext)
.where(cond, params)
.select()
.orderBy('staff.sex', 'staff.age');
console.log('Query:');
console.log(query.toString(), '\n');
return query
.execute();
}
function printResult(result) {
console.log('Result:');
console.log(util.inspect(result, {depth: null}));
}
And here is the output from the previous example ($ node example/retrieve/advancedWhere.js):
Query:
SELECT `staff`.`age` AS `staff.age`,
`staff`.`bikeShopID` AS `staff.bikeShopID`,
`staff`.`firstName` AS `staff.firstName`,
`staff`.`hasStoreKeys` AS `staff.hasStoreKeys`,
`staff`.`hireDate` AS `staff.hireDate`,
`staff`.`lastName` AS `staff.lastName`,
`staff`.`sex` AS `staff.sex`,
`staff`.`staffID` AS `staff.staffID`,
`bonuses`.`amount` AS `bonuses.amount`,
`bonuses`.`bonusID` AS `bonuses.bonusID`,
`bonuses`.`dateGiven` AS `bonuses.dateGiven`,
`bonuses`.`reason` AS `bonuses.reason`,
`bonuses`.`staffID` AS `bonuses.staffID`,
`bike_shops`.`address` AS `bike_shops.address`,
`bike_shops`.`bikeShopID` AS `bike_shops.bikeShopID`,
`bike_shops`.`name` AS `bike_shops.name`
FROM `staff` AS `staff`
LEFT OUTER JOIN `bonuses` AS `bonuses` ON `staff`.`staffID` = `bonuses`.`staffID`
INNER JOIN `bike_shops` AS `bike_shops` ON `staff`.`bikeShopID` = `bike_shops`.`bikeShopID`
WHERE ((`staff`.`sex` = :maleSex AND `staff`.`age` > :maleAge) OR (`staff`.`sex` = :femaleSex AND `staff`.`age` > :femaleAge))
ORDER BY `staff`.`sex` ASC, `staff`.`age` ASC
Result:
{ staff:
[ { staffID: 6,
age: 29,
bikeShopID: 3,
firstName: 'Valerie',
hasStoreKeys: ,
hireDate: 2007-11-12T00:00:00.000Z,
lastName: 'Stocking',
sex: 'female',
bonuses:
[ { bonusID: 2,
amount: 600,
dateGiven: 2009-07-07T00:00:00.000Z,
reason: 'Most supportive team member.',
staffID: 6 } ],
bike_shops:
{ bikeShopID: 3,
address: '3100 La Riviera Wy',
name: 'Cycle Works' } },
{ staffID: 5,
age: 42,
bikeShopID: 3,
firstName: 'Sal',
hasStoreKeys: ,
hireDate: 2004-01-30T00:00:00.000Z,
lastName: 'Green',
sex: 'male',
bonuses: [],
bike_shops:
{ bikeShopID: 3,
address: '3100 La Riviera Wy',
name: 'Cycle Works' } },
{ staffID: 2,
age: 54,
bikeShopID: 1,
firstName: 'John',
hasStoreKeys: ,
hireDate: 1999-08-14T00:00:00.000Z,
lastName: 'Stovall',
sex: 'male',
bonuses: [],
bike_shops:
{ bikeShopID: 1,
address: '9107 Sunrise Blvd',
name: 'Bob\'s Bikes' } },
{ staffID: 4,
age: 67,
bikeShopID: 2,
firstName: 'Abe',
hasStoreKeys: ,
hireDate: 2015-09-19T00:00:00.000Z,
lastName: 'Django',
sex: 'male',
bonuses: [],
bike_shops:
{ bikeShopID: 2,
address: '18271 Highway 50',
name: 'Zephyr Cove Cruisers' } } ] }
Note that the WHERE clause is parenthesized correctly.