Doctrine - How to Print Out the Real SQL, Not Just the Prepared Statement

Doctrine - How to print out the real sql, not just the prepared statement?

Doctrine is not sending a "real SQL query" to the database server : it is actually using prepared statements, which means :

• Sending the statement, for it to be prepared (this is what is returned by $query->getSql())
• And, then, sending the parameters (returned by $query->getParameters())
• and executing the prepared statements

This means there is never a "real" SQL query on the PHP side — so, Doctrine cannot display it.

How to get the raw sql of doctrine

According to Logging Doctrine SQL queries in Symfony2:

You can use a Doctrine logger such as DebugStack or own implementation of SQLLogger interface.

$logger = new \Doctrine\DBAL\Logging\DebugStack();

/* @var Doctrine\DBAL\Connection $connection */
$connection->getConfiguration()->setSQLLogger($logger);

After execution of some queries you can get query strings and parameters from public property of the logger.

var_dump($logger->queries);

---

You second question is very different. Btw, as said in documentation:

Objects that were not already loaded from the database are replaced
with lazy load proxy instances. Non-loaded Collections are also
replaced by lazy-load instances that fetch all the contained objects
upon first access. However relying on the lazy-load mechanism leads to
many small queries executed against the database, which can
significantly affect the performance of your application. Fetch Joins
are the solution to hydrate most or all of the entities that you need
in a single SELECT query.

Doctrine 2 DBAL and QueryBuilder - It really works?

Perfectly normal. The essence of prepared statements is that the sql is sent to the database server independent of the actual values. The two are always kept apart, and merged at database level; hence getSQLfrom Doctrine is unable to return the fully compiled SQL query.

It is a bit odd that the documentation does not have an actual example of executing the query.

Personally I'm not a big fan of QueryBuilder::setParameter. Instead I do:

$queryBuilder = $conn->createQueryBuilder();
$queryBuilder
    ->select('id', 'name')
    ->from('users')
    ->where('email = ?');

$stmt = $conn->executeQuery($queryBuilder->getSQL(),[$userInputEmail]);
$user = $stmt->fetch();

Just need to send an array of values whose order matches your parameter order. As a bonus, you can send an array for WHERE xxx IN(?). Something which prepared statements don't normally handle.

As far as the insert goes, I think you may be overthinking things. The connection object supports insert,update and delete without the need for a query builder.

$conn->insert('core_users',['username'=>'DTrump','secret='Putin']);

=================================

Here is a working example using setParameter

    $qb
        ->select('projectId','gameNumber','fieldName')
        ->from('games')
        ->where('gameNumber = ?')
        ->setParameter(0,11457);

    $stmt = $qb->execute();

    dump($stmt->fetchAll());

Doctrine IF Statement

Doctrine does not have IF expression support by default. Instead CASE expressions are supported, which can be converted to: CASE t.deleted WHEN 1 THEN 4 ELSE t.status END

Your original query converted to the ORM QueryBuilder would be:

$this->createQueryBuilder('t')
    ->addSelect('CASE t.deleted WHEN 1 THEN 4 ELSE t.status END AS status')
    ->where('t.status LIKE :status')
    ->orderBy('t.createdAt', 'DESC')
    ->setParameter('status', $status)
    ->setMaxResults($limit)
    ->getQuery()
    ->getResult();

However, this will not modify the value sent to the entity but instead produce a nested array result with the status value separated.

array
    array
        [0] => Entity (Object)
        ['status'] => 4
    array
        [0] => Entity (Object)
        ['status'] => 4

    // ...

where in no result in doctrine query builder

Try with

use Doctrine\DBAL\Connection;

//....

->setParameter('groles', $users, Connection::PARAM_STR_ARRAY)

Doctrine DBAL 2.13 $statement-execute() returns bool (instead of Result)

Update to doctrine/dbal 2.13.1 (released 4/2021) and use:

$result = $statement->executeQuery();
while (($row = $result->fetchAssociative()) !== false) {
}

Note that executeQuery() should be used to get the Result-object as execute() is now also deprecated. (this is also missing in the release notes)

Excluding overlapped period in doctrine QueryBuilder

I found out that using a subquery with NOT EXISTS () instead of inner join is the best way to do:

SELECT *
FROM product p 
WHERE NOT EXISTS (
  SELECT * FROM booking b WHERE (
    (b.startAt between '2022-01-05 00:00:00' and '2022-01-15 00:00:00') OR 
    (b.endAt between '2022-01-05 00:00:00' and '2022-01-15 00:00:00') OR 
    ('2022-01-05 00:00:00' between b.startAt and b.endAt)
  ) AND b.product_id = p.id
) IN (1,2);

Here, the subquery find conflicting bookings (thanks @nice_dev in comments).

Doctrine repository:

class ProductRepository extends EntityRepository
{
    private EntityRepository $bookingRepository;

    public function __construct(BookingRepository $bookingRepository)
    {
        $this->bookingRepository = $bookingRepository;
    }

    public function getAvailableProducts(\DateTimeInterface $startAt, \DateTimeInterface $endAt): array
    {
        $bookingQueryBuilder = $this->bookingRepository->createQueryBuilder('b')
            ->andWhere('b.startAt between :startAt and :endAt OR b.endAt between :startAt and :endAt OR :startAt between b.startAt and b.endAt')
            ->andWhere('b.product = p')
        ;

        return $this->createQueryBuilder('p')
            ->andWhere(sprintf('not exists (%s)', $bookingQueryBuilder->getDQL()))
        ;
    }
}

Need Doctrine Many to Many QueryBuilder Query to Return NOT In Common Rows

I would suggest to break down your logic as

First select prices that belongs to $user->getId() as

$userPrices =   $this->createQueryBuilder("u")
                     ->select("u.prices")
                     ->from("YourBundleName:Prices","p")
                     ->innerJoin('p.users', 'u')
                     ->andWhere('u.id = :user1Id')
                     ->getDQL();

Then get prices for owner which is $owner->getId() and exclude prices from the subquery for $user->getId() as

$qb = $this->createQueryBuilder("pr");
 $qb->select("pr")
    ->from("YourBundleName:Price", "pr")
    ->innerJoin('pr.owner', 'o')
    ->where(
        $qb->expr()->not(
            $qb->expr()->in(
            "pr.id",
            $userPrices
            )
        )
    )
    ->andWhere('o.id = :ownerId')
    ->setParameter('owner1Id', $owner->getId())
    ->setParameter('user1Id', $user->getId())
;
$query = $qb->getQuery();
$result = $query->getResult();

This would be more like to your original query but not the exact one I guess and might need some tweaks as per your mappings, but will give you an idea to move forward with this

References

• Doctrine EXIST and NOT EXIST
• Doctrine Query Builder nested orX and andX conditions with join

---

Related Topics