CODEWITHSUNDEEP
postgresql
yaodav
yaodav

Reputation: 1276

how to use NOT LIKE with result from query as pattern

I have these two tables 1 -

drop table if exists random_data;


create table random_data (
    name text,
    discription text
)
;

insert into random_data values ('david' , 'runner'),
                                ('mosh' , 'walker'),
                                ('ran' , 'sweemer'),
                                ('joh' , 'diver');

and the second

DROP TABLE IF EXISTS filter_test ;
CRATE TABLE filter_test (
    config_key text,
    config_value text
);
INSERET INTO filter_test select 'BLACKLIST_NAMES' , 'david,ran';
INSERET INTO filter_test select 'FILTER' , 'di,ja,aap';

I want to write a query the give me back the name from random_data that are not in BLACKLIST_NAMES from filter_test table and there discription not contained part of the pattern from FILTER I was able to complete the first part like this:

SELECT* from random_data where  name not in (with t as (
    SELECT
        max(case when config_key = 'BLACKLIST_NAMES' then config_value::text end) as names_blacklist
--      max(case when config_key = 'BLACKLIST_NAME_FILTERS' then config_value::text end) as BLACKLIST_NAME_FILTERS
    from filter_test
)
select 
    regexp_split_to_table(names_blacklist, ',') as names
from t
) order by name;

but I'm unable to complete the second part, I tried like this:

select  * from random_data where  name not in (with t as (
    select  
        max(case when config_key = 'BLACKLIST_NAMES' then config_value::text end) as names_blacklist        
    from filter_test
)
select 
    regexp_split_to_table(names_blacklist, ',') as names
from t
)   and  discription not like (with t as (
    select  
        max(case when config_key = 'BLACKLIST_NAME_FILTERS' then config_value::text end) as filters
    from filter_test
)
select 
    regexp_split_to_table(filters, ',') as filter
from t
)order by name

expected output

**name**    **discription**
  mosh          walker

Upvotes: 0

Views: 38

Answers (1)

Alan Millirud
Alan Millirud

Reputation: 1192

Is this you are looking for?

https://dbfiddle.uk/?rdbms=postgres_13&fiddle=9f44a43ef1d3d36e2b0f26bc71551751

    SELECT
      rd.name,
      rd.discription
    FROM random_data as rd

    LEFT JOIN (
      SELECT UNNEST(string_to_array(config_value, ',')) as config_value
      FROM filter_test
      WHERE config_key = 'BLACKLIST_NAMES'
    ) as names_filter
    ON rd.name like (CONCAT('%',  names_filter.config_value, '%'))

    LEFT JOIN (
      SELECT UNNEST(string_to_array(config_value, ',')) as config_value
      FROM filter_test
      WHERE config_key = 'FILTER'
    ) as desc_filter
    ON rd.discription like (CONCAT('%',desc_filter.config_value, '%'))


    WHERE names_filter.config_value IS NULL
    AND desc_filter.config_value IS NULL

Upvotes: 1

Related Questions