Query users on filter applied to a one-to-many relationship table postgresql-CodePudding

We currently have a users table with a one-to-many relationship on a table called steps. Each user can have either four steps or seven steps. The steps table schema is as follows:

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

I am trying to query all of the users who have a status of 1 on all of their steps. So if they have either 4 or 7 steps, they must all have a status of 1.

I tried a join with a check on step 4 (since a step cannot be complete without the previous one being complete as well) but this has issues if someone with 7 steps completed step 4 but not 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;

CodePudding user response：

The bool_and aggregate function should help you to identify the users with all their steps at status = 1 whatever the number of steps.

Then the array_agg aggregate function can help to find the updated_at date associated to the last step for each user by ordering the dates according to order DESC and selecting the first value in the resulting array [1] :

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