Getting huge number of rows returns an empty array (paginator)

[jsayer101]

Hi !

I'm trying to retrive +50000 rows, as I use this :

            $lines = $rm
                ->createQueryBuilder('purchase.order.line')
                ->select(['id', 'name', 'date_planned', 'order_id', 'product_qty', 'qty_received'])
                ->where($rm->expr()->lt('date_planned', (new DateTime())->format('Y-m-d')))
                ->getQuery()
                ->count();

I get :
50795
But if I use the getResult() function I got an empty array (if I set maxResults to 100, I'll get 100 rows).

I don't know if there is an issue here or if we need to paginate ourselves the requests & responses. It would be great to have a pagination system in the Query Builder if this is the case.

Thanks !

[jsayer101]

Resolved using a while loop for myself but that would be a good improvement if the lib could paginate requests itself to retrieve a huge number of rows ! 🥇

$i = 0;
$results = [1];
$lines = [];
while (count($results) !== 0) {
    $results = $rm
        ->createQueryBuilder('purchase.order.line')
        ->search()
        ->select(['id', 'name', 'date_planned', 'order_id', 'product_qty', 'qty_received'])
        ->where($rm->expr()->lt('date_planned', (new DateTime())->format('Y-m-d')))
        ->orderBy('id')
        ->setMaxResults('12000')
        ->setFirstResult($i)
        ->getQuery()
        ->getResult();
    $i += 12000;
    foreach ($results as $result) {
        $lines[] = $result;
    }
}

I'm now getting my +50 000 rows in $lines.

[Ang3]

Hi, thank your for your feedback. We must know what is the limit for rows (or the time limit maybe).
Do you know the game where we have to find out the price? :P

I'll try to get an answer from Odoo.

[jsayer101]

Hi, thank your for your feedback. We must know what is the limit for rows (or the time limit maybe).
Do you know the game where we have to find out the price? :P

I'll try to get an answer from Odoo.

Le Juste Nombre de lignes 😄

I've tried out different requests on different models :

The only thing I know is that it is never the same limit.
I don't think it's a row limit but more a response size limit maybe? When retrieving less fields using "select" you can get more rows in the response.

Are there limits when using XMLRPC ?
Wouldn't it be more convenient to use JSONRPC api from Odoo ?

Hope we can figure this out ! 😃

[Ang3]

Resolved using a while loop for myself but that would be a good improvement if the lib could paginate requests itself to retrieve a huge number of rows ! 🥇

$i = 0;
$results = [1];
$lines = [];
while (count($results) !== 0) {
    $results = $rm
        ->createQueryBuilder('purchase.order.line')
        ->search()
        ->select(['id', 'name', 'date_planned', 'order_id', 'product_qty', 'qty_received'])
        ->where($rm->expr()->lt('date_planned', (new DateTime())->format('Y-m-d')))
        ->orderBy('id')
        ->setMaxResults('12000')
        ->setFirstResult($i)
        ->getQuery()
        ->getResult();
    $i += 12000;
    foreach ($results as $result) {
        $lines[] = $result;
    }
}

I'm now getting my +50 000 rows in $lines.

Nice, a paginator will be implemented. ;)

Three warnings about your query :

• You called search and select (the search becomes useless)
• You don't have to format the date (the expression builder makes it for you)
• The most important: records can change between two requests - I think we should search ID's before, then iterate on it to be sure about data size.

[Ang3]

#24