Number of Products next to the Filter by brands - Cakephp 3

Friends, I created a menu of brands to filter products.
I am trying to put next to the brand, the number of products contained.

I’m generating the branding menu this way:

BrandsCell.php

.
$this->loadModel('Brands');

$query = $this->Brands->find('all');
$this->set('brands', $query);

display.ctp

<!DOCTYPE html>
<html>
<body>

	<div class="card mb-3">
		<div class="card-body">
			<h6 class="card-title text-muted">Brands</h6>
			<hr>
			<?php foreach ($brands as $brand): ?>
			

			<div class="mt-1"><?php echo $this->Html->link($brande->name, array('controller' => 'Brands', 'action' => 'search_brands', $brand->id), array('escape' => false));?>
				<b class="badge badge-pill badge-light float-right">***//Product Counter***</b>  
			</div>
			<?php endforeach; ?>

		</div>
	</div>
</body>
</html>

When I enter the brand id through the marca_id, it works well.

        $data = $this->Products->find();
        $data->select(['Products.id', $data->func()->count('Brands.id')])
        ->matching('Brands')
        ->group(['Products.id'])
        ->where(['Products.brand_id' => 5]);

        $total = $data->count();
        var_dump($total);

But I have difficulties for that in the foreach to be displayed next to each brand. I did it but without success!

<!DOCTYPE html>
<html>
<body>

	<div class="card mb-3">
		<div class="card-body">
			<h6 class="card-title text-muted">Brands</h6>
			<hr>
			<?php foreach ($brands as $brand): ?>
				<?php foreach ($data as $total): ?>

			
			<div class="mt-1"><?php echo $this->Html->link($brand->name, array('controller' => 'Brands', 'action' => 'search_brands', $brand->id), array('escape' => false));?>
				<b class="badge badge-pill badge-light float-right"><?php echo $total->count();?></b>  
			</div>
			<?php endforeach; ?>
				<?php endforeach; ?>

		</div>
	</div>
</body>
</html>

Erro: Notice (8): Undefined offset: 1 [CORE/src/Database/FieldTypeConverter.php, line 134]

My final display.ctp looked like this:

public function display()
    {
        $this->viewBuilder()->setLayout('loja');
        $this->loadModel('Brands');
        $this->loadModel('Products');

        $data = $this->Products->find();
        $data->select(['Products.id', $data->func()->count('Brands.id')])
        ->matching('Brands')
        ->group(['Products.id'])
        ->where(['Products.brand_id' => 5]);
        
        $query = $this->Brands->find('all');
        $this->set('brands', $query);
        $this->set('data', $data);
    }

I appreciate if anyone can help me!

I’m not really sure why your code is failing. But I know you can make the problem simpler by using the CounterCache Behavior.

This is a super simple use case where a couple of lines added to your Products table, and a new field in your Brands table, will give you the count of products recorded in your Brand records without additional queries in your controller code.

I read the documentation. I already added the lines. It really is quite simple, but I didn’t see how to handle it in the display.ctp. This count will be in the menu next to the brand name.

The count should be available on you brand record now as a new property.

So now you would have something like this:

<h6 class="card-title text-muted">Brands</h6>
<hr>
<?php foreach ($brands as $brand): ?>
        <div class="mt-1">
            <?php echo $this->Html->link(
                $brand->name, 
                array('controller' => 'Brands', 'action' => 'search_brands', $brand->id),
                array('escape' => false)
            );?>
            <b class="badge badge-pill badge-light float-right">
                <?php echo $brand->counter_cache_value);?>
            </b>
        </div>
<?php endforeach; ?>

Dedpending of course on what you named your new column

So my code looks like this:

public function display()
    {
        $this->viewBuilder()->setLayout('loja');
        $this->loadModel('Brands');
        
        $query = $this->Brands->find('all');
        $this->set('brand', $query);
    }

BrandsTable.php

