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

Community · Accepted Answer · 2017-05-23 12:08:34Z

1

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');

2 Comments

Anuj TBE Over a year ago

thanks ndm. but matching is not working. It is still showing products with SellerProducts.stock = 0

ndm Over a year ago

@AnujTBE That's a different question though.That's not how matching works. Matching filters the parent records, if you contain child records (SellerProducts) and want only specific ones, then you have to filter them too. See stackoverflow.com/a/26800203/1392379

Collectives™ on Stack Overflow

CakePHP : query printing same values twice

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related