Google-cloud-php: BigQuery: maxResults not working in $job->queryResults

[davidbielik]

I can't get the maxResults option to work and have tried adding it to a few different places ($job->queryResults($options), inside $options['jobConfig'], inside $queryResults->reload($options) but no luck.

Am I doing something wrong here?

$job = $bigQuery->job($jobId); // assume $job->isComplete() === true
$options = ['maxResults' => 50];
$queryResults = $job->queryResults($options);
$count = 0;
foreach ($queryResults->rows() as $i => $row) {
    ++count;
}

Expected: $count should equal maxResults (50)
Actual: $count equals $job->info()['totalRows']

[dwsupplee]

Hey @davidbielik!

You're actually spot on with your usage of maxResults! :)

$queryResults->rows() is returning a generator that is keeping track of page tokens and iterating through the result set for you. The maxResults option is used in each of the individual requests that the generator manages.

To explain a bit further, let's assume you have a set of 100 total results - by setting maxResults to 50 you are telling the generator to make two requests in total to get the entire result set of 100.

I definitely see where the room for confusion is and we will update our documentation accordingly.

In order to achieve your goal of getting the 50 results, you can do one of two things:

1. Set a limit clause in your query.
2. Break out of the loop once you have reached the 50th result (this will prevent further requests from being made by the generator).

$queryResults = $job->queryResults(['maxResults' => 50]);

foreach ($queryResults->rows() as $i => $row) {
    var_dump($row);

    if ($i === 49) {
        break;
    }
}

[davidbielik]

Hi @dwsupplee Thanks for the quick reply!

The number of results I pull varies so to benefit from free cached requests I didn't want to go changing the limit clause (unless limits aren't included in the decision to cache?).

I was hesitant to simply break from the loop since I assumed that would request all of the data and didn't know it handled pageTokens automatically. That's very useful to know and a really nice feature!

Thanks again! Really enjoying working with this library :)

[dwsupplee]

No problem, really glad to hear that!

If you don't mind, I'm going to keep this issue open as a way to track updating the documentation of maxResults. :)

[dwsupplee]

This was closed out by: https://github.com/GoogleCloudPlatform/google-cloud-php/pull/399

[lesterjanpearson]

So using maxresults is not the way to do pagination? like in mysql you have the offset,limit. where you can skip rows for another page.

[dwsupplee]

@lesterjanpearson maxResults is indeed used for pagination, it determines how many results are returned per page.

[lesterjanpearson]

function run_query_as_job($query, $maxResults = 10, $startIndex = 0)
{
    $options = [
        'maxResults' => $maxResults,
        'startIndex' => $startIndex
    ];
    $bigQuery = new BigQueryClient([
        'projectId' => 'xx',
        'keyFilePath' => 'xx-21c721cefe2c.json'
    ]);
    $job = $bigQuery->runQueryAsJob(
        $query,
        ['jobConfig' => ['useLegacySql' => true]]);

    $backoff = new ExponentialBackoff(10);
    $backoff->execute(function () use ($job) {
        print('Waiting for job to complete' . PHP_EOL);
        $job->reload();
        if (!$job->isComplete()) {
            throw new Exception('Job has not yet completed', 500);
        }
    });
    $queryResults = $job->queryResults($options);

    if ($queryResults->isComplete()) {
        $i = 0;
        $rows = $queryResults->rows($options);
        foreach ($rows as $row) {
            printf('--- Row %s ---' . "<br>", ++$i);
            echo $row['account_id']."<br><br>";
            if($i==$maxResults) break;
        }
        printf('Found %s row(s)' . "<br>", $i);


    } else {
        throw new Exception('The query failed to complete');
    }
}

@dwsupplee Im using this function but it still returns me 1m rows.

[dwsupplee]

Interesting. I ran an example locally very similar to yours and it was breaking out early - however, there may be a simpler way to achieve what you're looking for now with #399.

If your goal is to only print out information from the first page of results:

if ($queryResults->isComplete()) {
    $pageIterator = $queryResults->rows($options)
        ->iterateByPage();

    foreach ($pageIterator->current() as $row) {
        echo $row['account_id'] . "<br><br>";
    }
}

If possible, I would recommend using a LIMIT clause.

[lesterjanpearson]

@dwsupplee my goal is to do a pagination, exactly how mysql or mongodb does it. for example I have 100 rows.
on my web page, I want to display 5 at time. so it gives me 20 buttons from 1-20, when I click page 2. it will display the rows 6-10. if doing it realistically I have 1m rows, displaying 50 per pages. I can really put LIMIT on the query, is there a skip,offset function on bigquery like how mysql is?

Ihave this setup on mysql now, but we are uploading our data bigquery.

What is the proper syntax for this:

{ select | ( query_expr ) | query_expr set_op query_expr }
[ ORDER BY expression [{ ASC | DESC }] [, ...] ]
[ LIMIT count [ OFFSET skip_rows ] ]

SELECT * FROM table ORDER BY date DESC LIMIT 10,[OFFSET]?

[dwsupplee]

Gotcha! Per the Query Syntax Documentation both LIMIT and OFFSET are indeed supported.

If it fits better for your use case, you can also use a combination of the pageIterator (as outlined in my above example) and startIndex as well.

[lesterjanpearson]

I tried these, but doesnt seem to work.
SELECT * FROM table ORDER BY date DESC LIMIT 10[20]
SELECT * FROM table ORDER BY date DESC LIMIT 10[OFFSET 20]
SELECT * FROM table ORDER BY date DESC LIMIT 20

[dwsupplee]

Does this work?

SELECT * FROM table ORDER BY date DESC LIMIT 10 OFFSET 20

[lesterjanpearson]

thanks

[dwsupplee]

No problem!

[lesterjanpearson]

I need to verify the results first if they do skip and limit, or limit and skip order.