-
stephaniie
Newbie
-
Posts: 2
Threads: 2
Joined: Jul 2018
Reputation:
0
How is ManytoMany and HasManyThrough done in CodeIgniter? I want to make a clean default understanding of these two relationships in databases and web. Also, if possible, created with intermediate tables.
Laravel Example:
Many-to-many relations are slightly more complicated than hasOne and hasMany relationships. An example of such a relationship is a posts with many tags, where the tags are also shared by other posts. [link]
Has Many Through The "has-many-through" relationship provides a convenient shortcut for accessing distant relations via an intermediate relation. For example, a Tag model might have many Post models through an intermediate User model. ( How many posts with tag "business" did user "Adam" create? ) [link]
Alt Solution: Somewhere else here it is suggested to use Join [ codeigniter-many-to-many-relationship-loop ]
Code: $this->db->select('*');
$this->db->from('posts');
$this->db->join('posts_tags', 'posts.post_id = post_tags.post_id', 'inner');
$this->db->join('tags', 'posts_tags.tag_id = tags.tag_id', 'inner');
Code: $posts = array('title' => $title,'desc' => $desc, );
$this->db->where('id',$idNo);
$this->db->update('posts',$arrayName);
$post_tag = array('tag_id' => $tag_id);
$this->db->where('post_id',$idNo);
$this->db->update('posts',$post_tag);
-
kirasiris
Junior Web Developer
-
Posts: 62
Threads: 26
Joined: Dec 2016
Reputation:
0
First of all doing joins is not a good technique(nor for me as I really hate them unless there's a real need of them to use, especially when you're trying to edit/update/retrieve data frin an article/post/blog/etc coming from two tables)
I hope the following can help you, there is still a lot to be done(specially since I'm trying to create a WordPress terms system like) but it will give you a good insight. Good luck
You will probably need to have 3-4 DB tables in total.
One for Posts
id, title, slug.
One for terms
term_id, title, slug,
One for users:
user_id, username, slug, biography,etc, etc
One which will contain info coming from both post and terms tabes.
id, term_id, post_id, 'user_id, type
NOTE: Thew tables are not perfect, I just imagined an scenario that can be applied with the blocks of code that I'm about to tell you here.
Terms Cloud System
When creating tags(on its own controller) you could have a controller like this one:
PHP Code: public function add(){
// Field Rules
$this->form_validation->set_rules('title', 'Title', 'trim|required|min_length[3]');
if ($this->form_validation->run() == FALSE) {
// Load template
$this->template->load('admin', 'default', 'categories/add');
} else {
$slug = str_replace(' ', '-', $this->input->post('title'));
$slug = strtolower($slug);
// Page Data
$data = array(
'title' => strip_tags($this->input->post('title')),
'slug' => $slug,
'term_group' => '0',
);
$title = $data['title'];
$this->Terms_model->add($data);
// Relationship Array
$data = array(
'term_id' => $this->db->insert_id(),
'type' => $this->input->post('type'),
'status' => 'published',
);
// Insert relationship
$this->Terms_model->add_relationship($data);
// Activity Array
$data = array(
'resource_id' => $this->db->insert_id(),
'type' => 'term',
'action' => 'added',
'user_id' => $this->session->userdata('user_id'),
'message' => 'A new tag was added (' . $title . ')'
);
// Insert Activity
$this->Activity_model->add($data);
// Set Message
$this->session->set_flashdata('success', 'Term has been added');
// Redirect
redirect('admin/categories');
}
}
When editing them, you will have to get some info from the relationship table as well to make the updates possible:
PHP Code: public function edit($id){
// Field Rules
$this->form_validation->set_rules('title', 'Title', 'trim|required|min_length[3]');
if ($this->form_validation->run() == FALSE) {
$data['terms'] = $this->Terms_model->get($id);
// Get attached tags
$data['relationship'] = $this->Terms_model->the_relationship($id);
// Load template
$this->template->load('admin', 'default', 'categories/edit', $data);
} else {
$slug = str_replace(' ', '-', $this->input->post('title'));
$slug = strtolower($slug);
// Page Data
$data = array(
'title' => $this->input->post('title'),
'slug' => $slug,
'term_group' => '0',
);
$this->Terms_model->update($id, $data);
// Relationship Data
$data = array(
'type' => strip_tags($this->input->post('type')),
);
// Update Relationship
$this->Terms_model->update_relationship($id, $data);
// Activity Array
$data = array(
'resource_id' => $this->db->insert_id(),
'type' => 'term',
'action' => 'updated',
'user_id' => $this->session->userdata('user_id'),
'message' => 'A (' . $data['title'] . ') was updated'
);
// Insert Activity
$this->Activity_model->add($data);
// Set Message
$this->session->set_flashdata('success', 'Category has been updated');
// Redirect
redirect('admin/categories');
}
}
I will not put the info of the Model as that it is so simple to create(I assume you already know how to get info from database)
This will be the view for adding tags(very simple):
PHP Code: <?= form_open('admin/categories/add'); ?>
<!-- Title -->
<div class="form-group">
<?= form_label('Term Name', 'title'); ?>
<?php
$data = array(
'name' => 'title',
'id' => 'title',
'maxlength' => '100',
'class' => 'form-control',
'value' => set_value('title')
);
?>
<?= form_input($data); ?>
</div>
<!-- Type -->
<div class="form-group">
<?= form_label('Term Type', 'type'); ?>
<?php
$data = array(
'name' => 'type',
'id' => 'type',
'class' => 'form-control',
'value' => set_value('type')
);
$options = array(
'' => 'Please select an option',
'category' => 'Category',
'tag' => 'Tag',
);
?>
<?= form_dropdown($data, $options); ?>
</div>
<?= form_submit('mysubmit', 'Add Post Category', array('class' => 'btn btn-primary')); ?>
<?= form_close(); ?>
As I said in case you want to edit them, you will need to retrieve some info from the relationship table corresponding to current ID, this is the edit view(again something very easy):
PHP Code: <div class="container">
<h3 class="page-header">Edit Category</h3>
<?= validation_errors('<p class="alert alert-danger">'); ?>
<div class="clearfix">
<?= anchor('admin/categories', 'Return to Categories', array('title' => 'View Categories', 'class' => 'btn btn-primary pull-left')); ?>
<?= anchor('admin/categories/add', 'Add New Category', array('title' => 'Add Category', 'class' => 'btn btn-primary pull-right')); ?>
</div>
<?= form_open('admin/categories/edit/'.$terms->term_id); ?>
<!-- Title -->
<div class="form-group">
<?= form_label('Term Name', 'title'); ?>
<?php
$data = array(
'name' => 'title',
'id' => 'title',
'maxlength' => '100',
'class' => 'form-control',
'value' => $terms->title,
);
?>
<?= form_input($data); ?>
</div>
<!-- Type -->
<div class="form-group">
<?= form_label('Term Type', 'type'); ?>
<?php
$data = array(
'name' => 'type',
'id' => 'type',
'class' => 'form-control',
);
$options = array(
'' => 'Please select an option',
'category' => 'Category',
'tag' => 'Tag',
);
$value = array(
'value' => $relationship->type,
);
?>
<?= form_dropdown($data, $options, $value); ?>
</div>
<?= form_submit('mysubmit', 'Update Post Category', array('class' => 'btn btn-primary')); ?>
<?= form_close(); ?>
</div>
Now with the above done, you can continue with the posts!
The Post System
Easy stuff as well, as long as you make sure to add some inputs for the tag(categories and/or tags) cloud system.
Now here comes the complex stuff as you will have to retrieve data from the terms table(to be able to select them and then insert that data into the relationship table)
ADD:
PHP Code: public function add()
{
// Field Rules
$this->form_validation->set_rules('title', 'Title', 'trim|required|min_length[3]');
$this->form_validation->set_rules('body', 'Body', 'trim|required');
if ($this->form_validation->run() == FALSE) {
// Select Categories (Retrieved from a different model, terms table)
$categories_options = array();
$categories_options[0] = 'Select Categories';
$categories_list = $this->Terms_model->get_list();
foreach($categories_list as $cat){
$categories_options[$cat->term_id] = $cat->title;
}
$data['categories_options'] = $categories_options;
// Select Tags (Retrieved from a different model, terms table)
$categories_options = array();
$categories_options[0] = 'Select Categories';
$categories_list = $this->Terms_model->get_list();
foreach($categories_list as $cat){
$categories_options[$cat->term_id] = $cat->title;
}
$data['categories_options'] = $categories_options;
// Select Author (Retrieved from a different model, users table)
$user_options = array();
$user_options[0] = 'Select Username ID';
$user_list = $this->User_model->get_list();
foreach($user_list as $username){
$user_options[$username->id] = $username->username;
}
$data['user_options'] = $user_options;
// Load template
$this->template->load('admin', 'default', 'posts/add', $data);
} else {
$slug = str_replace(' /[^a-z0-9]+/i ', '-', $this->input->post('title'));
$slug = strtolower($slug);
// Page Data
$data = array(
'title' => $this->input->post('title'),
'slug' => $slug,
'body' => $this->input->post('body'),
);
$this->Post_model->add($data);
// Get last post id for inserting of categories data
$post_id = $this->db->insert_id();
// Get title for activity data
$activity_title = $data['title'];
// Create status/type many-to-many relationship
$data = array(
'post_id' => $post_id,
'user_id' => $this->input->post('user_id'),
'type' => 'post',
);
$this->Post_model->add_relationship($data);
// Create categories many-to-many relationship
$category = $this->input->post('categories[]');;
$data = array();
foreach ($category as $term){
$data[] = array(
'post_id' => $post_id,
'term_id' => $term,
'user_id' => $this->input->post('user_id'),
'status' => 'attached',
'type' => 'category',
);
}
$this->db->insert_batch('ci_relationship', $data);
// Create tags many-to-many relationship
$tag = $this->input->post('tags[]');;
$data = array();
foreach ($tag as $term){
$data[] = array(
'post_id' => $post_id,
'term_id' => $term,
'user_id' => $this->input->post('user_id'),
'status' => 'attached',
'type' => 'tag',
);
}
$this->db->insert_batch('ci_relationship', $data);
// Set Message
$this->session->set_flashdata('success', 'Post has been added');
// Redirect
redirect('admin/posts');
}
}
Now to edit the posts is something you will not only have to retrieve the terms to select them(as in the add method) but you will also need to retrieve the terms that are already attached to the specific post!
EDIT:
PHP Code: public function edit($id)
{
// Field Rules
$this->form_validation->set_rules('title', 'Title', 'trim|required|min_length[3]');
$this->form_validation->set_rules('body', 'Body', 'trim|required');
if ($this->form_validation->run() == FALSE) {
// Get item
$data['item'] = $this->Post_model->get($id);
// Get attached categories
$categories = array();
$categories[0] = 'Select Categories';
$categories_attached = $this->Post_model->the_categories($id);
if (is_array($categories_attached) || is_object($categories_attached))
{
foreach ($categories_attached as $cat)
{
$categories[$cat->term_id] = $cat->term_id;
}
}
$data['categories'] = $categories;
// Select Categories
$categories_options = array();
$categories_options[0] = 'Select Categories';
$categories_list = $this->Terms_model->get_list();
foreach($categories_list as $cat){
$categories_options[$cat->term_id] = $cat->title;
}
$data['categories_options'] = $categories_options;
// Get attached tags
$tags = array();
$tags[0] = 'Select Tags';
$tags_attached = $this->Post_model->the_tags($id);
if (is_array($tags_attached) || is_object($tags_attached))
{
foreach ($tags_attached as $tag)
{
$tags[$tag->term_id] = $tag->term_id;
}
}
$data['tags'] = $tags;
// Select Tags
$tags_options = array();
$tags_options[NULL] = 'Select Tags';
$tag_list = $this->Terms_model->get_list();
foreach($tag_list as $tag){
$tags_options[$tag->term_id] = $tag->title;
}
$data['tags_options'] = $tags_options;
// Load template
$this->template->load('admin', 'default', 'posts/edit', $data);
} else {
$slug = str_replace('/[^a-z0-9]+/i', '-', $this->input->post('title'));
$slug = strtolower($slug);
// Page Data
$data = array(
'title' => $this->input->post('title'),
'slug' => $slug,
'body' => $this->input->post('body'),
);
// Update Page
$this->Post_model->update($id, $data);
// Get title for activity data
$activity_title = $data['title'];
// Add/Remove term relationship
$this->Post_model->updateCats($id, $this->input->post('cat_id[]'));
$this->Post_model->updateTags($id, $this->input->post('tag_id[]'));
// Relationship Data
$data = array(
'post_id' => $id,
'user_id' => $this->input->post('user_id'),
'type' => 'post',
);
// Update author relationship
$this->Post_model->update_relationship($id, $data);
// Activity Array
$data = array(
'resource_id' => $id,
'type' => 'post',
'action' => 'updated',
'user_id' => $this->session->userdata('user_id'),
'message' => 'A post was updated (' . $activity_title . ')'
);
// Insert Activity
$this->Activity_model->add($data);
// Set Message
$this->session->set_flashdata('success', 'Post has been updated');
redirect('admin/posts');
}
}
In the code above there are 4 functions that are too important, two of them are to get the attached cats and tags, the 3 and 4 functions are two lines below the commented line Add/Remove term relationship.
You can check the function in the third question below(which will answer your second question as well).
Here is the model to retrieve the terms related to the specific post.id(which come from the relationship table):
PHP Code: // Categories for single
public function the_categories($id){
$this->db->select('*');
$this->db->from($this->relationship);
$this->db->where('post_id', $id);
$this->db->where('type', 'category');
$query = $this->db->get();
if($query->num_rows() >= 1){
return $query->result();
} else {
return false;
}
}
// Tags for single
public function the_tags($id){
$this->db->select('*');
$this->db->from($this->relationship);
$this->db->where('post_id', $id);
$this->db->where('type', 'tag');
$query = $this->db->get();
if($query->num_rows() >= 1){
return $query->result();
} else {
return false;
}
}
Here are the views for adding a post and editing a post:
Code: <div class="container">
<h3 class="page-header">Add Post</h3>
<?= validation_errors('<p class="alert alert-danger">'); ?>
<div class="row">
<?= form_open_multipart('admin/posts/add'); ?>
<div class="col-md-8">
<!-- Post Title -->
<div class="form-group">
<?= form_label('Post Title', 'title'); ?>
<?php
$data = array(
'name' => 'title',
'id' => 'title',
'maxlength' => '100',
'class' => 'form-control',
'placeholder' => 'Here goes the title',
'value' => set_value('title')
);
?>
<?= form_input($data); ?>
</div>
<!-- Post Body -->
<div class="form-group">
<?= form_label('Body', 'body'); ?>
<?php
$data = array(
'name' => 'body',
'id' => 'body',
'class' => 'form-control',
'placeholder' => 'Here goes the body',
'value' => set_value('body')
);
?>
<?= form_textarea($data); ?>
</div>
</div>
<div class="col-md-4">
<!-- Post Categories -->
<?php
$data = array(
'class' => 'form-control js-example-basic-multiple',
'value' => set_value('categories'),
'multiple' => 'multiple',
);
?>
<div class="form-group">
<?= form_label('Categories','categories'); ?>
<?= form_dropdown('categories[]', $categories_options, 0, $data); ?>
</div>
<!-- Post Tags -->
<?php
$data = array(
'class' => 'form-control js-example-basic-multiple',
'value' => set_value('categories'),
'multiple' => 'multiple',
);
?>
<div class="form-group">
<?= form_label('Categories','categories'); ?>
<?= form_dropdown('tags[]', $categories_options, 0, $data); ?>
</div>
</div>
<!-- Form buttons -->
<div class="col-md-12">
<?= form_submit('mysubmit', 'Add Post', array('class' => 'btn btn-primary')); ?>
<?= form_reset('mysubmit', 'Reset', array('class' => 'btn btn-default pull-right')); ?>
</div>
<?= form_close(); ?>
</div>
</div>
<script>
/* Categories with Select2 */
$(document).ready(function () {
$('.js-example-basic-multiple').select2({
tags: true,
tokenSeparators: [',', ' '],
});
});
</script>
PHP Code: <div class="container">
<h3 class="page-header">Edit Post</h3>
<?php if($this->session->flashdata('success')) : ?>
<div class="alert alert-success alert-dismissible">
<button type="button" class="close" data-dismiss="alert" aria-hidden="true">×</button>
<?= $this->session->flashdata('success') ?>
</div>
<?php endif; ?>
<?= validation_errors('<p class="alert alert-danger">'); ?>
<div class="row">
<?= form_open_multipart('admin/posts/edit/'.$item->id); ?>
<div class="col-md-8">
<!-- Post Title -->
<div class="form-group">
<?= form_label('Post Title', 'title'); ?>
<?php
$data = array(
'name' => 'title',
'id' => 'title',
'maxlength' => '100',
'class' => 'form-control',
'placeholder' => 'Here goes the title',
'value' => $item->title,
);
?>
<?= form_input($data); ?>
</div>
<!-- Post Body -->
<div class="form-group">
<?= form_label('Body', 'body'); ?>
<?php
$data = array(
'name' => 'body',
'id' => 'body',
'class' => 'form-control',
'placeholder' => 'Here goes the body',
'value' => $item->body,
);
?>
<?= form_textarea($data); ?>
</div>
</div>
<div class="col-md-4">
<!-- Post Categories -->
<div class="form-group">
<?php echo form_label('Categories', 'categories'); ?>
<?php
$data = array(
'id' => 'cat_id[]',
'name' => 'cat_id[]',
);
$class = array(
'class' => 'form-control js-example-basic-multiple',
'multiple' => TRUE,
);
?>
<?= form_dropdown($data, $categories_options, $categories, $class); ?>
<small>At least one category must be selected</small>
</div>
<!-- Post Tags -->
<div class="form-group">
<?php echo form_label('Tags', 'tags'); ?>
<?php
$data = array(
'id' => 'tag_id[]',
'name' => 'tag_id[]',
);
$class = array(
'class' => 'form-control js-example-basic-multiple',
'multiple' => TRUE,
);
?>
<?= form_dropdown($data, $tags_options, $tags, $class); ?>
<small>At least one tag must be selected</small>
</div>
<!-- Form buttons -->
<div class="col-md-12">
<?= form_submit('mysubmit', 'Update Post', array('class' => 'btn btn-primary')); ?>
<?= form_reset('mysubmit', 'Reset', array('class' => 'btn btn-default pull-right')); ?>
</div>
<?= form_close(); ?>
</div>
</div>
<script>
/* Categories with Select2 */
$(document).ready(function() {
$('.js-example-basic-multiple').select2({
tags: true,
tokenSeparators: [',', ' '],
});
});
</script>
With all of the codes above you should be able to create a nice many to many relationship table!(If you need me to paste the code in the model just tell me.... I dont think you will need them tho).
Not to forget how you can dispplay the post and terms attached to it:
Controller:
PHP Code: public function show($id)
{
// Get data
$data['posts'] = $this->Post_model->get($id);
$post = $this->Post_model->get($id);
// Meta
if($post){
$data['title'] = $this->settings->title.' | '. ucfirst($post->title);
//$data['description'] = $post->title;
}
// Get categories per post
$data['categories'] = $this->Post_model->the_categories($id);
// Get tags per post
$data['tags'] = $this->Post_model->the_tags($id);
// If empty show a 404 error
if(empty($data['posts'])){
show_404();
}
// Load template
$this->template->load('public', 'default', 'posts/show', $data);
}
PHP Code: <div class="container">
<?php if($posts) : ?>
<?= $posts->body; ?>
<?php endif; ?>
<!-- Categories -->
<?php if($categories) : ?>
<hr>
<h3>Categories</h3>
<?php foreach($categories as $cats) : ?>
<a href="<?= base_url('posts/category/'.$cats->term_id.'/'.get_term_slug($cats->term_id)) ?>"><span class="label label-orange"><i class="fa fa-tag" aria-hidden="true"></i> <?= get_term($cats->term_id); ?></span></a>
<?php endforeach; ?>
<?php endif; ?>
<!-- Tags -->
<?php if($tags) : ?>
<hr>
<h3>Tags</h3>
<?php foreach($tags as $tag) : ?>
<a href="<?= base_url('posts/tag/'.$tag->term_id.'/'.get_term_slug($tag->term_id)) ?>"><span class="label label-orange"><i class="fa fa-tag" aria-hidden="true"></i> <?= get_term($tag->term_id); ?></span></a>
<?php endforeach; ?>
<?php endif; ?>
</div>
There I'm using a helper to retrieve the data from the relationship table but that is stored in the terms table... I'll leave that to you as there are a lot of Youtube Videos which can help you how to create helpers.
That's it - You're DONE!
I did something similar not long ago with the help of some folks in Stackoverflow, you can check these questions out:
First Question:
https://stackoverflow.com/questions/5139...odeigniter
Second Question:
https://stackoverflow.com/questions/5144...in-foreach
Third Question:
https://stackoverflow.com/questions/5145...t-add-them
I do Front-End development most of the time
|
