Filter product by different tables - Cakephp 3.8

Need Help
1

Friends,
I appreciate if anyone can help me.

I created a field to filter products by brand, state and city after entering a keyword. When I have the search input and the brand field works well. However, when I add more fields, such as state and city he does not seek.

ProductsTable.php

.
$this->belongsTo('Users', [
       'foreignKey' => 'user',
       'joinType' => 'INNER',
]);
.

UsersTable.php

$this->hasMany('Products', [
       'foreignKey' => 'user_id',
]);

Finally, I do the method to filter like this:

public function search()
    {           
        $this->viewBuilder()->setLayout('search');

        $product = $this->paginate = [
            'contain' => ['Brand', 'User'],
            'conditions' => ['and' => [
                'Products.name like' => '%'. $this->request->getQuery('search') . '%',
                'Brands.id like' => '%'.$this->request->getQuery('brand').'%',
                'Users.state like' => '%'.$this->request->getQuery('estate').'%',
                'Users.city like' => '%'.$this->request->getQuery('city').'%',
            ]
        ],
            'order' => ['Products.price' => 'ASC' ]
        ];
        
        $this->set('products', $this->paginate($this->Products));
        $this->set('_serialize', ['products', 'brands', 'users']);

    }

The method does not give error, but it filters as I said earlier.

I appreciate if anyone can analyze.

2

To be able to filter you should join with those associations. Change the paginate call like this

$products = $this->Products->find('all')
   ->leftJoinWith('Products.Users')
   ->leftJoinWith('Brands');
$this->paginate($products);

Also, when using conditions, the key must be the table/association name and field, it should be like this

'conditions' => ['and' => [
    'Products.name like' => '%'. $this->request->getQuery('search') . '%',
    'Brands.id like' => '%'.$this->request->getQuery('brand').'%',
    'Users.state like' => '%'.$this->request->getQuery('estate').'%',
    'Users.city like' => '%'.$this->request->getQuery('city').'%',
]
3

I made this change in the code and no longer gives the error I reported. However, when I leave only the brand field, it works. When I add more fields, for example: the state and the city, he does not search.

I did it the way you indicated, but it gave error: “Cannot convert value of type string to integer”

Now the method looks like this:

public function search()
    {           
        $this->viewBuilder()->setLayout('search');

        $product = $this->paginate = [
            'contain' => ['Brands', 'Users'],
            'conditions' => ['and' => [
                'Products.name like' => '%'. $this->request->getQuery('search') . '%',
                'Brands.id like' => '%'.$this->request->getQuery('brand').'%',
                'Users.state like' => '%'.$this->request->getQuery('estate').'%',
                'Users.city like' => '%'.$this->request->getQuery('city').'%',
            ]
        ],
            'order' => ['Products.price' => 'ASC' ]
        ];
        
        $this->set('products', $this->paginate($this->Products));
        $this->set('_serialize', ['products', 'brands', 'users']);

    }
4

To search by state/city you have to use join, because the contain probably uses a subquery, and you want to filter the Products query, not the users.

I think its because you are trying to filter by Brands.id which its a int, could you try extracting and testing like this

$products = $this->Products->find('all')
   ->leftJoinWith('Products.Users')
   ->leftJoinWith('Brands')
   // ...
   ->andWhere(['Brands.id like' => '%'.$this->request->getQuery('brand').'%'], ['Brands.id' => 'string']);
5

Maybe this is the clean solution:
$additionCond = $this->getAdditionConditions($q);
$condition = [‘OR’ => $additionCond];

private function getAdditionConditions(string $q): array
{
    return [
        $this->Products->aliasField('name') . ' LIKE' => $q,
        $this->Users->aliasField('name') . ' LIKE' => $q,
}