Enhancement: Expand Data-Table Compatibility with Multiple Data Sources and Improve Query Flexibility

[alexandre-castelain]

Hello,

Right now, the way to create a data-table looks like this:

public function index(ProductRepository $productRepository, Request $request): Response
    {
        $query = $productRepository->createQueryBuilder('product');

        $datatable = $this->createDataTable(ProductDataTableType::class, $query);
        $datatable->handleRequest($request);

        return $this->render('home/index.html.twig', [
            'products' => $datatable->createView(),
        ]);
    }

This is cool and works well with Doctrine, allowing you to select the data you want to display by updating the query.

I would like this tool to be usable with other data sources: simple arrays, APIs, queries without ORM, etc. I believe this is also your goal in developing this tool.

I find that this approach raises several issues:

How do you modify the query based on user choices?
What data should be passed for a simple array or an API request?
What about the separation of concerns?

Shouldn't the way the data-table retrieves its data be managed by itself? We could have something like this:

public function index(ProductRepository $productRepository, Request $request): Response
    {
        $datatable = $this->createDataTable(ProductDataTableType::class, [
            ...options
        ]);
        $datatable->handleRequest($request);

        return $this->render('home/index.html.twig', [
            'products' => $datatable->createView(),
        ]);
    }

And in ProductDataTableType:

public function buildDataTable(DataTableBuilderInterface $builder, array $options): void
{
    $builder->createAdapter(OrmAdapter::class, //Or ArrayAdapter, MyApiAdapter, ...
    [
        'entity' => Employee::class,
        'query' => function (QueryBuilder $builder) {
            $builder
                ->select('e')
                ->addSelect('c')
                ->from(Employee::class, 'e')
                ->leftJoin('e.company', 'c')
            ;
        },
    ]);
}

This is heavily inspired by this bundle: Omines DataTables Bundle.

In my company, we were inspired by this bundle and customized it to create something very useful. The major downside now is that we would like to move away from jQuery.

The interesting part about this approach is that if, at some point, we can inject the "state" of the data-table into the "query," we can fully control how the data is displayed:

public function buildDataTable(DataTableBuilderInterface $builder, array $options): void
{
    $builder->createAdapter(OrmAdapter::class, //Or ArrayAdapter, MyApiAdapter, ...
    [
        'entity' => Employee::class,
        'query' => function (QueryBuilder $builder, DataTableState $state) {
            $myFilterValue = $state->getFilter('myFilter')->getValue(); //This is an example, we could get any data submitted from the front-end of the application, based on the filter or action.

            $builder
                ->select('e')
                ->addSelect('c')
                ->from(Employee::class, 'e')
                ->leftJoin('e.company', 'c')
                ->andWhere('e.firstname = :filter')->setParameter('filter', $myFilterValue)
            ;
        },
    ]);
}

I don't want to rewrite the entire bundle, of course. I've shown you my approach so far, which works very well.

But there are likely other ways to do it. What do you think?

Have a great day,

Alexandre

[Kreyu]

Hey @alexandre-castelain,

currently, instead of "adapters", this bundle has concept of "proxy queries" (like in SonataAdmin), which allows for integration with any data source. See: https://data-table-bundle.swroblewski.pl/docs/features/extensibility.html#proxy-queries

I'm planning to only add an array adapter by default (next to Doctrine ORM), so the data can be passed directly to the data table (see #63). Other sources can be integrated without problems (and with their own vendor specific filters!), and in my opinion, should be handled in a separate, extension-like bundles.

Also, the proxy query can be provided inside the data table type itself:

class ProductDataTableType extends AbstractDataTableType
{
    public function buildDataTable(DataTableBuilderInterface $builder, array $options): void
    {
        $builder->setQuery(...); // pass an instance of ProxyQueryInterface

        // so, for example, for doctrine query builder:
        $queryBuilder = ...

        $builder->setQuery(new DoctrineOrmProxyQuery($queryBuilder));
    }
}

in that case, you can omit passing the query when creating the data table:

$dataTable = $this->createDataTable(ProductDataTableType::class);

[alexandre-castelain]

Ok, thanks for this explanation. I'll dig into that to check how to use it properly !

I close the issue