How can I limit my database request according to the pagination of the datatable?

Question

I am loading data from my database, which is a very high amount of data. To get a real fast performace, I limit the data (with my parts function)

Controller.php

$table = $this->em->getRepository($EntityName)->parts(10);
$table_json = $serializer->serialize($table, 'json');
$table_array = json_decode($table_json);
// here I am adding some complex data to the array from other database tables and then I encode the array again for the datatable
$data = json_encode($table_array);

page.html.twig:

  var table = $('.table').DataTable({
    "lengthMenu": [[10, 25, 50,100, -1], [10, 25, 50,100, "All"]],
    "pageLength": 10,
    "scrollX": true,
    "data":{{ output.data|raw }},

parts function:

 public function parts($limit) {
      return $this->createQueryBuilder('documents')
      ->setMaxResults($limit)
      ->getQuery()
      ->execute();
    }

What I wanted to know, is there any chance that depending on the pagination I am loading only 10 records from my database? So pagination 1 to 10 would load the first 10 records and 11 to 20 loads the next 10 records. I was researching a lot and I did not find a solution that suits to my specific code. Its because after the loading from the database I need to do something with the records before I show it in the datatable.

Answer 1

public function parts($offset, $limit) {
    return $this->createQueryBuilder('documents')
      ->setFirstResult($offset)
      ->setMaxResults($limit)
      ->getQuery()
      ->getResult();
}

But i recommend to use the KnpPaginatorBundle