I have 2 tables.
Table1 "ads":
id | description | status |
---|---|---|
1 | Ad number 1 | active |
2 | Ad number 2 | active |
Table 2 "ads_features":
id | ad | feature |
---|---|---|
1 | 1 | feature 1 |
2 | 1 | feature 2 |
I need a SQL function that will return values from 1 and add new column with values from Table 2. But should return only values if ALL items from function parameter "features" (type array) are found in Table 2. Here is what I have so far:
CREATE OR REPLACE FUNCTION filter_ads(features text[] default NULL)
RETURNS table (
id uuid,
description text,
status text,
ads_features text[]
)
LANGUAGE plpgsql
AS $$
BEGIN
RETURN QUERY
SELECT
ads.*,
ARRAY(
SELECT featuresTable.feature
FROM ads_features featuresTable
WHERE featuresTable.ad = ads.id
) AS ads_features
FROM ads
WHERE
(ads.status = 'active') AND
features IS NULL OR COALESCE(features, '{}'::text[]) = '{}'::text[] OR EXISTS(
SELECT 1
FROM ads_features featuresTable
WHERE (featuresTable.ad = ads.id) AND
(featuresTable.feature = ANY(features))
);
END;
$$;
The problem is that if I pass "[feature 1, feature 3]" as parameter, function will still return ad with ID 1, as ANY operator is satisfied. But I want to return records when ONLY ALL array items are found in table 2. If I use ALL operator it does not work at all. I get zero records.
Expected results:
Scenario 1:
select * from filter_ads(ARRAY(“feature 1”, “feature 2”))
id | description | status | ads_features |
---|---|---|---|
1 | Ad number 1 | active | [feature 1, feature 2] |
Scenario 2:
select * from filter_ads(ARRAY(“feature 1”, “feature 3”))
No records found
Basically this is part of a filter function where user will select among many check boxes in UI and records that match all conditions will be shown.
feature
contains only one value. You need an array of all features for that ad. group by ad
and aggregate all of its features into an array with array_agg
.
Then use having
to constrain the aggregate and @>
to check if one array contains the other.
select
ads.*,
array_agg(feature) as features
from ads
join ads_features
on ads.id = ads_features.ad
where ads.status = 'active'
group by ads.id
having array_agg(feature) @> array['feature2', 'feature1']