Bismarck
Reputation: 115
Cake PHP Association
I have a 3 tables
Table
articles- Contains a list of all Articles.Table
articles_tags- Contains a list of all Article TagsTable
articles_tag_names- Contains a list of Tag Names
Structure for articles table is id|aticle_body|article_author_id|article_date.
Structure for articles_tags table is id|article_id|article_tag_id.
Structure for articles_tag_names table is id|article_tag_name.
I want to fetch all or specific articles together with their respective tag names using Table associations. I fruitlessly tried the following.
In App\Model\Table\ArticlesTable.php.
class ArticlesTable extends Table
{
public function initialize(array $config)
{
parent::initialize($config);
$this->hasMany('ArticlesTags', [
'dependent' => true,
'cascadeCallbacks' => false,
'propertyName' => 'article_tags'
]);
}
}
In App\Model\Table\ArticlesTagsTable.php.
class ArticlesTagsTable extends Table
{
public function initialize(array $config)
{
parent::initialize($config);
$this->belongsToMany('Articles')
->setForeignKey('article_id');
/** I need ideas on how to associate with ArticlesTagNamesTable **/
}
}
In App\Model\Table\ArticlesTagNamesTable.php.
class ArticlesTagNamesTable extends Table
{
public function initialize(array $config)
{
parent::initialize($config);
/** I need ideas on how to associate with ArticlesTagsTable **/
}
}
I am expecting an output like this below.
[
[
'id' => 145,
'article_body' => This is my first article body,
'article_author_id' => 13,
'article_date' => March 18 2017 19:00:12,
'article_tags' => [
[
'id' => 3,
'article_id' => 145,
'article_tag_id' => 32,
'article_tag_name' => cakephp
],
[
'id' => 4,
'article_id' => 145,
'article_tag_id' => 33,
'article_tag_name' => java
]
]
],
[
'id' => 146,
'article_body' => This is my second article body,
'article_author_id' => 13,
'article_date' => March 18 2017 22:00:16,
'article_tags' => [
[
'id' => 5,
'article_id' => 146,
'article_tag_id' => 34,
'article_tag_name' => php
]
]
]
]
OR
[
[
'id' => 145,
'article_body' => This is my first article body,
'article_author_id' => 13,
'article_date' => March 18 2017 19:00:12,
'article_tags' => [
[
'id' => 3,
'article_id' => 145,
'article_tag_id' => 32,
'articles_tag_names' => [
'id' => 12
'article_tag_name' => cakephp
]
],
[
'id' => 4,
'article_id' => 145,
'article_tag_id' => 33,
'articles_tag_names' => [
'id' => 13
'article_tag_name' => java
]
]
]
],
[
'id' => 146,
'article_body' => This is my second article body,
'article_author_id' => 13,
'article_date' => March 18 2017 22:00:16,
'article_tags' => [
[
'id' => 5,
'article_id' => 146,
'article_tag_id' => 34,
'articles_tag_names' => [
'id' => 14
'article_tag_name' => php
]
]
]
]
]
.... etc etc
Can someone help with combining Articles, ArticleTags and ArticleTagNames using CakePHP associations or joins.
Upvotes: 0
Views: 73
Answers (1)
Guillaume Pommier
Reputation: 477
My first advice is to follow CakePHP conventions to do this easily.
You will end up with something like this.
Articles table
CREATE TABLE `articles` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`body` varchar(255) DEFAULT NULL,
`author_id` int(11) DEFAULT NULL,
`created` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
Authors table
CREATE TABLE `authors` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
Tags table
CREATE TABLE `tags` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
ArticlesTags table
CREATE TABLE `articles_tags` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`article_id` int(11) DEFAULT NULL,
`tag_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx1` (`article_id`),
KEY `idx2` (`tag_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
Articles table class
<?php
namespace App\Model\Table;
use Cake\ORM\Query;
use Cake\ORM\RulesChecker;
use Cake\ORM\Table;
class ArticlesTable extends Table
{
public function initialize(array $config)
{
parent::initialize($config);
$this->setTable('articles');
$this->setDisplayField('id');
$this->setPrimaryKey('id');
$this->addBehavior('Timestamp');
$this->belongsTo('Authors', [
'foreignKey' => 'author_id'
]);
$this->belongsToMany('Tags', [
'foreignKey' => 'article_id',
'targetForeignKey' => 'tag_id',
'joinTable' => 'articles_tags'
]);
}
public function buildRules(RulesChecker $rules)
{
$rules->add($rules->existsIn(['author_id'], 'Authors'));
return $rules;
}
}
Tags table class
<?php
namespace App\Model\Table;
use Cake\ORM\Query;
use Cake\ORM\RulesChecker;
use Cake\ORM\Table;
class TagsTable extends Table
{
public function initialize(array $config)
{
parent::initialize($config);
$this->setTable('tags');
$this->setDisplayField('name');
$this->setPrimaryKey('id');
$this->belongsToMany('Articles', [
'foreignKey' => 'tag_id',
'targetForeignKey' => 'article_id',
'joinTable' => 'articles_tags'
]);
}
}
?>
Articles controller
<?php
namespace App\Controller;
use App\Controller\AppController;
class ArticlesController extends AppController
{
public function index()
{
$articles = $this->Articles->find()
->contain(['Authors', 'Tags'])
->hydrate(false)
->toArray();
debug($articles);
exit();
}
}
?>
You will end up with:
/src/Controller/ArticlesController.php (line 28)
[
(int) 0 => [
'id' => (int) 1,
'body' => 'Lorem ipsum',
'author_id' => (int) 1,
'created' => object(Cake\I18n\FrozenTime) {
'time' => '2018-03-19T00:00:25+00:00',
'timezone' => 'UTC',
'fixedNowTime' => false
},
'tags' => [
(int) 0 => [
'id' => (int) 1,
'name' => 'First tag',
'_joinData' => [
'id' => (int) 1,
'article_id' => (int) 1,
'tag_id' => (int) 1
]
],
(int) 1 => [
'id' => (int) 2,
'name' => 'Second Tag',
'_joinData' => [
'id' => (int) 2,
'article_id' => (int) 1,
'tag_id' => (int) 2
]
]
],
'author' => [
'id' => (int) 1,
'name' => 'Xander'
]
],
(int) 1 => [
'id' => (int) 2,
'body' => 'Follow CakePHP conventions!',
'author_id' => (int) 1,
'created' => object(Cake\I18n\FrozenTime) {
'time' => '2018-03-19T00:00:57+00:00',
'timezone' => 'UTC',
'fixedNowTime' => false
},
'tags' => [
(int) 0 => [
'id' => (int) 1,
'name' => 'First tag',
'_joinData' => [
'id' => (int) 3,
'article_id' => (int) 2,
'tag_id' => (int) 1
]
],
(int) 1 => [
'id' => (int) 3,
'name' => 'Third Tag',
'_joinData' => [
'id' => (int) 4,
'article_id' => (int) 2,
'tag_id' => (int) 3
]
]
],
'author' => [
'id' => (int) 1,
'name' => 'Xander'
]
]
]
Upvotes: 1