Hi there!

I'm building an event-related application where I have user defined "streams" of events. When a new event is added to the system, I need to find the streams to which the event should be added.

The event takes place at a venue (which belongs to a city), belongs to a category, is organized by an organization and is tagged with certain tags (among other things).

The streams have filters. So I can have a stream that only wants events from a particular city (without caring which venue it is at, who the organizer is, etc). I can have another stream where I only want events by a certain organizer. Or I can have a stream where I only want events from, say, 3 categories in a certain city.

A filter can be set to "any" (represented as 0) or it can be one or more id's of the thing it's filtering. To be flexible, I created a "filters" table with the following columns:

id | stream_id | filter | value

Say I only have 4 filters which are saved as such (0 means any; multiple values for the same filter are saved as separate records - see row 16 and 17):

id stream_id filter value
1 1 city 1
2 1 venue 0
3 1 org 2
4 1 tag 0
5 2 city 1
6 2 venue 0
7 2 org 0
8 2 tag 1
9 3 city 1
10 3 venue 3
11 3 org 0
12 3 org 0
13 4 city 1
14 4 venue 3
15 4 org 0
16 4 tag 2
17 4 tag 3

I need a query that would return stream_id's for streams where the event should be added.

Say I have an event added that is in:
city => 1
venue => 3
organizer =>2
tag => 1

It should return stream_id: 1,2,3.

I have not been able to figure out how to apply the "where_clauses" to records sharing the same stream_id.

I'm open to changing how the data is represented as well if that will make the query easier.

Thank you very much!