Second Level Cache support?

[itarcontact]

Hello, I'm using in my entities second level cache. Any ideas, how can i get cached results? Adding method setCacheable(true) to query builder is not possible, because returned data is scalar.

[curry684]

I think you're looking for this change that enables customizing the query builder before the query: https://github.com/omines/datatables-bundle/pull/76

[itarcontact]

I saw this event, but it's not working. Please look at the example below:

This is default queryBuilder results code

$query = $entityManager
    ->createQueryBuilder()
    ->select('r')
    ->from(Entity::class, 'r')
    ->where('r.deleted = :deleted')
    ->setParameter('deleted', false)
    ->getQuery();
$result = $query->setCacheable(true)->getResult();
// This returns cached results.

This is dataTable code:

$table = $dataTableFactory->create()
    ->add('title', TextColumn::class)
    ->add('url', TextColumn::class)
    ->add('city', TextColumn::class)
    ->createAdapter(ORMAdapter::class, [
        'entity' => Entity::class,
        'query' => function (QueryBuilder $builder) {
            $builder
                ->select('r')
                ->from(Entity::class, 'r')
                ->where('r.deleted = :deleted')
                ->setParameter('deleted', false)
            ;
        },
    ])
    ->handleRequest($request)
;
$table->addEventListener(ORMAdapterEvents::PRE_QUERY, function(ORMAdapterQueryEvent $event) {
    $event->getQuery()->setCacheable(true);
});
// This returns pure database results.

[itarcontact]

Ok, I found the solution. I created a new custom adapter that extends ORMAdapter. Only one method changed:

    protected function getResults(AdapterQuery $query): \Traversable
    {
        /** @var QueryBuilder $builder */
        $builder = $query->get('qb');
        $state = $query->getState();

        // Apply definitive view state for current 'page' of the table
        foreach ($state->getOrderBy() as list($column, $direction)) {
            /** @var AbstractColumn $column */
            if ($column->isOrderable()) {
                $builder->addOrderBy($column->getOrderField(), $direction);
            }
        }
        if ($state->getLength() > 0) {
            $builder
                ->setFirstResult($state->getStart())
                ->setMaxResults($state->getLength())
            ;
        }

        $query = $builder->getQuery();
        $event = new ORMAdapterQueryEvent($query);
        $state->getDataTable()->getEventDispatcher()->dispatch($event, ORMAdapterEvents::PRE_QUERY);

        foreach ($query->getResult() as $result) {
            yield $entity = $result;

            if (Query::HYDRATE_OBJECT === $this->hydrationMode) {
                $this->manager->detach($entity);
            }
        }
    }

In my case everythig works great.

[curry684]

I saw this event, but it's not working.

It most definitely works, you must be experiencing some other issue there.

But happy to see your problem is solved 👍

[itarcontact]

I agree, it works in default cache settings. But in my case, when I use second level cache with atomatic entity regions, this method:

$query->setCacheable(true)

do nothing.

After when I changed this code fragment:

foreach ($query->getResult() as $result) { ... }

the results began to be served from the cache which I use.

[curry684]

Hmm ok thanks for the additional info 👍