Pagination bug

[aniskasmi]

Hello,

I have a problem with my results, I have a filtering by year active 'See my find later', When I refresh my page I should see all my projects in 2023 but I am forced to go to the 5th page to see all my results, from page 1 to 4 I have only one line with a result

And the counter at the bottom right too, it tells me that there are 48 results but I have only 6 results in 2023 and isActive = 1

<?php

namespace App\Http\DataTable;

use App\Domain\Auth\User;
use App\Domain\Project\Entity\ProjectReference;
use App\Domain\Project\Entity\ProjectRessource;
use App\Domain\Project\Repository\ProjectRepository;
use DataTables\DataTableHandlerInterface;
use DataTables\DataTableQuery;
use DataTables\DataTableResults;
use Doctrine\ORM\Query\Expr\Join;
use Psr\Container\ContainerInterface;
use Symfony\Component\HttpFoundation\Request;
use Symfony\Component\Routing\Router;

class ProjectDataTable implements DataTableHandlerInterface
{
    public function __construct(
        private readonly ContainerInterface $container,
        private readonly ProjectRepository $repository,
    )
    {
    }

    /**
     * {@inheritdoc}
     */
    public function handle(DataTableQuery $request, array $context = []): DataTableResults
    {
        $results = new DataTableResults();

        $isActive = 1;
        if($context['displayDelete']) {
            $isActive = 0;
        }

        // Query to get requested entities.
        $query = $this->repository->createQueryBuilder('p')
            ->leftJoin('p.customer', 'user')
            ->leftJoin('p.ressources', 'ressource')
            ->where('YEAR(p.addedAt) = :year')
            ->andWhere('p.isActive = :isActive')
            ->setParameter('year', $context['year'])
            ->setParameter('isActive', $isActive)
        ;

        // Total number of project.
        $count = clone $query;
        $count->select('COUNT(p.id)');
        $results->recordsTotal = $count->getQuery()->getSingleScalarResult();

        // Search.
        if ($request->search->value) {
            $query
                ->orWhere('p.serial_number LIKE :search')
                ->orWhere('p.status LIKE :search')
                ->orWhere('p.price_real LIKE :search')
                ->orWhere('p.price_reimbursed LIKE :search')
                ->orWhere('p.order_number LIKE :search')
                ->orWhere('ressource.title LIKE :search')
                ->orWhere('user.name LIKE :search')
                ;
            $query->setParameter('search', "%{$request->search->value}%");
        }

        // Get filtered count.
        $queryCount = clone $query;
        $queryCount->select('COUNT(p.id)');
        $results->recordsFiltered = $queryCount->getQuery()->getSingleScalarResult();

        // Restrict results.
        $query->setMaxResults($request->length);
        $query->setFirstResult($request->start);

        foreach ($request->columns as $column) {
            if ($column->search->value) {
                $value = strtolower($column->search->value);
                // "Login" column
                if ($column->data == 2) {
                    dump('HIT');
                    $query->andWhere('p.serial_number LIKE :search');
                    $query->setParameter('search', "%{$value}%");
                }
                if ($column->data == 3) {
                    dump('HIT x2');
                    $query->andWhere('p.order_number LIKE :search');
                    $query->setParameter('search', "%{$value}%");
                }
            }
        }

        $projects = $query->getQuery()->getResult();

        foreach ($projects as $project) {
            $results->data[] = [
                $project->getCustomer()->getName(),
                $project->getReference(),
                $project->getSerialNumber(),
                $project->getOrderNumber(),
                $project->getReceptionDate()->format('d/m/Y'),
                $project->getRepairDate()->format('d/m/Y'),
                $project->getBreakDate()->format('d/m/Y'),
                $project->getReceptionDelay(),
                $project->getRepairDateLabel(),
                $project->getStatus(true),
                '<span class="badge rounded-pill bg-danger fs-12">'. $project->getWarrantyType(true) .'</span>',
                $project->getPriceOT(),
                $project->getPriceRefund(),
                $this->container->get('twig')->render('admin/projects/_datatable-actions.html.twig', ['project' => $project])
            ];
        }

        return $results;
    }
}

Thx

[aniskasmi]

Ok in dd() query I saw that my leftJoin creates problems, the COUNT made the accountant with all the resources of the project and the users as soon as I removed it from the first query it works perfectly, I problem is that the user must be able to search from a resource name or a customer name

@webinarium I fix every issues with innerJoin but i have 1 last bug

I have 2400 entries and only one Page on pagination menu i don't understand..

