有这样一个站点:
用户存在user表中,然后有role角色表,通过user_to_role表实现user和role之间的多对多关系。
另外有action表,里面存有站点的各个url——或者说功能。然后有action_group表,通过action_to_action_group表和action表形成多对多关系。这样在给用户发放权限的时候就可以发放“套餐”,这样管理会方便一些。
而role和action_group通过role_to_action_group表形成多对多关系。
这样当一个user登录后,通过user->user_to_role->role->role_to_action_group->action_group->action_to_action_group->action这样的链条就可以查询到该user有权访问的全部action。这样的关系通过ORM来写实在是太复杂了,所以最好还是使用原生SQL来写。
因为涉及到跨表,所以现代SQL推荐采用JOIN方法来处理。
在SQL中
SELECT tb1.name AS name, tb2.mark AS mark
FROM tb1 LEFT JOIN tb2 ON tb1.id=tb2.tb1_id
WHERE tb1.name='test'
JOIN语法有左侧的表tb1和右侧的表tb2
LEFT JOIN的意思就是说,以左侧的tb1为基础形成一个扩展表,扩展表会把tb1和tb2的各个column加入到扩展表中,然后把tb1中的所有条目填充到扩展表中。然后以ON为条件,针对tb1的每一条到右侧的tb2中查找对应的条目,如果有符合ON条件的则把tb2的条目填充到扩展表中的对应条目。如果tb2中没有符合ON条件的,则以null填充到扩展表中tb1条目对应的条目。然后以WHERE条件过滤扩展表,最后把结果输出。
以user、role、user_to_role这个多对多关联为例。如果把user表作为左侧的tb1,那么扩展表中每一条对应的是一个user,而且在user表中user是独特的unique,非重复的,所以每个user都不同。但是这个多对多关联中一个user要对应多个role,如果最后的扩展表每一条对应一个user,那么也就只能对应最多一个role,这明显是不符合预期的。
所以处理多对多关联的时候,应该以中间关系表作为LEFT JOIN的左表。这样扩展表的每一条就是一个user和role的对应关系,然后以WHERE过滤就可以得到一个user的全部role、或者一个role的全部user、或者其他的需求。比如下面这条语句就可以查到一个user到底有哪几条对应的user_to_role关系。
SELECT user.id AS id, user.name AS user_name, user_to_role.id AS relationship_id
FROM user_to_role LEFT JOIN user ON user.id=user_to_role.user_id
WHERE user.name='test'
这样形成的扩展表本质上也是一个“表”,只要把这个扩展表与role表再做一个JOIN就可以查出对应的role。因为这个新表曾经被user过滤过,所以新表中每一条目的role应该是不同的,此时就不需要在意JOIN的顺序了。
SELECT user.id AS id, user.name AS user_name, role.name AS role_name
FROM (user_to_role LEFT JOIN user ON user.id=user_to_role.user_id)
LEFT JOIN role ON user_to_role.role_id=role.id
WHERE user.name='test'
其实更加复杂的user->user_to_role->role->role_to_action_group->action_group->action_to_action_group->action查询链条类似,在进行LEFT JOIN时,一定要注意把可能含有多个重复条目的表放在LEFT JOIN的左侧,然后把不含有重复条目的放在LEFT JOIN的右侧。JOIN之后形成的扩展表也视为一个表,这个新的表也可以继续JOIN,JOIN时一样注意JOIN的顺序即可。对于现代SQL,JOIN的顺序是可以通过括号来控制的。所以这个长链条的最后的查询语句如下:
SELECT public.user.id, public.user.name, public.action.url, public.action.name
FROM (public.action_to_action_group LEFT JOIN ((public.role_to_action_group LEFT JOIN
((public.user_to_role LEFT JOIN public.user ON public.user_to_role.user_id=public.user.id)
LEFT JOIN public.role ON public.user_to_role.role_id=public.role.id)
ON public.role_to_action_group.role_id=public.role.id)
LEFT JOIN public.action_group ON public.role_to_action_group.action_group_id=public.action_group.id)
ON public.action_to_action_group.action_group_id=public.action_group.id)
LEFT JOIN public.action ON public.action.id=public.action_to_action_group.action_id
WHERE public.user.name='test'
因为这里用的是PostgreSQL,所以所有的表名前面要加上schema名,这里用的是public。如果是MySQL,不用加schema名