MySQL查询 - 使用URL名称来识别数据，其中数据被组织成层次结构 [英] MySQL query - indentiyfing data using URL names where data is organised into a hierachy
本文介绍了MySQL查询 - 使用URL名称来识别数据，其中数据被组织成层次结构的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有一个名为content的mysql表，用于存储内容管理系统的内容数据。
注意：所有内容都使用父标识列组织成hierachy。
+ ---- + ------------ + ------- ---------- + -------- +
| id | lug | content_type_id |父|
+ ---- + ------------ + ----------------- + -------- +
| 1 |投资组合| 5 | 0 |
| 2 |关于我们| 1 | 0 |
| 3 | find-us | 1 | 0 |
| 4 |联系我们| 1 | 2 |
| 5 | find-us | 1 | 4 |
+ ---- + ------------ + ----------------- + -------- +
我需要一个查询来选择表中的正确行，这取决于slug名称。
我有两个可能的路径，用户可以访问：
/ find-us /
/ about-us / contact-us / find-us /
我可以想到一个解决方案：
这是创建另一个列的完整路径：
full_path/ pre>
--------
/ portfolio /
/ about-us /
/ find-us /
/ about-us / contact-us /
/ about-us / contact-us / find-us /
但是有没有任何一种聪明的方法可以用来选择正确的行。我不知道如果创建另一个列有完整的路径名是一个伟大的想法（因为这些都有可能改变），个人我只想使用它作为最后的手段。
感谢。
解决方案如果DBMS支持递归查询，这将是可能的：
DROP SCHEMA tmp CASCADE;
CREATE SCHEMA tmp;
CREATE TABLE tmp.webmeuk
（id INTEGER NOT NULL PRIMARY KEY
，slug VARCHAR
，content_type_id INTEGER NOT NULL
，parent_id INTEGER REFERENCES tmp。 webmeuk（id）
）;
INSERT INTO tmp.webmeuk（id，slug，content_type_id，parent_id）
VALUES（0，'HTTP：//pr0n.mysite.xx'，5，NULL）
， 'portfolio'，5,0）
，（2，'about-us'，1,0）
，（3，'find-us'，1,0）
4，'contact-us'，1,2）
，（5，'find-us'，1,4）
;
- 一个视图的房间
CREATE VIEW tmp.reteview AS（
WITH RECURSIVE xx AS（
SELECT w0.id AS id
， w0.slug AS slug
，w0.content_type_id AS content_type_id
，w0.slug AS fullpath
FROM tmp.webmeuk w0
WHERE w0.parent_id IS NULL
UNION
SELECT w1.id AS id
，w1.slug AS slug
，w1.content_type_id AS content_type_id
，xx.fullpath ||'/':: text || w1.slug AS fullpath
FROM tmp.webmeuk w1，xx
WHERE w1.parent_id = xx.id
）
SELECT * FROM xx
）;
SELECT * FROM tmp.reteview;
- 更改一行数据
UPDATE tmp.webmeuk
SET slug ='what-about-us'
WHERE id = 2;
SELECT * FROM tmp.reteview;
输出：
code>注意：drop cascades到另外两个对象
详细信息：drop cascades to table tmp.webmeuk
丢弃级联以查看tmp.closure
DROP SCHEMA
CREATE SCHEMA
注意：CREATE TABLE / PRIMARY KEY将为表webmeuk创建隐式索引webmeuk_pkey
CREATE TABLE
INSERT 0 6
CREATE VIEW
id | lug | content_type_id | fullpath
---- + ----------------------- + ---------------- - + ------------------------------------------------ ---
0 | HTTP：//pr0n.mysite.xx | 5 | HTTP：//pr0n.mysite.xx
1 |投资组合| 5 | HTTP：//pr0n.mysite.xx/portfolio
2 |关于我们| 1 | HTTP：//pr0n.mysite.xx/about-us
3 | find-us | 1 | HTTP：//pr0n.mysite.xx/find-us
4 |联系我们| 1 | HTTP：//pr0n.mysite.xx/about-us/contact-us
5 | find-us | 1 | HTTP：//pr0n.mysite.xx/about-us/contact-us/find-us
（6行）
更新1
id | lug | content_type_id | fullpath
---- + ----------------------- + ---------------- - + ------------------------------------------------ --------
0 | HTTP：//pr0n.mysite.xx | 5 | HTTP：//pr0n.mysite.xx
1 |投资组合| 5 | HTTP：//pr0n.mysite.xx/portfolio
3 | find-us | 1 | HTTP：//pr0n.mysite.xx/find-us
2 |什么关于我们| 1 | HTTP：//pr0n.mysite.xx/what-about-us
4 |联系我们| 1 | HTTP：//pr0n.mysite.xx/what-about-us/contact-us
5 | find-us | 1 | HTTP：//pr0n.mysite.xx/what-about-us/contact-us/find-us
（6 rows）
I have a mysql table called content which stores content data for a content management system.
NOTE: all content is organised into a hierachy using a parent id column.
+----+------------+-----------------+--------+ | id | slug | content_type_id | parent | +----+------------+-----------------+--------+ | 1 | portfolio | 5 | 0 | | 2 | about-us | 1 | 0 | | 3 | find-us | 1 | 0 | | 4 | contact-us | 1 | 2 | | 5 | find-us | 1 | 4 | +----+------------+-----------------+--------+I need a query to select the correct row in the table depending on what the slug name is. The problem is when slugs have the same name.
I have two possible paths, which a user can visit:
/find-us/and
/about-us/contact-us/find-us/I can think of one solution:
Which is to create an another column with the full paths:
full_path -------- /portfolio/ /about-us/ /find-us/ /about-us/contact-us/ /about-us/contact-us/find-us/But are there any kind of clever methods I can use to select the correct row. I am not sure if creating another column with full path names is such a great idea (because these have the potential to change), personally I would only like to use that as a last resort.
Thanks.
解决方案This would be possible if your DBMS supported recursive queries:
DROP SCHEMA tmp CASCADE; CREATE SCHEMA tmp ; CREATE TABLE tmp.webmeuk ( id INTEGER NOT NULL PRIMARY KEY , slug VARCHAR , content_type_id INTEGER NOT NULL , parent_id INTEGER REFERENCES tmp.webmeuk(id) ); INSERT INTO tmp.webmeuk( id , slug, content_type_id , parent_id ) VALUES( 0 , 'HTTP://pr0n.mysite.xx', 5 , NULL ) , ( 1 , 'portfolio', 5 , 0 ) , ( 2 , 'about-us', 1 , 0 ) , ( 3 , 'find-us', 1 , 0 ) , ( 4 , 'contact-us', 1 , 2 ) , ( 5 , 'find-us', 1 , 4 ) ; -- a room with a view CREATE VIEW tmp.reteview AS ( WITH RECURSIVE xx AS ( SELECT w0.id AS id , w0.slug AS slug , w0.content_type_id AS content_type_id , w0.slug AS fullpath FROM tmp.webmeuk w0 WHERE w0.parent_id IS NULL UNION SELECT w1.id AS id , w1.slug AS slug , w1.content_type_id AS content_type_id , xx.fullpath || '/'::text || w1.slug AS fullpath FROM tmp.webmeuk w1, xx WHERE w1.parent_id = xx.id ) SELECT * FROM xx ); SELECT * FROM tmp.reteview ; -- Change one row of data UPDATE tmp.webmeuk SET slug = 'what-about-us' WHERE id = 2; SELECT * FROM tmp.reteview ;Output:
NOTICE: drop cascades to 2 other objects DETAIL: drop cascades to table tmp.webmeuk drop cascades to view tmp.closure DROP SCHEMA CREATE SCHEMA NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "webmeuk_pkey" for table "webmeuk" CREATE TABLE INSERT 0 6 CREATE VIEW id | slug | content_type_id | fullpath ----+-----------------------+-----------------+--------------------------------------------------- 0 | HTTP://pr0n.mysite.xx | 5 | HTTP://pr0n.mysite.xx 1 | portfolio | 5 | HTTP://pr0n.mysite.xx/portfolio 2 | about-us | 1 | HTTP://pr0n.mysite.xx/about-us 3 | find-us | 1 | HTTP://pr0n.mysite.xx/find-us 4 | contact-us | 1 | HTTP://pr0n.mysite.xx/about-us/contact-us 5 | find-us | 1 | HTTP://pr0n.mysite.xx/about-us/contact-us/find-us (6 rows) UPDATE 1 id | slug | content_type_id | fullpath ----+-----------------------+-----------------+-------------------------------------------------------- 0 | HTTP://pr0n.mysite.xx | 5 | HTTP://pr0n.mysite.xx 1 | portfolio | 5 | HTTP://pr0n.mysite.xx/portfolio 3 | find-us | 1 | HTTP://pr0n.mysite.xx/find-us 2 | what-about-us | 1 | HTTP://pr0n.mysite.xx/what-about-us 4 | contact-us | 1 | HTTP://pr0n.mysite.xx/what-about-us/contact-us 5 | find-us | 1 | HTTP://pr0n.mysite.xx/what-about-us/contact-us/find-us (6 rows)
这篇关于MySQL查询 - 使用URL名称来识别数据，其中数据被组织成层次结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
