Postgres return rows that dont intersect on two text arrays in different tables

Question

I have two tables ads & device.

Ads is a table of ads which has text array column called 'tags'. Each ad also has a 'brand'

Device is a table of devices which display ads and has two text array columns 'blacklisted_tags' & 'blacklisted_brands'. The device cannot display ads from brands or tags that are blacklisted by it.

Basically, a device can only show (improper postgres syntax):

ad.brand != ANY(device.blacklisted_brands) & array_intersection(device.blacklisted_tags, ads.tags) is null.

The task is to return all the ads that can be shown on the device given device id.
Eg:

Given device1, query should return all ads (ad1, ad2, ad3, ad4) since 'Shopping' isnt a blacklisted tag for any ad nor are any ads for 'Amazon', 'Walmart' available. 
Given device2, query should return all ads (ad1, ad2, ad3, ad4) since it has no blacklisted tags or brands.
Given device3, query should return ad2, ad3, ad4 (not ad1) since ad1 has a 'Food' tag. 
Given device4, query should return ad1, ad4 (not ad2, ad3) since ad2, ad3 are of 'Samsung' brand which is blacklisted for device4
Given device5, query should return ad2, ad3, ad4

I'm trying to construct a postgres query which will probably have a template as:

select ads.* from ads, device
.. <something> ..
where device.device_id = 'device1'

I'm unable to figure out what that something will be. I have created a fiddle here. How should a query for this be written?

Gordon Linoff · Accepted Answer · 2020-06-03 13:53:12Z

1

If I understand correctly, you are looking the ads that can be shown on a device. I think this does what you want:

select a.*, d.*
from ads a join
     device d
     on not (d.blacklisted_tags && a.tags) and
        not (d.blacklisted_brands && array[a.brand::text]);

I'm not sure if the brand on device needs to match. Your question is not clear on that.

answered Jun 3, 2020 at 13:53

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user248884 Over a year ago

Your query is bang on. I have edited the question to make it clear. Thanks!

Stefanov.sm · Accepted Answer · 2020-06-03 14:43:41Z

0

A bit old style implicit join

select ads.ad_id, device.device_id from ads, device
where
  not ads.brand = ANY(device.blacklisted_brands) 
  and not(device.blacklisted_tags && ads.tags);

edited Jun 3, 2020 at 14:43

answered Jun 3, 2020 at 14:33

Stefanov.sm

13.3k2 gold badges25 silver badges24 bronze badges

Collectives™ on Stack Overflow

Postgres return rows that dont intersect on two text arrays in different tables

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related