使用内部联接编写用SQL编写的继承查询? [英] Writing an inheritance query written in SQL using an inner join?

问题描述

我承认，我对SQL(使用mySQL)的了解几乎不超出数据库管理所需的标准查询，并且我的大部分数据操作都是通过php完成的.

我一直热衷于改变这一点，直到现在为止一直很成功，如果有人可以建议我如何在包含父"，子"的标准表上为继承查询创建存储过程，我将不胜感激.字段，内部连接到权限表上.

作为样本数据(出于演示目的):

table_group_inherit

parent        child
------------------------
admin         moderator
member        guest
super_admin   admin
moderator     member

table_group_permissions

moderator    move_post
----------------------
super_admin  create_forum
admin        move_forum
guest        view_post
member       create_post
member       edit_post

然后，我将在组名称(例如"admin")上调用该过程，以返回其权限的数组("move_forum"，"view_post"，"create_post"，edit_post").我不知道在这里是否需要迭代或递归(我读了一些有关mySQL的文章，不支持此内容?)，但是欢迎您提出任何建议.

(注:我不赞成将权限用作带有TRUE/FALSE检查的字段，因为我打算将更多字段附加到每个权限(例如描述).

解决方案

如果您正在查看树层次结构，则嵌套集模型可以很好地工作，但是会涉及到继承表结构的重大变化./p>

如果您要实现任意有向图(例如，您有一个作者"个人资料可以发布文章，但不能发表评论，而有一个主持人"个人资料可以发表评论，但不能发表文章)，则可能想寻找其他解决方案.

一种可能性是放弃继承，并手动为每个组设置权限.

另一种可能性是使用继承表来存储直接继承和间接继承(即，一个节点将使用直接"关系与其所有子代相关联，而其所有后代也将使用间接"关系进行关联. ).此策略要求您在更改任何一种直接关系时都重新创建表中的所有间接关系(这可以通过使用简单的INSERT SELECT来完成)，但是它的优点是只需要一个联接即可访问所有直接关系.后裔.

基本思想是:

CREATE TABLE group_inherit (
  parent INT NOT NULL, 
  child INT NOT NULL, 
  distance INT NOT NULL, 
  PRIMARY KEY (parent,child)
);

/* Clean up indirect relations */
DELETE FROM group_inherit WHERE distance <> 0;

/* Repeat this for each D > 0 until the maximum distance is reached */
INSERT IGNORE INTO (parent, child, distance) 
SELECT fst.parent, snd.child, D
FROM group_inherit fst
INNER JOIN group_inherit snd ON snd.parent = fst.child
WHERE fst.distance = 0 AND snd.distance = D - 1;

/* Select all permissions for a user type */
SELECT perm.*
FROM group_permissions perm
INNER JOIN group_inherit ON perm.moderator = child
WHERE parent = ?

应该进行距离循环，直到没有其他可用的距离D-1元素为止，可以使用选择查询或如果有的话，插入有关插入多少行的元信息来完成.

I confess that my knowledge of SQL (using mySQL) extends little beyond the standard queries required for database management, and that the majority of my data manipulation has been done through php.

I have been keen to change that, and have enjoyed success until now, and would appreciate it if someone could advise me on how I create a stored procedure for an inheritance query on a standard table containing 'parent', 'child' fields, inner joined on a permissions table.

As sample data (for demonstration purposes):

table_group_inherit

parent        child
------------------------
admin         moderator
member        guest
super_admin   admin
moderator     member

table_group_permissions

moderator    move_post
----------------------
super_admin  create_forum
admin        move_forum
guest        view_post
member       create_post
member       edit_post

Then I would call the procedure on a group name (e.g. 'admin')to return an array of its permissions ('move_forum', 'view_post', 'create_post', edit_post'). I do not know whether I require iteration or recursion here (I read something about mySQL not supporting this?), but any advice is welcome.

(N.B. I have elected against using permissions as fields with a TRUE/FALSE check as I intend to attach further fields to each permission e.g. description).

解决方案

If you're looking at a tree hierarchy, then the nested set model works fairly well, but involves a major change in the structure of your inheritance table.

If you're implementing an arbitrary directed graph (for instance, you have an "author" profile who can publish articles but not moderate comments, and a "moderator" profile who can moderate comments but not publish articles), you might want to look for some other solution.

One possibility is to give up on inheritance and manually set the permissions for every group.

Another possibility is to use the inheritance table to store both direct and indirect inheritance (that is, a node would be related to all its children using a "direct" relationship, as well as all its descendants using an "indirect" relationship). This strategy requires you to re-create all the indirect relationships in the table whenever you change one of the direct relationships (this can be done by using a simple INSERT SELECT), but has the advantage of only requiring a single join to access all descendants.

The basic idea is:

CREATE TABLE group_inherit (
  parent INT NOT NULL, 
  child INT NOT NULL, 
  distance INT NOT NULL, 
  PRIMARY KEY (parent,child)
);

/* Clean up indirect relations */
DELETE FROM group_inherit WHERE distance <> 0;

/* Repeat this for each D > 0 until the maximum distance is reached */
INSERT IGNORE INTO (parent, child, distance) 
SELECT fst.parent, snd.child, D
FROM group_inherit fst
INNER JOIN group_inherit snd ON snd.parent = fst.child
WHERE fst.distance = 0 AND snd.distance = D - 1;

/* Select all permissions for a user type */
SELECT perm.*
FROM group_permissions perm
INNER JOIN group_inherit ON perm.moderator = child
WHERE parent = ?

The loop on distance should be done until there are no more elements of distance D-1 available, which can be done using a select query or, if you have it, meta-information about how many lines were inserted.

这篇关于使用内部联接编写用SQL编写的继承查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！