I had a requirement to do a sql statement that contained a replace in the where. The case is that you want to do a search on a column in the database and you want replace some of the characters so that the search has a bit more power. As an example, you have a contact record that has an organization name of “D.I.” I want to be able to find that record by typing in “DI” to do the search. It seems the simplest solution to just do a replace on that column in sql to strip out some characters like spaces or periods. Not easily done in qcodo.
To solve the problem I added a function in QQuery.class.php:
class QQConditionLikeReplace extends QQConditionComparison {
public function __construct(QQNode $objQueryNode, $strValue) {
$this->objQueryNode = $objQueryNode;
if (!$objQueryNode->_ParentNode)
throw new QInvalidCastException('Unable to cast “' . $objQueryNode->_Name . '” table to Column-based QQNode', 3);
if ($strValue instanceof QQNamedValue)
$this->mixOperand = $strValue;
else {
try {
$this->mixOperand = QType::Cast($strValue, QType::String);
} catch (QCallerException $objExc) {
$objExc->IncrementOffset();
$objExc->IncrementOffset();
throw $objExc;
}
}
}
public function UpdateQueryBuilder(QQueryBuilder $objBuilder) {
$strColumn = $this->objQueryNode->GetColumnAlias($objBuilder);
if ($this->mixOperand instanceof QQNamedValue)
{
$objBuilder->AddWhereItem(“Replace(" . $strColumn . “,' ','') LIKE " . $this->mixOperand->Parameter());
$objBuilder->AddWhereItem(“OR Replace(" . $strColumn . “,'.','') LIKE " . $this->mixOperand->Parameter());
}
else
{
$objBuilder->AddWhereItem(“Replace(" . $strColumn . “,' ','') LIKE " . $objBuilder->Database->SqlVariable($this->mixOperand));
$objBuilder->AddWhereItem(“OR Replace(" . $strColumn . “,'.','') LIKE " . $objBuilder->Database->SqlVariable($this->mixOperand));
}
}
}
It seems to work pretty well. I'm not sure if this is a good idea to put into the qcodo base, but I'd love feedback on a better solution if this one isn't the best.