Error : operator does not exist datatable's search

[cyrilperard]

Hello, Sorry in advance, I use Deepl to translate.

I use Symfony 6.4 and PostGre and Datatable

I get an error when I use the datatable's search functionality, here's that error:

An exception occurred while executing a query: SQLSTATE[42883]: Undefined function: 7 ERROR: operator does not exist: timestamp without time zone ~~ unknown
LINE 1: ...'%t%' OR c0_.function LIKE '%t%' OR c0_.create_at LIKE '%t%'...
^
HINT: No operator matches the given name and argument types. You might need to add explicit type casts.

Here's how I fill my datatable in PHP :

<?php

namespace App\Datatable\customer;

use App\Entity\Customer;
use App\Entity\User;
use App\Twig\AppExtension;
use App\Twig\TranslateExtension;
use App\Twig\UserExtension;
use Doctrine\ORM\QueryBuilder;
use Omines\DataTablesBundle\Adapter\Doctrine\ORMAdapter;
use Omines\DataTablesBundle\Column\TextColumn;
use Omines\DataTablesBundle\DataTable;
use Omines\DataTablesBundle\DataTableFactory;
use Symfony\Component\HttpFoundation\Request;
use Symfony\Component\Security\Core\Authentication\Token\Storage\TokenStorageInterface;

class CustomerListDatatable
{
    public DataTable $dataTable;

    /**
     * @param DataTableFactory $dataTableFactory
     * @param TokenStorageInterface $tokenStorage
     */
    public function __construct(
        DataTableFactory      $dataTableFactory,
        TokenStorageInterface $tokenStorage,
        private AppExtension $appExtension,
        private UserExtension $userExtension,
        private TranslateExtension $translateExtension,
    )
    {
        $this->dataTableFactory = $dataTableFactory;
        $user = $tokenStorage->getToken() !== null
            ? $tokenStorage->getToken()->getUser()
            : null;
        $this->user = $user;
    }

    /**
     * @param Request $request
     * @param bool $isAdmin
     * @return DataTable
     */
    public function buildDatatable(Request $request): DataTable
    {
        $this->dataTable = $this->dataTableFactory->create()
            ->setName("customer_list")
            ->add('name', TextColumn::class, ['label' => $this->translateExtension->translate('customers.datatable.name'), 'data' => function ($context, $value) {
                return $value;
            }])
            ->add('email', TextColumn::class, ['label' => $this->translateExtension->translate('customers.datatable.email'), 'data' => function ($context, $value) {
                return $value;
            }])
            ->add('firstname', TextColumn::class, ['label' => $this->translateExtension->translate('customers.datatable.firstname'), 'data' => function ($context, $value) {
                return $value;
            }])
            ->add('lastname', TextColumn::class, ['label' => $this->translateExtension->translate('customers.datatable.lastname'), 'data' => function ($context, $value) {
                return $value;
            }])
            ->add('function', TextColumn::class, ['label' => $this->translateExtension->translate('customers.datatable.function'), 'data' => function ($context, $value) {
                return $value;
            }])
            ->add('create_at', TextColumn::class, ['label' => $this->translateExtension->translate('customers.datatable.create_at'), 'data' => function ($context, $value) {
                return $value->format("d/m/Y");
            }])
            ->add('active', TextColumn::class, ['label' => $this->translateExtension->translate('customers.datatable.active'), 'render' => function ($id, $context) {
                switch($context->isActive()){
                    case 1:
                        return '<span class="badge badge-primary">' . $this->translateExtension->translate('general.active') . '</span>';
                        break;
                    case 0:
                        return '<span class="badge badge-danger">' . $this->translateExtension->translate('general.not_active') . '</span>';
                        break;
                }
            }])
            ->add('id', TextColumn::class, [
                'render' => function ($id, $context) {
                    return '
                    <a href="/customer/show/' . $context->getid() . '" class="btn btn-icon btn-light-dark btn-sm">
                        <span class="svg-icon svg-icon-3">
                            <svg width="24" height="24" viewBox="0 0 24 24" fill="none" xmlns="http://www.w3.org/2000/svg">
                                <path d="M10 4L18 12L10 20H14L21.3 12.7C21.7 12.3 21.7 11.7 21.3 11.3L14 4H10Z" fill="currentColor"/>
                                <path opacity="0.3" d="M3 4L11 12L3 20H7L14.3 12.7C14.7 12.3 14.7 11.7 14.3 11.3L7 4H3Z" fill="currentColor"/>
                            </svg>
                        </span>
                    </a>';
                },
                'label' => "",
                'className' => "py-0 align-middle",
            ])
            ->createAdapter(ORMAdapter::class, [
                'entity' => Customer::class,
                'query' => function (QueryBuilder $builder){

                    if($this->userExtension->hasRole('ROLE_ADMIN')){
                        $builder
                            ->select('b')
                            ->from(Customer::class, 'b')
                            ->innerJoin('b.belongs_to', 'u')
                            ->innerJoin('u.user_group', 'g')
                            ->orderBy("b.id", "DESC");
                    }elseif($this->userExtension->hasRole('ROLE_LEADER')){
                        $builder
                            ->select('b')
                            ->from(Customer::class, 'b')
                            ->innerJoin('b.belongs_to', 'u')
                            ->innerJoin('u.user_group', 'g')
                            ->where('g.id = :user')
                            ->setParameter('user', $this->user->getUserGroup()->getId())
                            ->orderBy("b.id", "DESC");
                    }else{
                        $builder
                            ->select('b')
                            ->from(Customer::class, 'b')
                            ->where('b.belongs_to = :user')
                            ->setParameter('user', $this->user)
                            ->orderBy("b.id", "DESC");
                    }

                }
            ])->handleRequest($request);

        return $this->dataTable;
    }
}

Thank you in advance for your reply.

[Chris53897]

Can you change the column type to and see if this works? ->add('createdAt', DateTimeColumn::class)