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.
<?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 !
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
Zuluru
August 4, 2023, 7:25pm
3
Is this what you’re looking for?
It looks like it. It’s a bit complicated for my level. I will study this…
I’m trying to do this a bit blindly in order to understand:
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
$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 [];
}
]);
Maybe the easiest solution is to do something like:
<?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
But it doesn’t look very clean to me…
ndm
August 7, 2023, 4:25pm
7
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.
I find it terribly complicated for a fairly simple request in the end!
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])
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.
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.