在应用于一对多关系表postgresql的筛选器上查询用户

Question

我们目前在一个名为steps的表上有一个具有one-to-many关系的users表。每个用户可以有四个步骤或七个步骤。steps表模式如下：

id | user_id | order | status
-----------------------------
#  |  #      |1-7/1-4| 0 or 1

我正在尝试查询所有步骤中状态为1的用户。因此，如果它们有4个或7个步骤，它们的状态都必须为1。

我在第4步尝试了一个带有检查的连接(因为一个步骤如果没有前一个步骤也完成，就无法完成(，但如果有7个步骤的人完成了第4步而没有完成第7步，则会出现问题。

select u.first_name, u.last_name, u.email, date(s.updated_at) as completed_date
from users u
join steps s on u.id = s.user_id
where s.order = 4 and s.status = 1;

Answer 1

bool_and聚合函数应帮助您识别所有步骤都处于状态=1的用户，无论步骤数是多少。

然后，array_agg聚合函数可以通过根据order DESC对日期进行排序并在结果数组[1]:中选择第一个值来帮助找到与每个用户的最后一步相关联的updated_at日期

SELECT u.first_name, u.last_name, u.email
, s.completed_date
FROM users u
INNER JOIN 
( SELECT user_id
, (array_agg(updated_at ORDER BY order DESC))[1] :: date as completed_date
FROM steps
GROUP BY user_id
HAVING bool_and(status :: boolean) -- filter the users with all their steps status = 1
) AS s
ON u.id = s.user_id