CakePHP : query printing same values twice

Question

I'm working on CakePHP 3.2.

I have categories, products, seller_products table and their association are

categories->hasMany('Products');
seller_products->hasMany('Products');

I have to retrieve all products group by category where seller_products.stock > 0

This is what I'm doing

$pros1 = $this->Products->Categories->find()
          ->where([
            'Categories.status' => 0,
          ])
          ->contain([
            'Products.SellerProducts', 'Subcategories', 'Subcategories.ProductTypes'
          ])
          ->matching('Products.SellerProducts', function(\Cake\ORM\Query $q) {
            return $q->where(['SellerProducts.stock >' => 0]);
          });

but on debugging, it is returning same values twice. foreach($pros1 as $p){debug($p);} is printing same values twice

/src/Controller/PagesController.php (line 120)

object(App\Model\Entity\Category) {

    'id' => (int) 1,
    'title' => 'Electronics',
    'description' => '',
    'icon' => 'fa-television',
    'status' => (int) 0,
    'created' => object(Cake\I18n\Time) {
       .........
     }

/src/Controller/PagesController.php (line 120)

object(App\Model\Entity\Category) {

    'id' => (int) 1,
    'title' => 'Electronics',
    'description' => '',
    'icon' => 'fa-television',
    'status' => (int) 0,
    'created' => object(Cake\I18n\Time) {
       .........
     }

and debug(count($pros1)) prints 1

score 1 · Accepted Answer · edited May 23 '17 at 12:08

Quote from the docs:

As this function will create an INNER JOIN, you might want to consider calling distinct on the find query as you might get duplicate rows if your conditions don’t exclude them already. This might be the case, for example, when the same users comments more than once on a single article.

Cookbook > Database Access & ORM > Query Builder > Filtering by Associated Data

In your case there are multiple matching products that belong to the same category. So, either use a distinct select for the tables primary key, or group by it (there may be no difference between the two).

$pros1 = $this->Products->Categories
    ->find()
    ->distinct('Categories.id')
    // ...

    // ...
    ->matching(/* ... */)
    ->group('Categories.id');

CakePHP : query printing same values twice

1 Answers1