The problem come from leftJoin or InnerJoin when i comment this two lines, everything work perfecty, but user can search by ressource name per example

$query = $this->repository->createQueryBuilder('p')
            //->leftJoin(User::class, 'customer', Join::WITH, 'customer.id = p.customer')
            //->leftJoin(ProjectRessource::class, 'ressource' , Join::WITH, 'ressource.project = p.id')
            ->andWhere('YEAR(p.addedAt) = :year')
            ->andWhere('p.isActive = :isActive')
            ->setParameter('year', $context['year'])
            ->setParameter('isActive', $isActive)
        ;

When i put the $query->leftJoin after recordsTotal is work perfect on first loading, but when i want to made an search error appear, and pagination don't work only 1 page for 50 entries

[webinarium]

Sorry, I can't really help without knowing more about your project, like DB schema, actual data expected, and so on. But I quickly reviewed the code you provided and found a couple of things which look like bugs (and if they are, it could explain the current behavior).

1. You get "filtered count" before you actually filter the data. Please check the sequence of your code parts with the snippet from the docs.

2. You use the same query parameter for global search and for all your columns - it's named search. Actually, it's three different parameters and they must be named differently.

Hope it helps. If not and you still need an assistance, I will be happy to help if you provide me with more details. Ideally, it could be a complete project that I can run locally + a DB dump to be run against. If not, I would need at least the following:

• your handler code (I believe you changed it, so the above one is outdated, right?),
• your jQuery DataTables plugin initialization,
• your database schema description,
• complete description of the issue (what was expected and what was the actual result),
• complete step-by-step scenario of how to reproduce the issue,
• the copy of the request's payload and the response data (can be a screenshot from the "Network" tab of your browser's console).

[aniskasmi]

@webinarium Thx for reply i fix the filtered count place on handle

Handle :

