Note from Jeff: Like PDO? Check out how to use it with Codeigniter
Codeigniter with PDO Thanks!
Nested comments are a nice way to display the flow of a conversation, but figuring out how to pull that data from the tables for display without writing horrible recursive hits on your database is a pain. Nested sets are usually the best solution, but have their pros & cons. I'd like to show you a different technique that works quite well for things like blog comments, using a little CodeIgniter library I threw together.
The idea behind this style is to track the "lineage" of each comment - its parent, grand-parent, etc - all in one field. Then sort on that field. That's it. Like falling off a cliff.
So, a record 3 levels deep might look like:
|3||Child 1 a||1||2||00001-00002-00003||2|
The full technique is explained here: http://ferdychristant.com/blog//archive/DOMM-7QJPM7 I'm only going to explain how to use my library to work with the data in this way.
To make this technique work, your table needs to have a primary key, a parent_id, and two fields we'll call "lineage" and "deep", although you can configure those to be what you want. (This is in addition to your actual comment field, the link to the outside table (article_id), and any other data you want to track)
Notice variables at the top of the library:
//set this to whatever is most useful for you protected $db = 'default'; protected $table = 'hierarchy'; //if you rename your table fields, also rename them here protected $primary_key = 'id'; protected $parent_id = 'parent_id'; protected $lineage = 'lineage'; protected $deep = 'deep';
This allows you to use any set of names to wish - just make sure they match the functionality. So if you have an existing table with parent_id's you can "snap it" right onto to your table.
IMPORTANT NOTE!: I have added the ability to use a $config and initialize() function so that you can use multiple instances of the library. This works like most CodeIgniter libraries - $config is an array, the keys of which are the variables listed directly above. If you use two configurations of the library in the same page, set up the second one by calling:
$config = array('table'=>'hierarchy'); $this->load->library('mahana_hierarchy'); $this->mahana_hierarchy->initialize($config); $config = array('table'=>'products'); $this->mahana_hierarchy->initialize($config);
You can pick which variables need overridden - you don't need to set them all
If you are adding this to an existing dataset, the next thing you want to do is update all your table rows to have a lineage and deep value, by running:
This will read over all your table rows and set the appropriate values.
And now...you are ready to use it!
So, to insert data, we need to feed an array of the normal record data - i.e., comment body, create date, etc. If you include a parent id it will build from that, otherwise it will assume this is a new top-level record. No need to do anything with lineage or deep - the library will calculate all of that for you.
//add a top-level parent $new_comment['name'] = "A new parent record"; $insert_id = $this->mahana_hierarchy->insert($new_comment); // add a child $new_comment['name'] = "A new parent record"; $new_comment['parent_id'] = $insert_id; $this->mahana_hierarchy->insert($new_comment);
If we want to display this data,
$data = $this->mahana_hierarchy->get(); var_dump($data); (this example is showing the where clause usage - it assumes you've been building up your data and have an article_id = 5) $data = $this->mahana_hierarchy->where(array('article_id '=>5))->get(); var_dump($data);
The get() function returns all the records from a certain parent (optional) down. We can use the where() function as a chainable method that simply uses the CodeIgniter's Active record class where(). If we needed to start at a specific point in the hierarchy, pass that record's id as a parameter in get();
Additional functions include:
Returns a single record
Returns all direct children of the parent, but no other descendents
Returns all the descendents of the given parent (but not the parent)
Fetch all descendent records based on the parent id, ordered by their lineage, and groups them as a mulit-dimensional array.
get_ancestors($id, $remove_this = false)
Returns an array of all the ancestors of the given record. Second parameter will leave out the requesting record or not
Returns an row array of the parent of the given record.
Inserts new record, as explained above
This is a simple Active Record update, and will not resync all your lineages! Run resync if you need to do that
Delete a record. It will NOT delete its children as well unless you tell it to
Just lets you know how deep your child records are getting
Hope that proves useful - as always, interested in comments & code pull requests