[eluser]zoreli[/eluser]
Hi
First of all, I would like to thanks this community for pointing me in right direction. Special thanks goes to Joost Van Veen from codeigniter.tv, and to member of this forum, Mauricio de Abreu Antunes
I am trying to accomplish some tasks keeping the same logic, but I need some help here.
I have the following problem. Very often, I will need to use join, and I guess, that I will have to write new method to my_model, but simply said, I am not on that level yet, so i need help in writing this new method. Here is what I am trying to accomplish:
Code from my_model:
Code: class MY_Model extends CI_Model
{
public $table_name = '';
public $primary_key = '';
public $primaryFilter = 'intval'; // html entitites for string keys
public $order_by = '';
public function __construct()
{
parent::__construct();
}
public function get($ids = FALSE)
{
// Set the flag - if we passed signle id we should return a single record
$single = $ids == FALSE || is_array($ids) ? FALSE : TRUE;
// Limit the results to one or more ids
if($ids !== FALSE)
{
// $ids should be always be an array
is_array($ids) || $ids = array($ids);
// sanitize ids
$filter = $this->primaryFilter;
$ids = array_map($filter, $ids);
$this->db->where_in($this->primary_key, $ids);
}
// Set order by if it was not already set
count($this->db->ar_orderby) || $this->db->order_by($this->order_by);
// Return the results
$single == FALSE || $this->db->limit(1);
$method = $single ? 'row_array' : 'result_array';
return $this->db->get($this->table_name)->$method();
}
public function get_by($key,$val = FALSE, $orwhere = FALSE, $single = FALSE)
{
// Limit the results
if(! is_array($key))
{
$this->db->where(htmlentities($key), htmlentities($val));
} else {
$key = array_map('htmlentities',$key);
$where_method = $orwhere = TRUE ? 'or_where' : 'where';
$this->db->$where_method($key);
}
// Return the results
$single == FALSE || $this->db->limit(1);
$method = $single ? 'row_array' : 'result_array';
return $this->db->get($this->table_name)->$method();
}
public function get_key_value($key_field,$value_field,$ids = FALSE)
{
// get records
$this->db->select($key_field . ', '. $value_field);
$result = $this->get($ids);
// turn results into key=>value pair array
$data = array();
if(count($result) > 0)
{
if($ids != FALSE && ! is_array($ids))
{
$result = array($result);
}
foreach($result as $row)
{
$data[$row[$key_field]] = $row[$value_field];
}
}
return $data;
}
public function get_assoc($ids = FALSE)
{
$result = $this->get($ids);
// turn results into an associative array
if($ids != FALSE && ! is_array($ids))
{
$result = array($result);
}
$data = $this->to_assoc($result);
return $data;
}
public function to_assoc($result = array())
{
$data = array();
if(count($result) > 0)
{
foreach($result as $row)
{
$tmp = array_values(array_slice($row,0,1));
$data[$tmp[0]] = $row;
}
}
return $data;
}
I have the table name faq, which looks like this:
Code: faqid int(11)
catid int(11)
question text
question_en text
answer text
answer_en text
sorder int(11)
visible tinyint
As you can imagine, the catid represent the id, and I would like to display the name of that id, which is located in faq_categories table. This is something that I will have to do very often, in products, services, news...etc, so I need to write a method for that.
Anyone can help me with instruction & examples how to write such methods, using same logic that is already used in my_model?
Regards, Zoreli
[eluser]InsiteFX[/eluser]
You could use a pivot table which would hold the indexes to all of your other tables for fast look ups.
[eluser]zoreli[/eluser]
Thanks for the reply. The idea with pivot tables sound like rocket science to me at this time. Needles to mention, two months ago using my model looked as rocket science to me as well. So, can you please elaborate your idea further?
Regards, Zoreli
[eluser]zoreli[/eluser]
Shameless bump ... :-)
[eluser]JoostV[/eluser]
There are multiple ways you can accomplish this. But if you use categories for all kinds of content, then the most generic way is probably this. You only need a single method in you category model. You can use this method to fetch all content linked to a category, no matter what table that content is in.
Create a categories table:
Code: CREATE TABLE `categories` (
`cat_id` bigint(20) NOT NULL AUTO_INCREMENT,
`cat_title` varchar(50) NOT NULL,
PRIMARY KEY (`cat_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci COMMENT='Categories';
Create a table containing only links to categories:
Code: CREATE TABLE `category_links` (
`catlink_id` bigint(20) NOT NULL AUTO_INCREMENT,
`catlink_catid` int(11) NOT NULL DEFAULT '0',
`catlink_parent_table` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
`catlink_parent_field` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
`catlink_parent_id` int(11) NOT NULL,
PRIMARY KEY (`catlink_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Links categories to content in other tables';
In your category model, create this method:
Code: /**
* Return all items for a certain category
* @param mixed $cat_id can be an int or an array of integers
* @param string $parent_table
* @param string $parent_field
* @return array
*/
public function get_with_category ($cat_id, $parent_table, $parent_field)
{
// Select all fields from parent table and category table
$this->db->select($parent_table . '.*, ' . $this->table_name . '.*');
// Join with parent table through category links table
$this->db->join('category_links', $this->table_name . '.' . $this->primary_key . '=category_links.catlink_cat_id');
$this->db->join($parent_table, 'category_links.catlink_parent_id=' . $parent_table . '.' . $parent_field);
// Limit to content associated with one or more categories
is_array($cat_id) || $cat_id = array($cat_id);
$this->db->where_in('category_links.catlink_cat_id', $cat_id);
return parent::get();
}
In your controllers you can now get any content from any table, associated with one or more categories.
Code: $this->load->model('categories');
// Example: get all articles that have category 20
$articles = $this->categories->get_with_category(20, 'articles', 'art_id');
// Example: get all projects that have either category 19 or 20
$projects = $this->categories->get_with_category(array(19, 20), 'projects', 'pro_id');
This method will produce queries like
Code: SELECT `articles`.*, `categories`.*
FROM (`categories`)
JOIN `category_links` ON `categories`.`cat_id`=`category_links`.`catlink_cat_id`
JOIN `articles` ON `category_links`.`catlink_parent_id`=`articles`.`art_id`
WHERE `category_links`.`catlink_cat_id` IN (19, 20)
ORDER BY `cat_lft`
You can add to this by creating a field catlink_rank in the category_links table, and order by that field.
[eluser]Mauricio de Abreu Antunes[/eluser]
Thanks dude. 
I agree with JoosTv.
That's is the best way to go.
[eluser]zoreli[/eluser]
Hi
Thanks to all of you that reply on my post. I will pull up my sleeves and start to work on this solution right now.
Once again thanks to this great community for helping me to elevate my knowledge on next level.
Regards, Zoreli
[eluser]Lykos22[/eluser]
Hi, I'd like some help please, as I face quite the same difficulty. I'm also using the same MY_Model and I have 3 tables
Code: **Products**
product_id //eg 1
product_name //eg apple imac
description // mplah mplah
category_id // 3 - desktops
...
**Features**
feature_id // 1
feature_name // processor
category_id // takes the parent-category eg computers with id = 1
and a pivot table **product_features** that should have something like this
product_id // 1
feature_id // 1
feature_value // intel i5
Each table also represents a model, so i have a product_model for products, feature_model for features and a product_feature_model for product_features (which i'm not sure yet if its needed)
In my view I have a form in which i do inserting/editing, in which I want to fetch all the features of the specific category (in my example 'computers') and the values that also belong to the specific product.
Here's the query I've tested in phpmyadmin and also the method
Code: SELECT `features`.*, `product_features`.`value`
FROM (`features`)
LEFT JOIN `product_features` ON `features`.`feature_id`=`product_features`.`feature_id`
WHERE `category_id` = '1'
------------------------------------------------
// this is inside feature_model
public function get_features_values($category_id){
$this->db->select('features.*, product_features.value')
->join('product_features', 'features.feature_id=product_features.feature_id', 'left');
return parent::get_by('category_id', $category_id);
}
but I also want to specify and which is the product in order to get the correct feature_values.
How can I make this in order to create the method, and in which model should I place my method too??
|