public function handle(DataTableQuery $request, array $context = []): DataTableResults
    {
        $results = new DataTableResults();

        $isActive = 1;
        if($context['displayDelete']) {
            $isActive = 0;
        }

        // Query to get requested entities.
        $query = $this->repository->createQueryBuilder('p')
            //->leftJoin(User::class, 'customer', Join::WITH, 'customer.id = p.customer')
            //->leftJoin(ProjectRessource::class, 'ressource' , Join::WITH, 'ressource.project = p.id')
            ->where('YEAR(p.addedAt) = :year')
            ->andWhere('p.isActive = :isActive')
            ->setParameter('year', $context['year'])
            ->setParameter('isActive', $isActive)
        ;

        // Total number of project.
        $count = clone $query;
        $count->select('COUNT(p.id)');
        $results->recordsTotal = $count->getQuery()->getSingleScalarResult();

        // Search.
        if ($request->search->value) {
            $query
                ->andWhere(
                    '(LOWER(p.reference) LIKE :search OR' .
                    ' LOWER(p.order_number) LIKE :search OR' .
                    ' LOWER(p.cnhClaim) LIKE :search OR' .
                    ' LOWER(p.comment) LIKE :search OR' .
                    ' LOWER(ressource.title) LIKE :search OR' .
                    ' LOWER(customer.name) LIKE :search OR' .
                    ' LOWER(p.warranty_type) LIKE :search OR' .
                    ' LOWER(p.serial_number) LIKE :search)'
                );
            $query->setParameter('search', strtolower("%{$request->search->value}%"));
        }

        foreach ($request->columns as $column) {
            if ($column->search->value) {
                $value = strtolower($column->search->value);
                if ($column->data == 1) {
                    $query
                        ->andWhere('LOWER(customer.name) LIKE :customer')
                        ->setParameter('customer', "%{$value}%");
                }
                if ($column->data == 2) {
                    $query
                        ->andWhere('LOWER(p.reference) LIKE :reference')
                        ->setParameter('reference', "%{$value}%");
                }
                if ($column->data == 3) {
                    $query
                        ->andWhere('LOWER(p.serial_number) LIKE :serialNumber')
                        ->setParameter('serialNumber', "%{$value}%");
                }
                if ($column->data == 4) {
                    $query
                        ->andWhere('LOWER(p.order_number) LIKE :orderNumber')
                        ->setParameter('orderNumber', "%{$value}%");
                }
                if ($column->data == 5) {
                    $query
                        ->andWhere('LOWER(p.break_date) LIKE :breakDate')
                        ->setParameter('breakDate', "%{$value}%");
                }
                if ($column->data == 6) {
                    $query
                        ->andWhere('LOWER(p.repair_date) LIKE :repairDate')
                        ->setParameter('repairDate', "%{$value}%");
                }
                if ($column->data == 7) {
                    $query
                        ->andWhere('LOWER(p.reception_date) LIKE :receptionDate')
                        ->setParameter('receptionDate', "%{$value}%");
                }
                if ($column->data == 10) {
                    $query
                        ->andWhere('LOWER(p.status) LIKE :status')
                        ->setParameter('status', "%{$value}%");
                }
                if ($column->data == 11) {
                    $query
                        ->andWhere('LOWER(p.warranty_type) LIKE :warrantyType')
                        ->setParameter('warrantyType', "%{$value}%");
                }
            }
        }

        // Get filtered count.
        $queryCount = clone $query;
        $queryCount->select('COUNT(p.id)');
        $results->recordsFiltered = $queryCount->getQuery()->getScalarResult();

        // Restrict results.
        $query->setMaxResults($request->length);
        $query->setFirstResult($request->start);

        // Order.
        foreach ($request->order as $order) {
            if ($order->column == 1) {
                $query->addOrderBy('customer.name', $order->dir);
            }
            if ($order->column == 2) {
                $query->addOrderBy('p.reference', $order->dir);
            }
            if ($order->column == 3) {
                $query->addOrderBy('p.serial_number', $order->dir);
            }
            if ($order->column == 4) {
                $query->addOrderBy('p.order_number', $order->dir);
            }
            if ($order->column == 5) {
                $query->addOrderBy('p.break_date', $order->dir);
            }
            if ($order->column == 6) {
                $query->addOrderBy('p.repair_date', $order->dir);
            }
            if ($order->column == 7) {
                $query->addOrderBy('p.reception_date', $order->dir);
            }
            if ($order->column == 8) {
                $query->addOrderBy('DATE_DIFF(p.repair_date, p.reception_date)', $order->dir);
            }
            if ($order->column == 9) {
                $query->addOrderBy('DATE_DIFF(NOW(), p.repair_date)', $order->dir);
            }
            if ($order->column == 10) {
                $query->addOrderBy('p.status', $order->dir);
            }
            if ($order->column == 11) {
                $query->addOrderBy('p.warranty_type', $order->dir);
            }
        }

        $projects = $query->getQuery()->getResult();

        foreach ($projects as $project) {
            $results->data[] = [
                $this->container->get('twig')->render('project/_datatable-actions.html.twig', ['project' => $project]),
                $project->getCustomer()->getName(),
                $project->getReference(),
                $project->getSerialNumber(),
                $project->getOrderNumber(),
                $project->getReceptionDate()->format('d/m/Y'),
                $project->getRepairDate()->format('d/m/Y'),
                $project->getBreakDate()->format('d/m/Y'),
                $project->getReceptionDelay(),
                $project->getRepairDateLabel(),
                $project->getStatus(true),
                '<span class="badge rounded-pill bg-danger fs-12">'. $project->getWarrantyType(true) .'</span>',
                $project->getPriceOT(),
                $project->getPriceRefund()
            ];
        }

        return $results;
    }

Jquery Init :

<script>
        $('#data-table-sse tfoot th').each(function () {
            var title = $(this).text();
            $(this).html('<input type="text" style="width: 100% !important;" placeholder="" />');
        });
        var table = $('#data-table-sse').DataTable({
            serverSide: true,
            ajax: '{{ path('datatable_project', {
                'year': selectYear,
                'displayDelete': displayDelete,
            }) }}',
            initComplete: function () {
                // Apply the search
                this.api()
                    .columns()
                    .every(function () {
                        var that = this;

                        $('input', this.footer()).on('keyup change clear', function () {
                            if (that.search() !== this.value) {
                                that.search(this.value).draw();
                            }
                        });
                    });
            },
        });
</script>

My DataTableShema :

I have project table with all information of project, but user can add some files to project like pdf, or png the ressource is save on project_ressource table linked with RelationShip, on ressource we have title and on datatable display all project user want to find an project with title of ressource.

My Paylout:

