Omines datatables custom query doesn't work-CodePudding

I'm using the bundle Omines to create DataTables in Symfony 5 and I've written a custom query. However, when I do so, the search in the view doesn't work.

How I can fix it?

This is my code:

Controller

$table = $dataTableFactory->create()
            ->add('fullName', TextColumn::class, [
                'label' => 'Full Name',
                'propertyPath' => '[fullName]',
                'searchable' => true,
            ])
            ->createAdapter(ORMAdapter::class, [
                'entity' => Person::class,
                'hydrate' => AbstractQuery::HYDRATE_ARRAY,
                'query' => function (QueryBuilder $builder) {
                    $builder
                        ->select('p.id, CONCAT(p.firstName, \' \', p. middleName, \' \', p.lastName) AS fullName')
                        ->from(Person::class, 'p');
                },
            ])
            ->handleRequest($request);

        if ($table->isCallback()) {
            return $table->getResponse();
        }

        return $this->render('person/index.html.twig', [
            'datatable' => $table,
        ]);

datatables.html.twig

{% extends 'base.html.twig' %}

{% block stylesheets %}
    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/v/bs5/jq-3.6.0/dt-1.11.2/b-2.0.0/fh-3.1.9/r-2.2.9/datatables.min.css"/>
{% endblock %}

{% block javascripts %}
    <script type="text/javascript" src="https://cdn.datatables.net/v/bs5/jq-3.6.0/dt-1.11.2/b-2.0.0/fh-3.1.9/r-2.2.9/datatables.min.js"></script>
    <script src="{{ asset('bundles/datatables/js/datatables.js') }}"></script>
    <script>
        $(function () {
            $('#table').initDataTables({{ datatable_settings(datatable) }}, {
                searching: true,
                fixedHeader: true,
                responsive: true,
            });
        });
    </script>
{% endblock %}

index.html.twig

{% extends 'datatables.html.twig' %}

{% block title %}List{% endblock %}

{% block body %}
    <div id="table">Loading...</div
{% endblock %}

EDIT

The search term is the fullName alias. I've tried with custom criteria_query like this:

 ->createAdapter(ORMAdapter::class, [
                'entity' => Person::class,
                'hydrate' => AbstractQuery::HYDRATE_ARRAY,
                'query' => function (QueryBuilder $builder) {
                    $builder
                        ->select('p.id, CONCAT(p.firstName, \' \', p. middleName, \' \', p.lastName) AS fullName')
                        ->from(Person::class, 'p');
                },
                'criteria' => [
                    function (QueryBuilder $builder) {
                        $builder->andWhere($builder->expr()->like('CONCAT(p.firstName, \' \', p.middleName, \' \', p.lastName)', ':fullName'))->setParameter('fullName', '%%');
                    },
                    new SearchCriteriaProvider(),
                ],
            ])

but I'm not sure how to retrieve the value of the datatable search field to be used as a parameter in '%%'

CodePudding user response：

Solution:

use Doctrine\ORM\AbstractQuery;
use Doctrine\ORM\QueryBuilder;
use Omines\DataTablesBundle\Adapter\Doctrine\ORMAdapter;
use Omines\DataTablesBundle\DataTableState;

....

->createAdapter(ORMAdapter::class, [
                'entity' => Person::class,
                'hydrate' => AbstractQuery::HYDRATE_ARRAY,
                'query' => function (QueryBuilder $builder) {
                    $builder
                        ->select('p.id, CONCAT(p.firstName, \' \', p. middleName, \' \', p.lastName) AS fullName')
                        ->from(Person::class, 'p');
                },
                'criteria' => [
                    function (QueryBuilder $builder, DataTableState $state) {
                        $term = strtolower($state->getGlobalSearch());
                        $builder->andWhere($builder->expr()->like('LOWER(CONCAT(p.firstName, \' \', p.middleName, \' \', p.lastName))', ':fullName'))->setParameter('fullName', '%' . $term . '%');
                    },
                    new SearchCriteriaProvider(),
                ],
            ])