Search for Products in the User View

Need Help
1

Friends, I am displaying a user’s profile along with the products registered by him. My relationships are correct. I can list all products as normal, along with user information. But my product search field in the user’s view doesn’t work.

This is my code detail_user.ctp

//user information only above. (Address, Telephone, etc.)
.
.
.
  <h2 class="bd-title mt-2" id="content">Products Gallery</h2>
    <div class="card mt-2 mb-2">
      <div class="card-header">
        <?= $this->Form->create('', ['type' => 'get']); ?>
        <div class="input-group">
          <?= $this->Form->control('keyword', array('class' => 'form-control','name'=> 'search','label' => false,'required' => true,));?>
          
          <div class="input-group-append">
            <?= $this->Form->button(__('Search')); ?>
          </div>
        </div>
      </div>
      <div class="table-responsive">
        <table class="table table-hover">
          <tbody>
            <?php foreach ($user->products as $product): ?>

              <td> 
                <p class="title mb-0"><?= h($product->name) ?> </p>
                <var class="price text-muted">R$ <?= number_format($product->price);?></var>
              </td>
              <td>  <br></td>
              <td width="130">
                <?= $this->Html->link(__('Open'), ['controller' => 'Produtos','action' => 'details_product','prefix' => false, $product->slug], array('class' => 'btn btn-primary')) ?>

              </td>
            </tr>
            <?php endforeach; ?>

          </tbody></table>
        </div> <!-- table-responsive .end// -->
      </article>

And my method is like this:

 public function detailUser($slug)
  {
  
    $keyword = $this->request->getQuery('keyword');

   $this->paginate = [
        'contain' => ['Products'],
        'conditions' => ['product_name like' => '%'. $this->request->getQuery('keyword') . '%']
      ];

    $usuario = $this->Users
      ->findBySlug($slug)
      ->contain(['Products', 'Groups'])
      ->firstOrFail();

    $query = $this->Users->find('all');
    $this->set('user', $user);
    $this->set('query', $this->paginate($query));
  }

I have difficulties to solve this method. At the moment, when I type the product name, the url changes, but the product is not searched.I get the error: Column not found: 1054 Unknown column ‘product_name’ in ‘where clause’

I also tried:

'contain' => ['Products.Users'],

But the error continues.

I appreciate any comments!

2

Well, I have to ask the obvious; is there a the column named product_name in the products table schema?

3

Yes there is.
I made a change. Change form->create to:

