如何实现分层cms站点？ [英] How to implement hierarchical cms sites?

问题描述

我编写的一个小型学习CMS项目，我打了一堵砖墙，阻止我完成下一步。我知道我应该考虑KISS（保持简单，愚蠢），但我认为很高兴，能够分组页面分层。

问题是我想要页面 [root] - > fruits-> tropical-> bananas 只能从此网址访问： http：// localhost / cms / fruits / tropical / bananas / 。我到目前为止的是，cms表有一个父字段指向其父。问题是：如何解析uri地址并从DB中尽可能少地查询/有效地选择一行？

 表结构：
 Id 
 Slug 
 ... 
 ... 
 ... 
 ParentId 
   
 
解决方案
这里是我用来测试这个表结构 -  
  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`），
 
）ENGINE = InnoDB DEFAULT CHARSET = utf8; 
  
记下唯一键（parent_id，slug）。这是从以下查询获得最佳性能的关键。我测试了这个与50k行，它仍然返回在少于1ms的一个五段路径 -   / cms / slug-1 / slug-2 / slug-3 / slug-4 / slug-5 / / code> 
 
 
 
这是我创建一个合适的查询的PHP代码 -  
 <？php 
 
 //我假设其余的URL已经被删除
 $ url ='/ fruits /热带/香蕉
 
 //只是确保我们没有任何前导或尾随/ 
 $ url = trim（$ url，'/'）; 
 
 //现在让我们根据/ 
 $ aUrl = explode（'/'，$ url）拆分剩余的字符串; 
 
 / ** 
 *现在让我们构建查询以检索
 * / 
 
 //此数组存储要绑定到查询结束
 $ aParams = array（）; 
 
 $ field_list ='SELECT p1。*'; 
 $ tables ='FROM pages p1'; 
 $ where =WHERE p1.parent_id IS NULL AND p1.slug =？; 
 
 //该数组存储要绑定到查询末尾的值
 $ aParams [] = $ aUrl [0]; 
 
 //如果我们在数组中有多个元素，我们需要添加到查询
 $ count = count（$ aUrl）; 
 
 for（$ i = 1; $ i <$ count; $ i ++）{
 
 //为查询添加另一个表
 $ table_alias =' p'。 （$ i + 1）; 
 $ prev_table_alias ='p'。 $ i; 
 $ tables。=INNER JOIN页$ table_alias ON {$ prev_table_alias} .id = {$ table_alias} .parent_id; 
 
 //添加到where子句
 $ where。=AND {$ table_alias} .slug =？; 
 $ aParams [] = $ aUrl [$ i]; 
 
 //每次覆盖$ field_list的内容，所以我们
 //只检索实际请求的页面的数据
 $ field_list =SELECT {$ table_alias}。* ; 
 
} 
 
 $ sql = $ field_list。 $ tables。 $ where; 
 
 $ result = $ this-> db-> query（$ sql，$ aParams）; 
  
 
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.
解决方案
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);


                        
这篇关于如何实现分层cms站点？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！