Array source

[easis]

Hi! I'm trying to build a datatable with an array as a source. My goal is to allow the users to filter some values (select one or more employees from a list, date rangee and some other checkboxes) and perform some queries to gather data, do some business logic and present the results in the datatable.

I did not find any example in the docs about how to this and actually don't know if this is possible.

[Kreyu]

Hey. For now, only Doctrine ORM queries are supported, although I want it to be supported by default as well. Creating an array adapter would require adding a ProxyQuery class accepting arrays and handling pagination and sorting.

[easis]

Ok, I'll wait. Thanks!

[davidromani]

I'm trying to achieve same goal. Hope can this steps can help you @easis

In my case, I have a custom Doctrine query (from WorkOrderResultRepository) that obtains an array result (plain associative array).

// Controller
    public function customDatatableAction(Request $request, WorkOrderResultRepository $wor): Response
    {
        $data = $wor->batchAnalysis();
        /**
            [
              [
                "batchCode" => "2416",
                "totalWorkstationSeconds" => "815448"
               ],
               [...]
            ]
        */
        $batchAnalysisArrayProxyQuery = new ArrayProxyQuery(
            data: $data,
            page: $request->query->has('page_batch_analysis') ? (int) $request->query->get('page_batch_analysis') : 1
        );
        $dataTable = $this->createDataTable(BatchAnalysisDataTableType::class, $batchAnalysisArrayProxyQuery);
        $dataTable->handleRequest($request);
        if ($dataTable->isExporting()) {
            return $this->file($dataTable->export());
        }

        return $this->render('@App/admin/reports/batch_analysis.html.twig', [
            'batchAnalysisTable' => $dataTable->createView(),
        ]);
    }

Then I've created a custom ArrayProxyQuery with this code:

<?php

namespace App\Datatable\Query;

use App\Doctrine\Enum\SortOrderEnum;
use Kreyu\Bundle\DataTableBundle\Filter\Filter;
use Kreyu\Bundle\DataTableBundle\Filter\FilterData;
use Kreyu\Bundle\DataTableBundle\Pagination\PaginationData;
use Kreyu\Bundle\DataTableBundle\Query\ResultSet;
use Kreyu\Bundle\DataTableBundle\Sorting\SortingData;
use Kreyu\Bundle\DataTableBundle\Query\ProxyQueryInterface;
use Kreyu\Bundle\DataTableBundle\Query\ResultSetInterface;

final class ArrayProxyQuery implements ProxyQueryInterface
{
    public function __construct(
        private array $data,
        private readonly int $page = 1,
    ) {
    }

    public function sort(SortingData $sortingData): void
    {
        foreach ($sortingData->getColumns() as $column) {
            usort($this->data, function ($a, $b) use ($column) {
                if ($column->getDirection() === strtolower(SortOrderEnum::ASC->value)) {
                    return ($a[$column->getName()] < $b[$column->getName()]) ? -1 : 1;
                } else {
                    return ($a[$column->getName()] > $b[$column->getName()]) ? -1 : 1;
                }
            });
        }
    }

    public function paginate(PaginationData $paginationData): void
    {
        if ($this->page > 1) {
            $total = count($this->data);
            $limit = $paginationData->getPerPage();
            $totalPages = ceil($total / $limit);
            $page = max($this->page, 1);
            $page = min($page, $totalPages);
            $offset = ($page - 1) * $limit;
            if( $offset < 0 ) {
                $offset = 0;
            }
            $this->data = array_slice($this->data, $offset, $limit);
        }
    }

    public function getResult(): ResultSetInterface
    {
        return new ResultSet(
            iterator: new \ArrayIterator(iterator_to_array($this->data)),
            currentPageItemCount: $this->page,
            totalItemCount: count($this->data),
        );
    }

    public function filterDataBy(FilterData $filterData, Filter $filter): void
    {
        $this->data = array_filter($this->data, function ($item) use ($filterData, $filter) {
            return $item[$filter->getConfig()->getName()] === $filterData->getValue();
        });
    }
}

Finally, I've implemented my own BatchAnalysisDataTableType like this:

<?php

namespace App\Datatable\Type;

use App\Datatable\Query\ArrayProxyQuery;
use App\Doctrine\Enum\SortOrderEnum;
use Kreyu\Bundle\DataTableBundle\Filter\FilterData;
use Kreyu\Bundle\DataTableBundle\Filter\FilterInterface;
use Kreyu\Bundle\DataTableBundle\Filter\Operator;
use Kreyu\Bundle\DataTableBundle\Filter\Type\CallbackFilterType;
use Kreyu\Bundle\DataTableBundle\Bridge\PhpSpreadsheet\Exporter\Type\CsvExporterType;
use Kreyu\Bundle\DataTableBundle\Bridge\PhpSpreadsheet\Exporter\Type\XlsxExporterType;
use Kreyu\Bundle\DataTableBundle\Column\Type\NumberColumnType;
use Kreyu\Bundle\DataTableBundle\Column\Type\TextColumnType;
use Kreyu\Bundle\DataTableBundle\Pagination\PaginationData;
use Kreyu\Bundle\DataTableBundle\Sorting\SortingData;
use Kreyu\Bundle\DataTableBundle\Type\AbstractDataTableType;
use Kreyu\Bundle\DataTableBundle\DataTableBuilderInterface;
use Symfony\Component\OptionsResolver\OptionsResolver;

class BatchAnalysisDataTableType extends AbstractDataTableType
{
    public function buildDataTable(DataTableBuilderInterface $builder, array $options): void
    {
        $builder
            ->addColumn(
                'batchCode',
                TextColumnType::class,
                [
                    'label' => 'batchCode',
                    'property_path' => '[batchCode]',
                    'sort' => true,
                    'export' => true,
                ]
            )
            ->addColumn(
                'totalWorkstationSeconds',
                NumberColumnType::class,
                [
                    'label' => 'totalWorkstationSeconds',
                    'property_path' => '[totalWorkstationSeconds]',
                    'sort' => true,
                    'export' => true,
                ]
            )
            ->addFilter(
                'batchCode',
                CallbackFilterType::class,
                [
                    'default_operator' => Operator::Equals,
                    'callback' => function (ArrayProxyQuery $query, FilterData $data, FilterInterface $filter): void {
                        $query->filterDataBy($data, $filter);
                    },
                ]
            )
            ->addFilter(
                'totalWorkstationSeconds',
                CallbackFilterType::class,
                [
                    'default_operator' => Operator::Equals,
                    'callback' => function (ArrayProxyQuery $query, FilterData $data, FilterInterface $filter): void {
                        $query->filterDataBy($data, $filter);
                    },
                ]
            )
            ->addExporter('csv', CsvExporterType::class)
            ->addExporter('xlsx', XlsxExporterType::class)
            ->setDefaultSortingData(SortingData::fromArray([
                'batchCode' => strtolower(SortOrderEnum::ASC->value),
            ]))
        ;
    }

    public function configureOptions(OptionsResolver $resolver): void
    {
        $resolver->setDefaults([
            'filtration_enabled' => true,
        ]);
    }
}

[Kreyu]

At last, a simple ArrayProxyQuery is now available in the 0.23 version of the bundle. Usage explained in the documentation.