Querying large number of entities via primary key criteria #5543

New Issue

admin · 2026-01-22T15:10:37+01:00

admin commented

2026-01-22 15:10:37 +01:00

Originally created by @yasminalzokari on GitHub (May 19, 2017).

Originally assigned to: @Ocramius on GitHub.

We have a problem querying a large number (i.e. more than 1000) of entities via criteria on their primary key which fields due to expanding list parameters with more than 1000 expressions, i.e. it yields the Oracle error

ORA-01795: maximum number of expressions in a list is 1000

We would have expected that DBAL takes care of this, but it looks like this is considered to much of a corner case. Hence we tried too implement a workaround on the repository level by issuing several calls to the underlying repositories findBy method and merging their results after chunking up the list parameters, e.g.:

public function findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null)
{
    $repository = $this->getRepository();
    return self::chunkListParameters($criteria, function ($clause) use ($repository, $orderBy, $limit, $offset) {
        return $repository->findBy($clause, $orderBy, $limit, $offset);
    });
}

private static function chunkListParameters(array $criteria, callable $query, $size = 1000)
{
    $clauses = [[]];
    $newClauses = [];
    foreach ($criteria as $parameter => $values) {
        foreach ($clauses as $clause) {
            if (is_array($values) && count($values) > $size) {
                foreach (array_chunk($values, $size) as $chunk) {
                    $newClause = $clause;
                    $newClause[$parameter] = $chunk;
                    $newClauses[] = $newClause;
                }
            } else {
                $clause[$parameter] = $values;
                $newClauses[] = $clause;
            }
        }
        $clauses = $newClauses;
        $newClauses = [];
    }

    if (count($clauses) == 1) {
        return $query($clauses[0]);
    } else {
        return array_reduce(array_map($query, $clauses), "array_merge", []);
    }
}

Do think that something like this could become part of the upstream implementation?

Originally created by @yasminalzokari on GitHub (May 19, 2017). Originally assigned to: @Ocramius on GitHub. We have a problem querying a large number (i.e. more than 1000) of entities via criteria on their primary key which fields due to expanding list parameters with more than 1000 expressions, i.e. it yields the Oracle error ``` ORA-01795: maximum number of expressions in a list is 1000 ``` We would have expected that DBAL takes care of this, but it looks like this is [considered to much of a corner case](https://github.com/doctrine/dbal/pull/2699#issuecomment-294801322). Hence we tried too implement a workaround on the repository level by issuing several calls to the underlying repositories `findBy` method and merging their results after chunking up the list parameters, e.g.: ``` public function findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null) { $repository = $this->getRepository(); return self::chunkListParameters($criteria, function ($clause) use ($repository, $orderBy, $limit, $offset) { return $repository->findBy($clause, $orderBy, $limit, $offset); }); } private static function chunkListParameters(array $criteria, callable $query, $size = 1000) { $clauses = [[]]; $newClauses = []; foreach ($criteria as $parameter => $values) { foreach ($clauses as $clause) { if (is_array($values) && count($values) > $size) { foreach (array_chunk($values, $size) as $chunk) { $newClause = $clause; $newClause[$parameter] = $chunk; $newClauses[] = $newClause; } } else { $clause[$parameter] = $values; $newClauses[] = $clause; } } $clauses = $newClauses; $newClauses = []; } if (count($clauses) == 1) { return $query($clauses[0]); } else { return array_reduce(array_map($query, $clauses), "array_merge", []); } } ``` Do think that something like this could become part of the upstream implementation?

admin added the Improvement Won't Fix Duplicate labels 2026-01-22 15:10:37 +01:00

admin closed this issue

2026-01-22 15:10:37 +01:00

admin commented

2026-01-22 15:10:38 +01:00

@Ocramius commented on GitHub (May 20, 2017):

Closing as duplicate of https://github.com/doctrine/dbal/pull/2699

@Ocramius commented on GitHub (May 20, 2017): Closing as duplicate of https://github.com/doctrine/dbal/pull/2699

Sign in to join this conversation.