问题描述
我在创建最佳SQL查询时遇到问题.我有私人消息系统，用户可以将一条消息发送给许多用户或用户组.收件人存储在单个文本列中(不要问我为什么我不负责设计)，就像这样:
I have a problem with creating optimal SQL query. I have private messages system where user can send single message to many of users or groups of users. Recipients are stored in single text column (don't ask me why is that I wasn't responsible for designing that) like that:
[60,63,103,68]
此外，我在用户所属的组中添加了新的文本列，以便可以将其作为组包含在数据库中:
Additionaly I've added new text column where is placed group which user belongs to so I can have as a groups in database:
[55,11,11,0]
现在，我想获取所有用户(接收者)及其群组.我有表用户和组ID之间的关系.问题是单个用户可以属于多个组，例如，用户60可以位于组ID 55和11中.我想以最佳方式进行操作(列中可以存储50个以上的接收器...)所以我可以这样写查询:
Now I want to get all users (receivers) and their groups. I have table where relation between user and group id. The problem is that single user can belong to multiple groups, for example user 60 can be in group ID 55 and 11. I would like to do it in the most optimal way (there can be 50+ receivers stored in column...) so I can write query like that:
SELECT u.name, u.last_name, g.group_name
FROM
user u
LEFT JOIN
group g ON u.id = g.user_id
WHERE
u.id IN (".$users.") and
g.id IN (".$groups.")
不幸的是，查询返回的组名可能不正确-与放置在WHERE中的组ID有关.我可以创建PHP foreach并使用我拥有的ID来获取用户及其组:
Unfortunately group name returned by query might by not proper - connected with the group ID i placed in WHERE. I may create PHP foreach and get user and his group using IDs I have:
foreach($user as $key => $single)
{
$sql = "...
where u.id = $single AND g.id = $group[$key] ";
}
但是我认为这是非常糟糕的方法.有没有办法在单个查询中获取用户和指定的组?
but I think this is very bad way. Is there any way to get user and specified group in single query?
推荐答案
由于用户和组仅通过列表中的常规位置进行链接，因此您需要使用它们.
Since users and groups are only linked by their ordinal positions in the list, you need to make use of that.
快速而肮脏的方法是 unnest() :
The quick and dirty method would be to unnest() in parallel:
SELECT u.name, u.last_name, g.group_name
FROM (
SELECT unnest(string_to_array('3,1,2', ',')::int[]) AS usr_id -- users
, unnest(string_to_array('10,11,12', ',')::int[]) AS grp_id -- groups
) sel
JOIN usr_grp ug USING (usr_id, grp_id)
JOIN usr u USING (usr_id)
JOIN grp g USING (grp_id);
请注意如何替换 SQL关键字例如user或group作为标识符.
Note how I replaced SQL key words like user or group as identifiers.
这样，数组中顺序位置相同的元素(从逗号分隔的列表转换)形成一行.两个数组都必须具有相同数量的元素，否则该操作将产生笛卡尔积.根据您的描述，这里应该是这种情况.添加代码以验证是否可能违反该条件.
This way, elements with the same ordinal positions in the array (converted from a comma-separated list) form a row. Both arrays need to have the same number of elements or the operation will result in a Cartesian product instead. That should be the case here, according to your description. Add code to verify if that condition might be violated.
尽管上述方法工作可靠，但这是SRF(集合返回函数)的非标准Postgres功能，对此有些人不屑一顾.
While the above works reliably, it is a non-standard Postgres feature of SRF (set returning functions) which is frowned upon by some.
有更清洁的方法可以做到这一点.而即将发布的Postgres 9.4版本将提供一个新功能:WITH ORDINALITY，允许使用更简洁的代码.这个相关的答案说明了这两个方面:
带有元素编号的PostgreSQL unnest()
There are cleaner ways to do it. And the upcoming version 9.4 of Postgres will ship a new feature: WITH ORDINALITY, allowing for much cleaner code. This related answer demonstrates both:
PostgreSQL unnest() with element number
这篇关于多对多关系，IN运算符和不正确结果的可能性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！