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):
[table=“width: 500, class: grid”]
[tr]
[td]id[/td]
[td]stream_id[/td]
[td]filter[/td]
[td]value[/td]
[/tr]
[tr]
[td]1[/td]
[td]1[/td]
[td]city[/td]
[td]1[/td]
[/tr]
[tr]
[td]2[/td]
[td]1[/td]
[td]venue[/td]
[td]0[/td]
[/tr]
[tr]
[td]3[/td]
[td]1[/td]
[td]org[/td]
[td]2[/td]
[/tr]
[tr]
[td]4[/td]
[td]1[/td]
[td]tag[/td]
[td]0[/td]
[/tr]
[tr]
[td]5[/td]
[td]2[/td]
[td]city[/td]
[td]1[/td]
[/tr]
[tr]
[td]6[/td]
[td]2[/td]
[td]venue[/td]
[td]0[/td]
[/tr]
[tr]
[td]7[/td]
[td]2[/td]
[td]org[/td]
[td]0[/td]
[/tr]
[tr]
[td]8[/td]
[td]2[/td]
[td]tag[/td]
[td]1[/td]
[/tr]
[tr]
[td]9[/td]
[td]3[/td]
[td]city[/td]
[td]1[/td]
[/tr]
[tr]
[td]10[/td]
[td]3[/td]
[td]venue[/td]
[td]3[/td]
[/tr]
[tr]
[td]11[/td]
[td]3[/td]
[td]org[/td]
[td]0[/td]
[/tr]
[tr]
[td]12[/td]
[td]3[/td]
[td]org[/td]
[td]0[/td]
[/tr]
[tr]
[td]13[/td]
[td]4[/td]
[td]city[/td]
[td]1[/td]
[/tr]
[tr]
[td]14[/td]
[td]4[/td]
[td]venue[/td]
[td]3[/td]
[/tr]
[tr]
[td]15[/td]
[td]4[/td]
[td]org[/td]
[td]0[/td]
[/tr]
[tr]
[td]16[/td]
[td]4[/td]
[td]tag[/td]
[td]2[/td]
[/tr]
[tr]
[td]17[/td]
[td]4[/td]
[td]tag[/td]
[td]3[/td]
[/tr]
[/table]
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!
V