CakePHP how I will fetch category wise products?

Question

I have 3 tables

categories, product_categories , products

Here categories hasMany product_categories and product_categories hasMany products. I am trying to fetch latest 20 products under categories.

I have written below query

$categories = \Cake\ORM\TableRegistry::getTableLocator()->get( 'Categories' )->find()
    ->contain(
                [
                    'ProductCategories.Products' => function($q){
                            return $q->limit(20);
                    }
                ]
     )

By this query I am getting 20 products for per ProductCategories. I just need 20 products for categories not by per ProductCategories. How can I solve this problem ?

After @ndm comment I have tried below library https://github.com/icings/partitionable

In CategoriesTable table I have written below code

$this
      ->partitionableBelongsToMany('RecentProducts')
      ->setClassName('Products')
      ->setThrough('ProductCategories')
      ->setLimit(9)
      ->setSort([
          'RecentProducts.created' => 'DESC',
      ])
;

My query looks like

SELECT 
  ProductCategories.id AS RecentProducts_CJoin__id, 
  ProductCategories.name AS RecentProducts_CJoin__name, 
  ProductCategories.slug AS RecentProducts_CJoin__slug, 
  ProductCategories.img AS RecentProducts_CJoin__img, 
  ProductCategories.category_id AS RecentProducts_CJoin__category_id, 
  ProductCategories.sub_category_id AS RecentProducts_CJoin__sub_category_id, 
  ProductCategories.created AS RecentProducts_CJoin__created, 
  ProductCategories.modified AS RecentProducts_CJoin__modified, 
  RecentProducts.id AS RecentProducts__id, 
  RecentProducts.name AS RecentProducts__name, 
  RecentProducts.item_id AS RecentProducts__item_id, 
  RecentProducts.product_category_id AS RecentProducts__product_category_id, 
  RecentProducts.crawl_uniquekey AS RecentProducts__crawl_uniquekey, 
  RecentProducts.website_name AS RecentProducts__website_name, 
  RecentProducts.original_price AS RecentProducts__original_price, 
  RecentProducts.discount_amount AS RecentProducts__discount_amount, 
  RecentProducts.description AS RecentProducts__description, 
  RecentProducts.img_url AS RecentProducts__img_url, 
  RecentProducts.total_review AS RecentProducts__total_review, 
  RecentProducts.review_average AS RecentProducts__review_average, 
  RecentProducts.price AS RecentProducts__price, 
  RecentProducts.percentage AS RecentProducts__percentage, 
  RecentProducts.expiry AS RecentProducts__expiry, 
  RecentProducts.price_after_discount AS RecentProducts__price_after_discount, 
  RecentProducts.price_zero_padded AS RecentProducts__price_zero_padded, 
  RecentProducts.created AS RecentProducts__created, 
  RecentProducts.modified AS RecentProducts__modified 
FROM 
  products RecentProducts 
  INNER JOIN product_categories ProductCategories ON ProductCategories.id = (
    RecentProducts.product_category_id
  ) 
