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...
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.
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.