How to filter based on I18n content with pagination component?

Question

I have comeup with strange problem in cakephp 3.4. I am running filter query on i18n content like this.

if($this->request->query("q")){
    $this->paginate["conditions"][$this->ContractTypes->translationField('title').' LIKE'] = '%'.$this->request->query("q").'%';
}

but following call is ending up in Database error

$records = $this->paginate($this->ContractTypes);

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'ContractTypes_title_translation.content' in 'where clause' SELECT (COUNT(*)) AS `count` FROM contract_types ContractTypes WHERE ContractTypes_title_translation.content like :c0

The paginator's count query is not joing i18n table. What is the best approach to solve this problem.

Thanks in advance,

Answer 1

I have solved this by creating my custom paginator component by editing the paginate function. My paginator contains following code incase somebody else is facing the same problem.

namespace Console\Controller\Component;
use Cake\Controller\Component\PaginatorComponent as BasePaginator;


class PaginatorComponent extends BasePaginator
{
    public function paginate($object, array $settings = [])
    {
        $query = null;
        if ($object instanceof QueryInterface) {
            $query = $object;
            $object = $query->repository();
        }

        $alias = $object->alias();
        $options = $this->mergeOptions($alias, $settings);
        $options = $this->validateSort($object, $options);
        $options = $this->checkLimit($options);

        $options += ['page' => 1, 'scope' => null];
        $options['page'] = (int)$options['page'] < 1 ? 1 : (int)$options['page'];
        list($finder, $options) = $this->_extractFinder($options);

        if (empty($query)) {
            $query = $object->find($finder, $options);
        } else {
            $query->applyOptions($options);
        }
        $cleanQuery = clone $query;
        // My Modification Starts Here
        $table      = $cleanQuery->repository();
        $results = $query->all();
        $numResults = count($results);

        $count = $numResults ? $cleanQuery->select([
            "count"=>$cleanQuery
                        ->func()
                        ->count($table->alias().'.'.$table->primaryKey())
        ])->first()->count : 0;
        // My Modification ends Here
        $defaults = $this->getDefaults($alias, $settings);
        unset($defaults[0]);

        $page = $options['page'];
        $limit = $options['limit'];
        $pageCount = (int)ceil($count / $limit);
        $requestedPage = $page;
        $page = max(min($page, $pageCount), 1);
        $request = $this->_registry->getController()->request;

        $order = (array)$options['order'];
        $sortDefault = $directionDefault = false;
        if (!empty($defaults['order']) && count($defaults['order']) == 1) {
            $sortDefault = key($defaults['order']);
            $directionDefault = current($defaults['order']);
        }

        $paging = [
            'finder' => $finder,
            'page' => $page,
            'current' => $numResults,
            'count' => $count,
            'perPage' => $limit,
            'prevPage' => $page > 1,
            'nextPage' => $count > ($page * $limit),
            'pageCount' => $pageCount,
            'sort' => key($order),
            'direction' => current($order),
            'limit' => $defaults['limit'] != $limit ? $limit : null,
            'sortDefault' => $sortDefault,
            'directionDefault' => $directionDefault,
            'scope' => $options['scope'],
        ];

        if (!$request->getParam('paging')) {
            $request->params['paging'] = [];
        }
        $request->params['paging'] = [$alias => $paging] + (array)$request->getParam('paging');

        if ($requestedPage > $page) {
            throw new NotFoundException();
        }

        return $results;
    }
}