Currently subqueries can be issued using the “QQ::SubSql( $strSql )” node clause, but that means the need to handle hand-made SQL statements that break the DB-engine agnosticism of the application.
It could be very convenient if the QQueryBuilder could be used to build those subquery SQL statements. This way, a subquery could be composed as any other query, and the resulting SQL statement could be used as a “QQ to SQL” intermediate pass to get subquery nodes.
qcodo_query_methods.tpl
<?php
/**
* Static Qcodo method to get the SQL statement of a query.
* Uses BuildQueryStatment to perform most of the work.
* @param QQCondition $objConditions any conditions on the query, itself
* @param QQClause[] $objOptionalClausees additional optional QQClause objects for this query
* @param mixed[] $mixParameterArray a array of name-value pairs to perform PrepareStatement with
* @return string the SQL statement for the query
*/
public static function GetQuerySqlStatement(QQCondition $objConditions, $objOptionalClauses = null, $mixParameterArray = null) {
// Get the Query Statement
try {
return <%= $objTable->ClassName %>::BuildQueryStatement($objQueryBuilder, $objConditions, $objOptionalClauses, $mixParameterArray, false);
} catch (QCallerException $objExc) {
$objExc->IncrementOffset();
throw $objExc;
}
}
?>
This method, by the way, could also be used to log or debug SQL queries, as currently there's no way to log queries or take a look at a query when it's not well-formed or throws an exception.
But returning to the subqueries topic, this partial solution leaves a problem: the potential collisions between query and subquery aliases. I already solved this in a non generic manner, but here's a generalization of the idea I used, not difficult to implement thanks to Qcodo's elegant class designs:
SUBQUERIES IMPLEMENTATION
Let's consider the construction of any subquery (and any query, in fact) as identified by an unique SQL-safe id string. When building the (sub)query, we will suffix any aliases that can collide with a special named wildcard. Later, when preparing the (sub)query SQL statement, we'll substitute this wildcard with the (sub)query identifier string, that will be empty if it's not a subquery.
Let's assume the named wildcard is '{_SubqueryId}', although a more unusual name could be used to avoid possible named arguments collisions, and better if this name is used as a PHP defined constant. I'll also use the QQNamedValue::DelimiterCode character to delimite it, although a more specific constant should be defined for this.
When building queries, all table aliases should be suffixed with this named wildcard. A slight modification in the QQueryBuilder->GetTableAlias() method is enough for this purpose, as I've checked it's always used when composing table aliases and also JOIN aliases.
QQuery.class.php
<?php
class QQueryBuilder extends QBaseClass {
[...]
public function GetTableAlias($strTableName) {
if (!array_key_exists($strTableName, $this->strTableAliasArray)) {
$strTableAlias = 't' . $this->intTableAliasCount++;
$this->strTableAliasArray[$strTableName] = $strTableAlias;
} else {
$strTableAlias = $this->strTableAliasArray[$strTableName];
}
return $strTableAlias . chr(QQNamedValue::DelimiterCode) . '{_SubqueryId}';
}
[...]
?>
However, I've observed that there are two places where columns are assigned aliases not based in table aliases. These “fixed” column aliases should also be suffixed with the named wildcard to avoid collisions between query and subqueries column aliases.
QQuery.class.php
<?php
class QQueryBuilder extends QBaseClass {
[...]
public function AddSelectItem($strTableName, $strColumnName, $strFullAlias) {
$strTableAlias = $this->GetTableAlias($strTableName);
if (!array_key_exists($strFullAlias, $this->strColumnAliasArray)) {
$strColumnAlias = 'a' . $this->intColumnAliasCount++;
$this->strColumnAliasArray[$strFullAlias] = $strColumnAlias;
} else {
$strColumnAlias = $this->strColumnAliasArray[$strFullAlias];
}
$this->strSelectArray[$strFullAlias] = sprintf('%s%s%s.%s%s%s AS %s%s%s%s',
$this->strEscapeIdentifierBegin, $strTableAlias, $this->strEscapeIdentifierEnd,
$this->strEscapeIdentifierBegin, $strColumnName, $this->strEscapeIdentifierEnd,
$this->strEscapeIdentifierBegin, $strColumnAlias, chr(QQNamedValue::DelimiterCode) . '{_SubqueryId}', $this->strEscapeIdentifierEnd);
}
public function AddSelectFunction($strFunctionName, $strColumnName, $strFullAlias) {
$this->strSelectArray[$strFullAlias] = sprintf('%s(%s) AS %s__%s%s%s',
$strFunctionName, $strColumnName,
$this->strEscapeIdentifierBegin, $strFullAlias, chr(QQNamedValue::DelimiterCode) . '{_SubqueryId}', $this->strEscapeIdentifierEnd);
}
[...]
?>
This wildcard suffix should be always substituted with the subquery id (empty if not a subquery) in the QDataBase->PrepareStatement() method, the same way as named parameters are.
QDatabaseBase.class.php
<?php
public function PrepareStatement($strQuery, $mixParameterArray) {
foreach ($mixParameterArray as $strKey => $mixValue) {
if (is_array($mixValue)) {
$strParameters = array();
foreach ($mixValue as $mixParameter)
array_push($strParameters, $this->Database->SqlVariable($mixParameter));
$strQuery = str_replace(chr(QQNamedValue::DelimiterCode) . '{' . $strKey . '}', implode(',', $strParameters) . ')', $strQuery);
} else if ($strKey == '_SubqueryId') {
// Alias suffix substitution.
$strQuery = str_replace(chr(QQNamedValue::DelimiterCode) . '{' . $strKey . '}', '_' . QType::Cast($mixValue, QType::String), $strQuery);
} else {
$strQuery = str_replace(chr(QQNamedValue::DelimiterCode) . '{=' . $strKey . '=}', $this->SqlVariable($mixValue, true, false), $strQuery);
$strQuery = str_replace(chr(QQNamedValue::DelimiterCode) . '{!' . $strKey . '!}', $this->SqlVariable($mixValue, true, true), $strQuery);
$strQuery = str_replace(chr(QQNamedValue::DelimiterCode) . '{' . $strKey . '}', $this->SqlVariable($mixValue), $strQuery);
}
}
// Alias suffix substitution if no suffix specified (not a subquery).
$strQuery = str_replace(chr(QQNamedValue::DelimiterCode) . '{_SubqueryId}', '', $strQuery);
return $strQuery;
}
?>
With this, we've added support for suffixing all the query aliases (table, JOIN and column aliases) with an optional (sub)query identifier, that can be specified as a BuildQuery parameter.
Finally, when getting an SQL statement specifically for a subquery, a query id should be forced to be used as alias suffix.
qcodo_query_methods.tpl
<?php
/**
* Static Qcodo method to get the SQL statement for a subquery.
* Uses BuildQueryStatment to perform most of the work.
* @param QQCondition $objConditions any conditions on the query, itself
* @param QQClause[] $objOptionalClausees additional optional QQClause objects for this query
* @param mixed[] $mixParameterArray a array of name-value pairs to perform PrepareStatement with
* @return string the SQL statement for the subquery
*/
public static function GetSubquerySqlStatement(QQCondition $objConditions, $objOptionalClauses = null, $mixParameterArray = null) {
if (!is_array($mixParameterArray)) {
$mixParameterArray = Array();
}
if (!array_key_exists('_SubqueryId', $mixParameterArray)) {
$mixParameterArray['_SubqueryId'] = uniqid('_subq_');
}
// Get the subquery statement
try {
return <%= $objTable->ClassName %>::BuildQueryStatement($objQueryBuilder, $objConditions, $objOptionalClauses, $mixParameterArray, false);
} catch (QCallerException $objExc) {
$objExc->IncrementOffset();
throw $objExc;
}
}
?>
That would work and avoid aliases collisions. In fact, I've used a particularization of this approach in one application with full success.
Take care of never specifying a '_SubqueryId' value when composing the SQL of the main query, as it's only for subqueries. If you accidentally do, the InstantiateDbRow() logic could not work for the main query result.
With this simple implementation you still can't use a subquery as a subtable in the FROM clause, but you can do almost anything else. In fact, it allows to use DB-engine agnostic subqueries in condition clauses and also subquery expansions:
- Subquery node for condition clauses:
QQ::SubSql(
<ClassName>::GetSubquerySqlStatement(
<conditions>,
<clauses>
)
[, <substitution QQNode 1>]
[, <substitution QQNode 2>]
[, etc.]
)
- Subquery expansion of a query:
QQ::Expand(
QQ::Virtual( $strVirtualName,
QQ::SubSql(
<ClassName>::GetSubquerySqlStatement(
<conditions>,
<clauses>
)
[, <substitution QQNode 1>]
[, <substitution QQNode 2>]
[, etc.]
)
)
)
Additionally, this also brings full support for nested subqueries, with any nesting level.
There's one limitation in this subquery nesting, however: when using named arguments in subqueries, substitution QQNodes always inherit the QQueryBuilder from its “parent” query when being converted to column names/aliases, you can't choose to which subquery nesting level they refer to. So for example, in a query like ( Query 1 ( Subquery 2 ( Subquery 3 ) ) ), you can't substitute a named argument in (Subquery 3) with a query node from (Query 1), but only with a query node that refers to (Subquery 2). However, I think that will be enough for most scenarios.
As a final improvement, the “QQ to SQL” intermediate pass to get subquery nodes could be hidden by including a factory method for QQSubQuerySqlNode nodes in each active record class:
qcodo_query_methods.tpl
<?php
/**
* Static Qcodo method to get a subquery node for a <%= $objTable->ClassName %>-based subquery.
* Uses BuildQueryStatment to perform most of the work.
* @param QQCondition $objConditions any conditions on the query, itself
* @param QQClause[] $objOptionalClausees additional optional QQClause objects for this query
* @param mixed[] $mixParameterArray a array of name-value pairs to perform PrepareStatement with
* @return string the SQL statement for the query
*/
public static function Subquery(QQCondition $objConditions, $objOptionalClauses = null, $mixParameterArray = null) {
if (!is_array($mixParameterArray)) {
$mixParameterArray = Array();
}
if (!array_key_exists('_SubqueryId', $mixParameterArray)) {
$mixParameterArray['_SubqueryId'] = uniqid('_subq_');
}
// Get the subquery statement
try {
$strQuery = <%= $objTable->ClassName %>::BuildQueryStatement($objQueryBuilder, $objConditions, $objOptionalClauses, $mixParameterArray, false);
} catch (QCallerException $objExc) {
$objExc->IncrementOffset();
throw $objExc;
}
$objParentQueryNodeArray = func_get_args();
array_shift($objParentQueryNodeArray);
array_shift($objParentQueryNodeArray);
array_shift($objParentQueryNodeArray);
return new QQSubQuerySqlNode($strQuery, $objParentQueryNodeArray);
}
?>
This way, the usage of subqueries would be simplified in an elegant manner:
- Subquery node for condition clauses:
<ClassName>::Subquery(
<conditions>,
<clauses>,
null
[, <substitution QQNode 1>]
[, <substitution QQNode 2>]
[, etc.]
)
- Subquery expansion of a query:
QQ::Expand(
QQ::Virtual( $strVirtualName,
<ClassName>::Subquery(
<conditions>,
<clauses>,
null
[, <substitution QQNode 1>]
[, <substitution QQNode 2>]
[, etc.]
)
)
)
Another possible improvement: if our DB engine is capable of caching queries results, it should be convenient to generate the '_SubqueryId' unique query id as a hash that involves the conditions and clauses of the subquery and also the substitution nodes for its named arguments. That way the aliases suffix should be always the same under the same circumstances, so queries with subqueries could also be cached.
Two things to note:
- The indentation when profiling queries with subqueries could be a visual issue (as it currently is). But I think the gain really pays the bill, compared with this minor inconvenience.
- I've chosen to suffix aliases instead of prefixing them because I presume that if some day a full subqueries implementation is developed (with no need for the “QQ to SQL” intermediate pass, and full support for subqueries as subtables inside the FROM part of the SELECT), it will use prefixes for aliases, as currently JOIN expansions do.
I hope you find this solution helpful. Standard “QQ” subqueries were still an unresolved issue.