How to bind object params to raw sql #6575

New Issue

admin · 2026-01-22T15:35:12+01:00

admin commented

2026-01-22 15:35:12 +01:00

Originally created by @zmitic on GitHub (Nov 29, 2020).

Consider this simplified example:

$count = $em->createQuery('SELECT COUNT(o.id) FROM Contract o WHERE o.user = :user')
    ->setParameter('user', $user)  // $user is an object
    ->getQuery()->getSingleScalarResult();

This is very similar how pagination tools work, including one in Doctrine. The problem is that this will count all matches in table, thus making it pretty slow after few million rows.

And honestly; no one need something like you have 1.200.000 results at the cost of speed. But having something like there is more than 1.000 matches, please add more filters is something usable.

So I tried to limit COUNT and this worked:

SELECT COUNT(*) 
FROM (
    SELECT id FROM contract WHERE user_id="15"  LIMIT 1000
    ) AS id ;

but we can't put subquery in FROM.

Now here is the question; once I extract SQL from $qb and wrap it like in example, how do I bind object params and arrays to NativeQuery?

I tried everything for hours, probably running in circles now... SO didn't help and it is important to reduce load time.

The example is oversimplified, it is actually full of conditions based on filters user selected. But it is important to bind params like in $qb->setParameter('user', $user).

Originally created by @zmitic on GitHub (Nov 29, 2020). Consider this simplified example: ```php $count = $em->createQuery('SELECT COUNT(o.id) FROM Contract o WHERE o.user = :user') ->setParameter('user', $user) // $user is an object ->getQuery()->getSingleScalarResult(); ``` This is very similar how pagination tools work, including one in Doctrine. The problem is that this will count **all** matches in table, thus making it pretty slow after few million rows. And honestly; no one need something like ``you have 1.200.000 results`` at the cost of speed. But having something like ``there is more than 1.000 matches, please add more filters`` is something usable. So I tried to limit COUNT and this worked: ```sql SELECT COUNT(*) FROM ( SELECT id FROM contract WHERE user_id="15" LIMIT 1000 ) AS id ; ``` but we can't put [subquery in FROM](https://github.com/doctrine/orm/issues/3542). --- Now here is the question; once I extract SQL from $qb and wrap it like in example, how do I bind object params and arrays to NativeQuery? I tried everything for hours, probably running in circles now... SO didn't help and it is important to reduce load time. --- The example is oversimplified, it is actually full of conditions based on filters user selected. But it is important to bind params like in ``$qb->setParameter('user', $user)``.

admin referenced this issue

2026-01-22 16:06:14 +01:00

[PR #6575] Move performance tests to phpbench #10041

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: doctrine/archived-orm#6575