How do I limit the number of rows affected from the query?

Question

In my custom module for Drupal 8, I am using the following code.

$query = $this->connection->select('node_field_data', 'nf_data');
$result = $query->execute();

I want to get the first 500 rows. I tried with $query->limit(50), but that gives the following error.

Call to undefined method Drupal\Core\Database\Driver\mysql\Select::limit()

What is the correct code to get the first 500 rows?

Answer 1

See: https://api.drupal.org/api/drupal/core%21lib%21Drupal%21Core%21Database%21Query%21Select.php/class/Select/8.6.x

$query->range(0, 500);

Answer 2

The object returned from $this->connection->select() is an object of a class that extends \Drupal\Core\Database\Query\Select (not \Drupal\Core\Entity\Query\QueryInterface which is for entity queries). In your case, since you are using MySQL as database engine, that class specifically is \Drupal\Core\Database\Driver\mysql\Select, as the error you quoted says.

The method you are looking for is Select::range().

You are confusing it with PagerSelectExtender::limit(), used to limit the number of elements per page, and implemented from the PagerSelectExtender class. To use this method, you need to add a pager to the query, though.