year: 
2023
amp;displayDelete: 
0
draw: 
1
columns[0][data]: 
0
columns[0][name]: 
columns[0][searchable]: 
true
columns[0][orderable]: 
true
columns[0][search][value]: 
columns[0][search][regex]: 
false
columns[1][data]: 
1
columns[1][name]: 
columns[1][searchable]: 
true
columns[1][orderable]: 
true
columns[1][search][value]: 
columns[1][search][regex]: 
false
columns[2][data]: 
2
columns[2][name]: 
columns[2][searchable]: 
true
columns[2][orderable]: 
true
columns[2][search][value]: 
columns[2][search][regex]: 
false
columns[3][data]: 
3
columns[3][name]: 
columns[3][searchable]: 
true
columns[3][orderable]: 
true
columns[3][search][value]: 
columns[3][search][regex]: 
false
columns[4][data]: 
4
columns[4][name]: 
columns[4][searchable]: 
true
columns[4][orderable]: 
true
columns[4][search][value]: 
columns[4][search][regex]: 
false
columns[5][data]: 
5
columns[5][name]: 
columns[5][searchable]: 
true
columns[5][orderable]: 
true
columns[5][search][value]: 
columns[5][search][regex]: 
false
columns[6][data]: 
6
columns[6][name]: 
columns[6][searchable]: 
true
columns[6][orderable]: 
true
columns[6][search][value]: 
columns[6][search][regex]: 
false
columns[7][data]: 
7
columns[7][name]: 
columns[7][searchable]: 
true
columns[7][orderable]: 
true
columns[7][search][value]: 
columns[7][search][regex]: 
false
columns[8][data]: 
8
columns[8][name]: 
columns[8][searchable]: 
true
columns[8][orderable]: 
true
columns[8][search][value]: 
columns[8][search][regex]: 
false
columns[9][data]: 
9
columns[9][name]: 
columns[9][searchable]: 
true
columns[9][orderable]: 
true
columns[9][search][value]: 
columns[9][search][regex]: 
false
columns[10][data]: 
10
columns[10][name]: 
columns[10][searchable]: 
true
columns[10][orderable]: 
true
columns[10][search][value]: 
columns[10][search][regex]: 
false
columns[11][data]: 
11
columns[11][name]: 
columns[11][searchable]: 
true
columns[11][orderable]: 
true
columns[11][search][value]: 
columns[11][search][regex]: 
false
columns[12][data]: 
12
columns[12][name]: 
columns[12][searchable]: 
true
columns[12][orderable]: 
true
columns[12][search][value]: 
columns[12][search][regex]: 
false
columns[13][data]: 
13
columns[13][name]: 
columns[13][searchable]: 
true
columns[13][orderable]: 
true
columns[13][search][value]: 
columns[13][search][regex]: 
false
order[0][column]: 
0
order[0][dir]: 
asc
start: 
0
length: 
10
search[value]: 
search[regex]: 
false
_: 
1674310151362

------

data
: 
[[,…], [,…], [,…], [,…]]
0
: 
[,…]
1
: 
[,…]
2
: 
[,…]
3
: 
[,…]
draw
: 
1
recordsFiltered
: 
1
recordsTotal
: 
54

My issue:

My problem is simply that I want to be able to search in relations (Example by file name and find the projects with this file name) I have made good progress to understand the handle, but I am now blocked because I have many possible filters and I can not see where the problem comes from. My filtering works well, by column or global but now for example I have 48 projects in 2023 active in database when I arrive on the page only 9 are visible and I have no more pagination menu to navigate among the projects

I removed all the filtering to have the simplest handle possible I limited my maxresults to 2 but my pagination still doesn't work

[webinarium]

Well, I expected a SQL dump for a "database schema description" rather than literally a description of it. :) Anyway, there is still one more suspicious thing in your handler which may cause the issue.

If you take a closer look at the response you provided, you will see that this particular request returns 4 records filtered out of 54 total. So, the recordsFiltered is supposed to be at least 4, but it equals to 1. This may "confuse" the DataTables plugin, so the pagination just got broken.

Now, why there is 1? When you are counting number of filtered records, you use getScalarResult instead of getSingleScalarResult. As far as I understand, the getScalarResult returns you an array, which later (inside the bundle) typed into (int). I believe this is why you (always!) see 1 in the response. Interesting fact - the first version of your handler (which was provided in this issue) doesn't contain this bug. ;)

If you fix the bug above and still have issues, please take an extra mile and try to analyse the situation before seeking for a help:

• check the response and understand is there anything different to what was expected,
• run the request using a step-by-step PHP debugger and go through the handler to see where exactly the response data becomes wrong.

Good luck!

[aniskasmi]

Thank you very much for your feedback! With autocomplete of PHPStorm I did not see that it was not the good function . The pagination works again, I thank you very much for your time and feedback that will allow me to analyze better next time