I am developing a list with filter in Yii1 and doctrine2. I will skip unnecessary information like controller-action codes and just explain the problem.
This is my queryBuilder object:
$queryBuilder = $this->createQueryBuilder('asset')
->select("asset.id,
CONCAT(user.firstName, CONCAT(' ',user.lastName)) assignedBy,
asset.createdAt, asset.serial, asset.brand, asset.model,
assetType.name assetTypeName,
CONCAT(assigned.firstName,CONCAT(' ',assigned.lastName)) assignedTo,
asset.status, location.name locationName")
->leftJoin('asset.user', 'user')
->leftJoin('asset.assigned', 'assigned')
->leftJoin('asset.location', 'location')
->leftJoin('asset.assetType', 'assetType');
As you may noticed, I have two aggregated columns(assignedBy, AssignedTo) without alias(If I could, I would write user.assignedBy in the first one and same for the later one, but it is not acceptable in sql since they do not belong to any actual table).
Later on, when form submitted in client, I need to add like expressions for them. (It is kind of auto generated query, but for simplification I will mention the function in charge of it only and skip the rest) this function works with an array from $_REQUEST. I put it here as guide:
array (size=9)
'assignedBy' => string 'mojtaba' (length=7)
'createdAt' => string '' (length=0)
'serial' => string '' (length=0)
'brand' => string '' (length=0)
'model' => string '' (length=0)
'assetType' => string '' (length=0)
'assigned' => string '' (length=0)
'location' => string '' (length=0)
'status' => string '' (length=0)
and the function:
/**
* @return $this
* @throws \CException
*/
public function applyFilter()
{
// it is the array came from $_REQUEST, I mentioned it above.
if (isset($_REQUEST[$this->getFilterKey()])) {
$or = $this->getQueryBuilder()->expr()->orX();
foreach ($_REQUEST[$this->getFilterKey()] as $key => $val) {
if ($val) {
$term = $key;
$or->add($this->getQueryBuilder()->expr()->like($term, ":" . $key));
$this->getQueryBuilder()->setParameters(array($key => "%" . $val . "%"));
}
}
($or->getParts()) ? $this->getQueryBuilder()->andWhere($or) : "";
}
return $this;
}
for all the columns with alias in this query, there is absolutely no problem but for these two, I get this error:
[Syntax Error] line 0, col 186: Error: Expected '.' or '(', got 'assignedBy'
and the real error occurs in here:
vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(2541): Doctrine\ORM\Query\Parser->LikeExpression()
I have read that QueryBuilder does not support functions in Like expression here: Doctrine query + LIKE expression, but It is a different matter, I do not need to use functions, I want to use a column without alias in queryBuilder alias expression and I get this error, any help will be appreciated.