WHERE 
  (
    ProductCategories.category_id in (1, 2) 
    AND ProductCategories.id in (
      SELECT 
        __ranked__RecentProducts.id AS id 
      FROM 
        (
          SELECT 
            ProductCategories.id AS RecentProducts_CJoin__id, 
            ProductCategories.name AS RecentProducts_CJoin__name, 
            ProductCategories.slug AS RecentProducts_CJoin__slug, 
            ProductCategories.img AS RecentProducts_CJoin__img, 
            ProductCategories.category_id AS RecentProducts_CJoin__category_id, 
            ProductCategories.sub_category_id AS RecentProducts_CJoin__sub_category_id, 
            ProductCategories.created AS RecentProducts_CJoin__created, 
            ProductCategories.modified AS RecentProducts_CJoin__modified, 
            ProductCategories.id AS id, 
            (
              ROW_NUMBER() OVER (
                PARTITION BY ProductCategories.category_id 
                ORDER BY 
                  RecentProducts.created DESC
              )
            ) AS __row_number, 
            RecentProducts.id AS RecentProducts__id, 
            RecentProducts.name AS RecentProducts__name, 
            RecentProducts.item_id AS RecentProducts__item_id, 
            RecentProducts.product_category_id AS RecentProducts__product_category_id, 
            RecentProducts.crawl_uniquekey AS RecentProducts__crawl_uniquekey, 
            RecentProducts.website_name AS RecentProducts__website_name, 
            RecentProducts.original_price AS RecentProducts__original_price, 
            RecentProducts.discount_amount AS RecentProducts__discount_amount, 
            RecentProducts.description AS RecentProducts__description, 
            RecentProducts.img_url AS RecentProducts__img_url, 
            RecentProducts.total_review AS RecentProducts__total_review, 
            RecentProducts.review_average AS RecentProducts__review_average, 
            RecentProducts.price AS RecentProducts__price, 
            RecentProducts.percentage AS RecentProducts__percentage, 
            RecentProducts.expiry AS RecentProducts__expiry, 
            RecentProducts.price_after_discount AS RecentProducts__price_after_discount, 
            RecentProducts.price_zero_padded AS RecentProducts__price_zero_padded, 
            RecentProducts.created AS RecentProducts__created, 
            RecentProducts.modified AS RecentProducts__modified 
          FROM 
            products RecentProducts 
            INNER JOIN product_categories ProductCategories ON ProductCategories.id = (
              RecentProducts.product_category_id
            ) 
          WHERE 
            ProductCategories.category_id in (1, 2)
        ) __ranked__RecentProducts 
      WHERE 
        __ranked__RecentProducts.__row_number <= 9
    )
  ) 
ORDER BY 
  RecentProducts.created DESC

My query builder code

$categories = \Cake\ORM\TableRegistry::getTableLocator()->get( 'Categories' )->find()
        ->contain(
                    [
                        'CategoryKeywords',
                        'RecentProducts'
                    ]
         )

Here I am getting 18 items but expectation is 9.

That's not supported out of the box, and can be quite tricky depending on the specific use case: **https://stackoverflow.com/questions/30241975/how-to-limit-contained-associations-per-record-group/30270675#30270675** — ndm, Oct 04 '21 at 15:41
Is *Product* and *Categories* direct relation available in table ? — RiTeSh, Oct 04 '21 at 16:15
@ndm I have tried by your solution, problem I am facing how can get data with limit for nested container table products Categories->Product Categories->products — Niloy Rony, Oct 04 '21 at 17:43
Just contain the new association like any other association, eg instead of `Products` you reference `TopProducts` (or whatever you've named it). — ndm, Oct 04 '21 at 21:56
@ndm I have updated my question after tried by user library. — Niloy Rony, Oct 05 '21 at 09:51
Sorry, I ment the query builder code, eg `$table->find()...`, but the SQL is also helpful. Another question, do you always get double the amount of the limit, eg for a limit of `5` you get `10` products per category? — ndm, Oct 05 '21 at 11:54
@ndm do you always get double the amount of the limit, eg for a limit of 5 you get 10 products per category? Ans is no. For example for a category I am getting 200 products. I have also update query builder code in question. — Niloy Rony, Oct 05 '21 at 12:33
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/237826/discussion-between-niloy-rony-and-ndm). — Niloy Rony, Oct 05 '21 at 12:38
@NiloyRony There might be a misunderstanding, you don't really receive 200 products when you set the limit to 5, or do you? Also I'm wondering, where does the number 18 stem from exactly, do you see 18 products in the results set / when you output the data, or are you maybe referring the what you see in debug kit? Because 18 records in total would be correct, as you have two categories, so 9 products for each category makes 18 products in total, but they should of course be separated properly in the query result set. — ndm, Oct 05 '21 at 16:12

RiTeSh · Answer 1 · 2021-10-04T15:21:26.577

0

Assuming that you have following relation

Categories hasMany ProductCategories

ProductCategories hasMany Products

Product belongsTo ProductCategories

$this->loadModel('Products');
        $product = $this->Products->find('all')
            ->contain(['ProductCategories'])
            ->limit(20)
            ->groupBy('product_categories_id');

edited Oct 04 '21 at 15:21

answered Oct 04 '21 at 15:02

RiTeSh

513
3
12

I want to fetch category to product, not product to category. – Niloy Rony Oct 04 '21 at 15:41

CakePHP how I will fetch category wise products?

1 Answers1