SQL Select refactor and reuse complex where clause-CodePudding

We're doing the following union, which re-uses the where clause in 3 places:

select distinct *
from (
  select p.* -- finding parents
  from projects p
  join projects c on c.parent_id = p.id
  where p.is_chosen
 union all
  select c.* -- finding children
  from projects p
  join projects c on c.parent_id = p.id
  where not p.is_chosen and c.is_chosen
) x

Now we want to add a more complex where clause instead of is_chosen.

Is there a way to factor out the where clause so that it can be reused in all 3 places?

For example, how would we simplify:

select distinct *
from (
  select p.* -- finding parents
  from projects p
  join projects c on c.parent_id = p.id
  where p.is_chosen and p.last_active > now() - interval '7' day and (select count(*) from items i where i.project_id = p.id and i.last_active is not null) > 5
 union all
  select c.* -- finding children
  from projects p
  join projects c on c.parent_id = p.id
  where not (p.is_chosen and p.last_active > now() - interval '7' day and (select count(*) from items i where i.project_id = p.id and i.last_active is not null) > 5) and (c.is_chosen and c.last_active > now() - interval '7' day and (select count(*) from items i where i.project_id = c.id and i.last_active is not null) > 5)
) x

We've tried a WITH clause at the beginning, unsuccessfully.

CodePudding user response：

Just out of curiosity, is this select below give same results ?

select p.* from projects p
left join projects parent on (p.parent_id = parent.id)
where p.is_chosen 
  and p.last_active > now() - interval '7' day 
  and (select count(*) from items i where i.project_id = p.id and i.last_active is not null) > 5
and (
  p.parent_id is null and exists (select 1 from projects where parent_id = p.id )
  or 
  p.parent_id is not null and not (parent.is_chosen 
       and parent.last_active > now() - interval '7' day 
       and (select count(*) from items i where i.project_id = parent.id and i.last_active is not null) > 5
  )
)

CodePudding user response：

I'll try to reformulare your question

You need to get all projects for which is_chosen = TRUE and last_active is no older than 7 days, that have more than 5 rows in items table with last_active IS NOT NULL. The query must exclude child projects if parent is in the results.

If it is so, than you can use a CTE like this

WITH cte AS (
    SELECT p.*
    FROM projects p
    WHERE 
    (
        p.parent_id IS NOT NULL -- project is a child
        OR EXISTS (
            -- project should have some children
            SELECT FROM projects WHERE parent_id = p.id
        )  
    )
    -- other conditions 
    AND p.is_chosen 
    AND p.last_active > now() - interval '7' day
    AND (
        SELECT COUNT(*) 
        FROM items i 
        WHERE i.project_id = p.id 
              AND i.last_active IS NOT NULL
    ) > 5
)
SELECT *
FROM cte t
WHERE NOT EXISTS (
    -- do not include child if parent presents
    SELECT FROM cte WHERE id = t.parent_id 
)

Please, check a demo with modified sample data from your previous querstion.