时间戳

首页 > 解决方案 > SQL query doctrine symfony

php - SQL query doctrine symfony

问题描述

I would like to design a query in my repository, but it's a bit complex to do it with the queryBuilder method, so I prefer a complete query.

$queryBuilder = $this->_em->createQueryBuilder(
        "SELECT need_id
        FROM notification_need
        WHERE user_id <> :user
        AND check_date >=
            (SELECT check_date
                FROM notification_need
                WHERE user_id = :user
                ORDER BY check_date DESC
                LIMIT 1)
        AND need_id IN
            (SELECT id
            FROM option_need
            WHERE commande_id IS NULL)
                ")
        ->setParameter('user', $userId);

    return $queryBuilder->getQuery()->getResult();

But an error is generated when executing this request:

[Syntax Error] line 0, col -1: Error: Expected IdentificationVariable | ScalarExpression | AggregateExpression | FunctionDeclaration | PartialObjectExpression | "(" Subselect ")" | CaseExpression, got end of string.

标签： phpsymfonydoctrine

解决方案

Query Builder in Doctrine is meant for use with DQL, not plain SQL. If you want to run plain SQL, you need to take the DB connection object from the Entity Manager, and call executeQuery on it:

$conn = $this->_em->getConnection();
$result = $conn->executeQuery(/* your query goes here */);

If you want to use parameters, you can take advantage of prepared statements. In this case, you can work just like with the regular PDO objects:

$stmt = $conn->prepare('SELECT * FROM some_table WHERE id = :id');
$stmt->bindValue(':id', $someId);
$result = $stmt->fetchAll();

推荐阅读