# Show categories and posts associated with a subcategory

**URL:** <https://discourse.cakephp.org/t/show-categories-and-posts-associated-with-a-subcategory/11418>\
**Category:** Need Help\
**Created:** [August 4, 2023, 4:26pm UTC](https://discourse.cakephp.org/t/show-categories-and-posts-associated-with-a-subcategory/11418 "2023-08-04T16:26:52Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![CyberLost](https://avatars.discourse-cdn.com/v4/letter/c/a3d4f5/32.png) [@CyberLost](https://discourse.cakephp.org/u/CyberLost)\
**Post date:** [August 4, 2023, 4:26pm UTC](https://discourse.cakephp.org/t/show-categories-and-posts-associated-with-a-subcategory/11418/1 "2023-08-04T16:26:52Z")

</div>

Good morning,

I would like to display on my homepage categories and the last 10 posts belonging to these categories. That I know how to do.

But my concern is that posts belonging to a subcategory of the displayed category are not retrieved when I would like them to be displayed.

```auto
<?php 
$categories = $this->Categories->find('all')
	->where(['Categories.public'=> true])
	->contain([
		'ChildCategories',
		'ParentCategories',
		'Videos' => function($q) {
	 	 	 return $q
			->contain('Categories')
			->limit(5);
		}
	])
	 $this->set(compact('categories'));
?>

```

Can you help me ? thx !

---

<div class="post-metadata">

**Author:** ![CyberLost](https://avatars.discourse-cdn.com/v4/letter/c/a3d4f5/32.png) [@CyberLost](https://discourse.cakephp.org/u/CyberLost)\
**Post date:** [August 4, 2023, 4:51pm UTC](https://discourse.cakephp.org/t/show-categories-and-posts-associated-with-a-subcategory/11418/2 "2023-08-04T16:51:04Z")

</div>

I understand that the limit that I have applies to all the videos found while I would like it to apply to each category.

5 videos / categories displayed  
not 5 videos for all displayed categories

---

<div class="post-metadata">

**Author:** ![Zuluru](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/zuluru/32/1230_2.png) [@Zuluru](https://discourse.cakephp.org/u/Zuluru)\
**Post date:** [August 4, 2023, 7:25pm UTC](https://discourse.cakephp.org/t/show-categories-and-posts-associated-with-a-subcategory/11418/3 "2023-08-04T19:25:58Z")

</div>

Is [this](https://stackoverflow.com/questions/30241975/how-to-limit-contained-associations-per-record-group) what you’re looking for?

---

<div class="post-metadata">

**Author:** ![CyberLost](https://avatars.discourse-cdn.com/v4/letter/c/a3d4f5/32.png) [@CyberLost](https://discourse.cakephp.org/u/CyberLost)\
**Post date:** [August 4, 2023, 7:43pm UTC](https://discourse.cakephp.org/t/show-categories-and-posts-associated-with-a-subcategory/11418/4 "2023-08-04T19:43:53Z")

</div>

It looks like it. It’s a bit complicated for my level. I will study this…

---

<div class="post-metadata">

**Author:** ![CyberLost](https://avatars.discourse-cdn.com/v4/letter/c/a3d4f5/32.png) [@CyberLost](https://discourse.cakephp.org/u/CyberLost)\
**Post date:** [August 4, 2023, 9:26pm UTC](https://discourse.cakephp.org/t/show-categories-and-posts-associated-with-a-subcategory/11418/5 "2023-08-04T21:26:47Z")

</div>

I’m trying to do this a bit blindly in order to understand:

```auto
class CategoriesTable extends Table
{
   
	public function initialize(array $config): void
	{
		parent::initialize($config);

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

		$this->addBehavior('Timestamp');
		$this->addBehavior('Tree');

		$this->belongsTo('ParentCategories', [
			'className' => 'Categories',
			'foreignKey' => 'parent_id',
		]);
		$this->hasMany('ChildCategories', [
			'className' => 'Categories',
			'foreignKey' => 'parent_id',
		]);

		$this->hasMany('Videos');

		$this->hasOne('TopVideos', [
			'className' => 'Videos',
			'strategy' => 'select',
			'conditions' => function (\Cake\Database\Expression\QueryExpression $exp, \Cake\ORM\Query $query)
			{
				$query->innerJoin([
					'VideosFilter' => $query
					->getConnection()
					->newQuery()
					->select(['category_id'])
					->from('videos')
					->group('category_id')
				],
				[
					'TopVideos.category_id = Videos.category_id',
				]);
			return [];
			}
		]);

	}

```

But I have this error :

> SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘Videos.category\_id’ in 'on clause

**EDIT :** It works with this… but I only have one record/categories ☹

```auto
$this->hasOne('TopAbstracts', [
			'className' => 'Videos',
			'strategy' => 'select',
			'conditions' => function (\Cake\Database\Expression\QueryExpression $exp, \Cake\ORM\Query $query)
			{
				$query->innerJoin([
					'VideosFilter' => $query
					->getConnection()
					->newQuery()
					->select(['category_id'])
					->from('videos')
					->group('category_id')
					->limit(5)
				],
				[
					//'TopVideos.category_id = Videos.category_id',
				]);
			return [];
			}
		]);

```

---

<div class="post-metadata">

**Author:** ![CyberLost](https://avatars.discourse-cdn.com/v4/letter/c/a3d4f5/32.png) [@CyberLost](https://discourse.cakephp.org/u/CyberLost)\
**Post date:** [August 7, 2023, 1:17pm UTC](https://discourse.cakephp.org/t/show-categories-and-posts-associated-with-a-subcategory/11418/6 "2023-08-07T13:17:53Z")

</div>

Maybe the easiest solution is to do something like:

```auto
<?php 
$cats - $this->Cat->find('all',array('recursive'=>-1));
foreach($cats as $key=>$cat){
  $cats[$key]['ArticleCount'] = $this->Cat->Article->find('count',array(
     'conditions'=>array('Article.cat_id'=>$cat['Cat']['id']))
  );
}

```

Source : [php - Counting number of articles within a category using CakePHP? - Stack Overflow](https://stackoverflow.com/questions/1343713/counting-number-of-articles-within-a-category-using-cakephp)

But it doesn’t look very clean to me…

---

<div class="post-metadata">

**Author:** ![ndm](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/ndm/32/2234_2.png) [@ndm](https://discourse.cakephp.org/u/ndm)\
**Post date:** [August 7, 2023, 4:25pm UTC](https://discourse.cakephp.org/t/show-categories-and-posts-associated-with-a-subcategory/11418/7 "2023-08-07T16:25:45Z")

</div>

The custom association examples do only work for a single record, that’s what the original SO question was about. If you want multiple results per group, then use either the plugin, or the manual examples shown at the end.

---

<div class="post-metadata">

**Author:** ![CyberLost](https://avatars.discourse-cdn.com/v4/letter/c/a3d4f5/32.png) [@CyberLost](https://discourse.cakephp.org/u/CyberLost)\
**Post date:** [August 7, 2023, 6:12pm UTC](https://discourse.cakephp.org/t/show-categories-and-posts-associated-with-a-subcategory/11418/8 "2023-08-07T18:12:34Z")

</div>

I find it terribly complicated for a fairly simple request in the end!

---

<div class="post-metadata">

**Author:** ![thomasg](https://avatars.discourse-cdn.com/v4/letter/t/9d8465/32.png) [@thomasg](https://discourse.cakephp.org/u/thomasg)\
**Post date:** [August 7, 2023, 11:59pm UTC](https://discourse.cakephp.org/t/show-categories-and-posts-associated-with-a-subcategory/11418/9 "2023-08-07T23:59:07Z")

</div>

In this case you would first collect the subcategories, add the parent category to $cat\_array and then use -\>where([‘Articles.category\_id IN’ =\> $cat\_array])

---

<div class="post-metadata">

**Author:** ![CyberLost](https://avatars.discourse-cdn.com/v4/letter/c/a3d4f5/32.png) [@CyberLost](https://discourse.cakephp.org/u/CyberLost)\
**Post date:** [August 9, 2023, 2:17pm UTC](https://discourse.cakephp.org/t/show-categories-and-posts-associated-with-a-subcategory/11418/10 "2023-08-09T14:17:59Z")

</div>

It works but if I want to display a list of categories with the last 5 posts for each category it’s not easy.

Ideally I would have wanted a foreach $categories and inside a foreach $categories-\>videos to display this.

---

<div class="post-metadata">

**Author:** ![thomasg](https://avatars.discourse-cdn.com/v4/letter/t/9d8465/32.png) [@thomasg](https://discourse.cakephp.org/u/thomasg)\
**Post date:** [August 10, 2023, 2:58am UTC](https://discourse.cakephp.org/t/show-categories-and-posts-associated-with-a-subcategory/11418/11 "2023-08-10T02:58:08Z")

</div>

yes its still possible just you have to find the subcategories foreach $category and then do a separate find to get the videos, then add them to the original $categories array.  
It’s not in one find but it still will work fine, and a user doesnt care if it was done in one query or multiple ones… as long as they can see the end result its ok.