我想使用Doctrines QueryBuilder创建一个查询。
用户可以通过选中或取消选中HTML表单中的复选框来选择要搜索的数据库字段。这就是$_POST contains 'filters'
和"like"的原因。
$_POST['filters']
看起来像这样:
array(2) { [0]=> string(4) "rack" [1]=> string(5) "shelf" }
我正在尝试获得以下查询工作以产生
SELECT * FROM `inventories` WHERE (`rack` OR `shelf` LIKE '%01%') AND `checkedOutAt` IS NULL ORDER BY `lastChangedAt`
我在版本2.5.5中使用Doctrine,在版本7中使用PHP。我的控制器看起来像这样:
public function searchAction()
{
$filters = array();
$em = $this->getEntityManager();
$vendors = $em->getRepository('EntitiesVendor')->findAll();
if ($_POST)
{
$filters = $_POST['filters'];
$like = trim($_POST['like']);
$inventories = $em
->getRepository('EntitiesInventory')
->findInventoriesBySearch($like, $filters)
;
$this->addContext('like', $like);
}
else
{
$inventories = $em
->getRepository('EntitiesInventory')
->findInventories()
;
}
$count = count($inventories);
$this->addContext('filters', $filters);
$this->addContext('vendors', $vendors);
$this->addContext('inventories', $inventories);
$this->addContext('count', $count);
$this->setTemplate('inventoryAction');
}
根据存储库("findInventories(("存储库功能运行良好(:
public function findInventoriesBySearch($like, $filters)
{
$em = $this->getEntityManager();
$orExpr = $qb->expr()->orX();
foreach ($filters as $filter)
{
$orExpr->add($qb->expr()->like($filter, $like));
}
$qb ->andWhere('i.checkedOutAt is NULL');
$qb->setParameter('like', '%' . $like . '%');
$qb->select('i')->where($orExpr)->addOrderBy('i.lastChangedAt', 'DESC');
return $qb->getQuery()->getResult();
}
当我运行脚本时,我收到以下错误消息:
致命错误:Uncaught Doctrine\ORM\Query\QueryException:SELECT iWHERE rack LIKE 01 OR shelf LIKE 01 ORDER BY i.lastChangedAt DESC in…/vender/doctrine/orm/lib/doctrine/orm/QueryException.php:41堆栈跟踪:#0…/vender/doctrine/orm/lib/doctrine/orm/Query/Parser.php(483(:Doctrine\ORM\Query\QueryException::dqlError('SELECT i WHERE…'(#1…/vender/doctrine/orm/lib/doctrine/orm/Query/Parser.php(971(:Doctrine\ORM\Query\Parser->语义错误('第0行,第15列…',阵列(#2…/vender/doctrine/orm/lib/doctrine/orm/Query/Parser.php(1702(:Doctrine\ORM\Query\Parser->AbstractSchemaName((#3…/vender/doctrine/orm/lib/doctrine/orm/Query/Parser.php(1557(:Doctrine\ORM\Query\Parser->RangeVariableDeclaration((#4…/vender/doctrine/orm/lib/doctrine/orm/Query/Parser.php(1292(:中的Doctrine\ORM\Query\Parser->IdentificationVariableDeclaration((…/vender/doctory/orm/lib/Doctory/orm/QueryException.php on第63行
尝试检查&改变它。
-
(只是一个建议(不要使用
$_POST
/请求类型/访问它等。通过"你的方式"。使用类型提示的Request
,例如:public function searchAction(Request $request){ if ($request->isMethod('POST')) { $filters = $request->request->get('filters'); //..... }else{ } }
-
我猜,问题是:
//... foreach ($filters as $filter) { $filter = 'i.'.$filter; // <== 'i.rack','i.shelf', so on $orExpr->add($qb->expr()->like($filter, $like)); }