Postgres select where all joined rows match

Question

I'm trying to solve the following problem in Postgres SQL, but I'm lost as to know how I should construct it. I have two tables, services:

ID
1
2
3

and service_provision:

SERVICE_ID  PROVISION   REQUIRED
1           A           FALSE
2           B           TRUE
2           C           FALSE
3           B           TRUE
3           C           TRUE

The first part of the query is easy. A user may have any number of a set of issues. I need to be able to return services which have provision for at least one of the issues. So this does that easily enough where user has issues A and B:

select service.id
from services s
left join service_provision p
on s.id = p.service_id
where
p.provision = ANY (ARRAY['A','B'])

returns services 1,2,3

I'm stuck on the next part - the service can also have a number of 'required' issues. The user must also have all these issues for the service to be returned. So if user has issue 'A' and the service provides for 'A', 'B' and 'B' has the 'required' property, then the service should not be returned.

The test case I want to pass is: user issues: ['A', 'B'] returns: 1,2

The strategy in my head, is to do a second join on the service_provision table again, get that into an array, where I can then add a where condition to compare that array against the search one. Something like this:

select service.id
from services s
left join service_provision p
on s.id = p.service_id
left join service_provision p2
on s.id = p2.service_id and p2.required = true
where
p.provision = ANY (ARRAY['A','B'])
and array_agg(p2.provision) <@ ARRAY['A','B']

My problem is this is not allowed, and I don't know how else to solve it. Any ideas welcome...

user_13808319 · Answer

Since you are comfortable with arrays, please give this a try:

with inparms as (
  select array['A', 'B'] as inarray
), mappings as (
  select service_id, 
         array_agg(provision) as all_provs, 
         array_agg(provision) filter (where required) req_provs
    from service_provision
   group by service_id
)
select m.* 
  from mappings m
  cross join inparms i
 where i.inarray && all_provs
   and (req_provs is null or i.inarray @> req_provs);

 service_id | all_provs | req_provs 
------------+-----------+-----------
          2 | {B,C}     | {B}
          1 | {A}       | 
(2 rows)

inparms takes in your list of provision values.

mappings groups the entries into arrays to represent all and required provision values.

The comparison in the query's where clause looks for any overlap with && and then sees if req_provs is null or if req_provs is entirely contained within inarray.

user_4454732 · Answer

Here's something simple that comes close to what you need:

select * 
  from services s 
   join service_provision sp
  where
    s.id in ( select service_id from service_provision req 
                where req.required == true 
                and req.provision in ('A', 'B')
            )
    and sp.provision in ('A', 'B')

This will find services with at least one required provision. I think you want to ensure that all required provisions are present. Maybe you can adapt this subquery approach to match what you need.

Postgres select where all joined rows match

2 Answers