How to implement hierarchical cms sites?

问题:

I'm coding a little learning CMS project and I've hit a brick wall that's stopping me to complete the next step. I know I should be taking KISS (Keep It Simple, Stupid) into account, but I think it would nice, to be able to group pages hierarchicaly.

The problem is that I want page [root]->fruits->tropical->bananas to be accessible only from this url: http://localhost/cms/fruits/tropical/bananas/. What I came up with until now is that cms table has a parent field that points to its parent. The question is: How to parse uri adress and select a row from DB with as few queries/efficiently as possible?

Table structure:
Id
Slug
...
...
...
ParentId

All help and advice is kindly accepted.

回答1:

Here is the table structure that I used for testing this -

CREATE TABLE  `test`.`pages` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `slug` varchar(45) NOT NULL,
    `title` varchar(45) NOT NULL,
    `content` text NOT NULL,
    `parent_id` int(10) unsigned DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `UQ_page_parent_id_slug` (`parent_id`,`slug`),
    CONSTRAINT `FK_page_parent_id` FOREIGN KEY (`parent_id`) REFERENCES `pages` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Note the unique key on (parent_id, slug). This is key to getting the best performance from the following query. I tested this with 50k rows and it still returned in less than 1ms for a five slug path - /cms/slug-1/slug-2/slug-3/slug-4/slug-5/

Here is the PHP code that I have come up with to build a suitable query -

<?php

// I will assume the rest of the url has already been stripped away
$url = '/fruits/tropical/bananas/';

// lets just make sure we don't have any leading or trailing /
$url = trim($url, '/');

// now let's split the remaining string based on the /
$aUrl = explode('/', $url);

/**
* Now let's build the query to retrieve this
*/

// this array stores the values to be bound to the query at the end
$aParams = array();

$field_list = 'SELECT p1.* ';
$tables = 'FROM pages p1 ';
$where = "WHERE p1.parent_id IS NULL AND p1.slug = ? ";

// this array stores the values to be bound to the query at the end
$aParams[] = $aUrl[0];

// if we have more than one element in our array we need to add to the query
$count = count($aUrl);

for ($i = 1; $i < $count; $i++) {

    // add another table to our query
    $table_alias = 'p' . ($i + 1);
    $prev_table_alias = 'p' . $i;
    $tables .= "INNER JOIN pages $table_alias ON {$prev_table_alias}.id = {$table_alias}.parent_id ";

    // add to where clause
    $where .= "AND {$table_alias}.slug = ? ";
    $aParams[] = $aUrl[$i];

    // overwrite the content of $field_list each time so we
    // only retrieve the data for the actual page requested
    $field_list = "SELECT {$table_alias}.* ";

}

$sql = $field_list . $tables . $where;

$result = $this->db->query($sql, $aParams);

回答2:

If the page only exists on one url the easiest way to resolve the problem will be to store a hash of the full url in a indexed field of it's own:

SELECT * FROM table WHERE page = MD5('http://localhost/cms/fruits/tropical/bananas/')

Although if you are going to go down the hierarchical route you may find the following useful: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

回答3:

I particularly like Bill Karwin's ( https://stackoverflow.com/users/20860/bill-karwin ) presentation on hierarchies:

http://en.slideshare.net/billkarwin/models-for-hierarchical-data

Recursively grab all data based on a parent id

You should take a look at his solution with a closure table. I found it particularly useful. It might be what you need.

回答4:

You've tagged this question as CodeIgniter so this an answer specific to that.

You can use it's routeing capabilities to force the URL but handle the request in the required way.

What you would have is something like:

$route['cms/fruit/(:any)'] = 'fruit/$1';
$route['cms/fruit/(:any)/(:any)'] = 'fruit/$1/$2';

The first line would forward all URLs that start with cms/fruit to the fruit controller and pass across the fruit type as the first variable (maybe the fruit name as the second variable too). The second line is a fall back in case it didn't take care of the fruit name.

Combine this with the base path in the config and you could automatically set 'cms' in the URL too, if it's something that should always be in the URL.

回答5:

Are you using codeigniter for this development? This answer below is based on the web application framework from codeigniter. So here goes,

---

The problem is that I want page [root]->fruits->tropical->bananas to be accessible only from this url: http://localhost/cms/fruits/tropical/bananas/.

So create a function in your controller with function name fruits and with two parameters? For example

class Cms extends CI_Controller {
  ...
  ...
  ...
  public function __construct() {
     $this->load->model('cms_model');
  }

  public function fruits($tropical, $bananas) {

     $string = $this->cms_model->getPage($tropical, $bananas);

     // load the view you want.
     $this->load->view('');
  }
  ...
  ...
  ...

}

---

What I came up with until now is that cms table has a parent field that points to its parent. The question is: How to parse uri adress and select a row from DB with as few queries/efficiently as possible?

Table cms:
Id
Slug
ParentId

Table cms_parent:
Id

Let's describe with two example table shown above, cms table and cms parent table. You did not specify exactly in your question on what is your query want or the query result return. So below is my guess based on your question description that is, query two table by joining them using the common key and then apply the condition where.

// select * from cms t1 join cms_parent t2 on t1.ParentId = t2.Id where t1.Id = '' and t2.ParentId = 'level1';
public function getPage($level0, $level1) {
    $this->db->select('*');
    $this->db->from('cms');
    $this->db->join('cms_parent', 'cms.ParentId = cms_parent.Id');
    $this->db->where('cms.Id', $level0);
    $this->db->where('cms.ParentId', $level1);

    $query = $this->db->get();

    // return one row from database.
    return $query->row();
}