QQuery - SubQueries SubSQL - natural support for literals

[alex94040]

Ported from  http://www.qcodo.com/forums/topic.php/2770/1//?strSearch=SubSQL:

I've just been banging my head against the wall with subqueries - I think they're really cool, but without documentation, it's a bit tough to get them right the first time. Or the 10-th time, in my case :-)

One particular issue has been messing me up badly: the fact that {1}-style substitutions only work with QQ Nodes, not literals.

So a supposedly innocent query

QQ::Virtual('invitationCount',
   QQ::SubSql('SELECT count(*)  
               FROM invitation
               WHERE invitation.JobID = {1} AND
               invitation.WorkerID = {2}', 
               QQN::Job()->Id, 
               $myIntegerWorkerId
              )
)

falls flat on its face with a cryptic error message about GetColumnAlias?. The root cause, of course, is that {2} is not the right way to refer to the integer literal here. And that's totally not intuitive - I had to go with the following 'messy' code instead:

QQ::Virtual('invitationCount',
   QQ::SubSql('SELECT count(*)
            FROM invitation
            WHERE invitation.JobID={1} AND
            invitation.WorkerID=' . intval($myIntegerWorkerId), 
            QQN::Job()->Id
          )
)

Please, add support for literals, not just QQN nodes, into SubSql?.

[VexedPanda]

I really wish Mike Ho had used my QQCustomCondition as inspiration and adopted the printf syntax for replacements instead.

Either way, this is my code from QQCustomCondition that achieves the desired goal, and can be used as inspiration to fix this in the QQSubQuerySqlNode:

$strWhereNodes = array();
//Allowed "nodes" are arrays or singles of Nodes, or raw SQL strings
if(is_array($this->mixWhereNodes))
{
	foreach($this->mixWhereNodes as $node)
	{
		if($node instanceof QQNode)
			$strWhereNodes[] = $node->GetColumnAlias($objBuilder);
		else
			$strWhereNodes[] = $node;
	}
}
else
{
	if($this->mixWhereNodes instanceof QQNode)
		$strWhereNodes[] = $this->mixWhereNodes->GetColumnAlias($objBuilder);
	elseif(null !== $this->mixWhereNodes)
		$strWhereNodes[] = $this->mixWhereNodes;
}

//insert the "nodes" into the SQL expression
$this->strWhereExpression = vsprintf($this->strWhereExpression, $strWhereNodes);