<? = $this->form->create (' ', ['controllers' => 'Users', 'action' => 'search', 'type' => 'get' ]); ?>`

And the search method looked like this:

  public function search()
  {           
      $users = $this->Users
      ->find()
      ->contain('Products')
      ->matching('Products', function(\Cake\ORM\Query $q) {
          return $q->where(['product_name' => '%'. $this->request->getQuery('keyword') . '%']);
      })->first();
      
      $this->set('user', $this->paginate($user));
      $this->set('_serialize', ['product','user']);

  }

However, information about users and their products is not displayed. I get the error: Undefined property: Cake\ORM\ResultSet

All fields that are displayed in the view have this information.

4

There is a typo in your public function search(). You set the variable $users (plural) to the result of your query but set the value of $user (singular) to the variable for the view.

5

It was a typing error. Is correct.
I debugged it ($ user) and my query looks like this:

SELECT Users.id AS `Users`, Users.user_name AS `Users__user_name`, Users.group_id AS `Users__group_id`, Products.id AS `Products__id`, Products.product_name AS `Products__product_name`, Products.user_id AS `Products__user_id`FROM users Users INNER JOIN products Products ON (product_name = :c0 AND Users.id = (Products.user_id))'

'params' => [
	':c0' => [
		'value' => '%phone%',
		'type' => 'string',
		'placeholder' => 'c0'
	]
],
6

Can you give more details about the Undefined property: Cake\ORM\ResultS error? It should say what object doesn’t have the property. Knowing that detail, you could backtrack the history of the variable that carries the object.

7

I left the method like this:

 public function search()
  {           
      $user = $this->Users
      ->find()
      ->contain('Products')
      ->matching('Products', function(\Cake\ORM\Query $q) {
          return $q->where(['product_name LIKE' => "%". $this->request->getQuery('keyword'). "%"]);
      })->first();
      
      $this->set('user', $this->paginate($user));
      $this->set('_serialize', ['product','user']);

  }

Now I get this:
# Call to undefined method App\Model\Entity\Usuario::getAlias()

The consultation is correct, it is some detail that is going through.

8

->getAlias() is a method of Table objects not Entity objects. So that is an expected error.

The file name and line number associated with the error should take you right to the problem. Possibly passing the wrong variable as an argument to a method call? Or accidentally making a method call on the wrong variable?

9

One thing that doesn’t make sense to me:

You are setting $user to the first record found. That will make it an entity. But you are passing that entity to the paginate() method (component). That might be the source of your problem because a single entity is probably not a valid argument for paginate()

10

I removed the $ user variable from paginate () and the error was resolved. I don’t even get Cake\ORM\ResultSet :: But do not filter by the word typed in the search field. All products listed remain!

11

Hmm…

Looking at your detail_user.ctp I wonder how your search() method ever gets called. And looking at your deailUser() method, I’m not sure why it would need to be called.

First issue How

You create your search form on the page with:

$this->Form->create('', ['type' => 'get']);

Since this call did not receive and action, it will use the current controller/action. How is your search() getting involved?

Second issue Why
Your detailUser() method looks like it is already set up to take care of product filtering and is paying attention to any filter values passed in.

12

I updated the form. I added:

<?= $this->Form->create('', ['url' => ['controller' => 'Usuarios','action' => 'search'], 'type' => 'get']);?>

And the search () method looks like this:

public function search()
  {           
      $user= $this->Users
      ->find()
      ->contain('Products')
      ->matching('Products', function(\Cake\ORM\Query $q) {
          return $q->where(['product_name LIKE' => "%". $this->request->getQuery('keyword'). "%"]);
      })->first();
      
      $this->set('user', $this->paginate());
      $this->set('user', $user);
      $this->set('_serialize', ['product','user']);
  }

The detailUser () method does just that. Displays user information and registered products.
I thought about doing the search in the detailUser () method, but I created the search () method and put the action on the form. The request does not give an error, it just does not seek the product.

13

Ok then. More questions.

  • can you verify that your search() method is being run as expected? (I usually do this by sticking a debug in the method which will then appear in the page)
  • can you verify that the query in search() is returning expected data (by using debugs in the method)? These methods might help.
  • does the sql produced by your query make sense for your task? Can it be run directly as sql against your database and produce the results you want?

If the query is ok, we’ll have to move on to the template.

14

Hmm… I just realized your search() query will lose track of who the User is. There is no transfer of the user id into the method so you will always get the first user that has one of the products that comes out of the search.

Surly this is not the behavior you want. So you’re going to have to rethink the search feature and the query (or queries) that will do what you actually want.

15

I usually do it without having to redirect to another action. That’s why I tried to do it in detailUser () to keep the user’s information. But it doesn’t. I will analyze another way, without losing the user’s information.

16

I tested this as a possible example that you might build from:

        $result = $this->People
            ->find()
            ->contain('Addresses', function (Query $q) {
                return $q->where(['Addresses.city LIKE' => '%on%' ]);
            })
            ->where([ 'People.id' => 20 ])
            ->toArray();
17

I organized my code like this:

function search()
{
   if (!empty($this->data))
   {
     // .. do search, gather results
     $this->set('users', $users);
   }
}

and in view ()

if (isset($results))
{
   // print table with results
}
else
{
   // show search form
}

I believe it will do. However, the query does not filter correctly. I did as you proposed.

  $user = $this->User
      ->find()
      ->contain('Products', function (Query $q) {
          return $q->where(['Products.product_name LIKE' => '%'.$this->request->getQuery('keyword').'%' ]);
      })
      ->where([ 'Users.id' => 'Products.user_id' ])
      ->toArray();

This returns: Cannot convert value of type string to integer

When I use it this way, it doesn’t give an error, but it doesn’t filter correctly: The search changes the user and displays all products if there is one related to the search. It does not keep the user selected.

      $user= $this->User
      ->find('all')
      ->contain('Products')
      ->matching('Products', function (\Cake\ORM\Query $q) {
          return $q->where(['Products.name_product LIKE' => '%'.$this->request->getQuery('keyword').'%' ]);
      })->first();
18

This is probably the line that is giving you the error

You’ll have to actually get the id from somewhere for this where statement.

19

To passing the id by parameter and continue. My url looks like this:
… /17? Keyword = Telephone

Error: Cannot convert value of type string to integer

20

That’s probably your user_id in the URL (17)

You should be able to get the id out of that url with:

$params = $this->request->getAttribute('params');

Your id will be in the 'pass` node of the resulting array