.
$this->hasMany('Products', [
            'foreignKey' => 'brand_id',
);
.
.
$this->addBehavior('CounterCache', [
            'Products' => ['brand_count']
]);

ProductsTable.php

.
$this->belongsTo('Brands', [
            'foreignKey' => 'brand_id',
            'joinType' => 'INNER',
]);
.
.
$this->addBehavior('CounterCache', [
            'Brands' => ['product_count']
]);

Display.ctp

<?php foreach ($marcas as $marca): ?>

<div class="mt-1">
<?php echo $this->Html->link($brand->name, array('controller' => 'Brands', 'action' => 'search_brands', $brand->id), array('escape' => false));?>
<b class="badge badge-pill badge-light float-right">
<?php echo $brand->product_count;?>
</b>  
</div>

<?php endforeach; ?>

However, the numbering is not displayed next to the mark. I appreciate if you can analyze.

Away from my computer but two things:

The counter that counts brands per product will always say 1 because product belongs to brand

And, the counters won’t hold any value until some activity causes them to evaluate. For example, setting a product brand_id to dirty and saving it will trigger the behavior and put a count value on the corresponding brand record.

So you will probably have to artificially trigger that for every brand.

And possibly clear cake’s model cache first too.

Well, if you need count of products then why not use the helper function so that you can use easily on .ctp.

I went to add a test to see if the couter worked and asked to create a field in the bank: product_count. I believed with INT (11), now it gives error: Error: SQLSTATE [42S22]: Column not found: 1054 Unknown column ‘product_count’ in the ‘field list’

Did you clear the ORM cache after adding the new field?

Yes I did.
It still does not recognize the column in the field list. I even generated the model again by bake.


If you are using SQL keywords as table column names, you can enable identifier citation for your database connection at config / app.php.

Can this be caused by using automatic tables?

Some of the table objects in your application were created by instantiating “** Cake \ ORM \ Table **” instead of any other specific subclass.

You have a typo somewhere. When cake can’t find a table class it expects it will make a generic one and that is what’s happening here.

It could be in an association definition or a call to make a table class somewhere.

Where ever this ‘Error: SQLSTATE [42S22]: Column not found: 1054 Unknown column ‘product_count’ in the ‘field list’’ is coming from probably will lead back to the naming typo.

I created a new project to understand this while solving the problem. Will there be a need to add an action from this counter in the product addition and exclusion method?

I added the fields in the table as below:

CommentsTable.php

$this->addBehavior('CounterCache', [
            'Articles' => ['comments_count']
        ]);

        parent::initialize($config);

        $this->setTable('comments');
        $this->setDisplayField('title');
        $this->setPrimaryKey('id');

        $this->belongsTo('Articles', [
            'foreignKey' => 'article_id',
            'joinType' => 'INNER',
        ]);

ArticlesTable.php

$this->addBehavior('CounterCache', [
            'Comments' => ['article_count']
        ]);
        parent::initialize($config);

        $this->setTable('articles');
        $this->setDisplayField('name');
        $this->setPrimaryKey('id');

        $this->hasMany('Comments', [
            'foreignKey' => 'article_id',
        ]);

When I use the bake to generate the code, an article_count and comments_cout input is also created, which I leave null with a null pattern.

It looks like you have defined your columns in the wrong tables. Though the behavior definitions appear to be on the correct Table classes.

The articles table should have comments_count column defined in it. The behavior however will go on Comments Table class as you have done.

With this pattern, every save action on a Comment record will, after completion, execute it’s behavior to calculate the new count of comments for the associated article. The behavior config in the counted table holds all the information about the parent table that will receive the new count and which field the parent will use to store the count.

There is however no need of an article_count column in Comments or the corresponding behavior in Articles to store counts of articles into Comments because there is always one article per comment (Comments belongsTo Articles).

I’m not clear on what you are asking. Perhaps the answer I’ve given will make things clear.

Thank you very much for the explanations. It really is a lot easier than I imagined, but I ended up doing some things wrong in the relationship of the tables.

Thank you so!