Results 1 to 13 of 13
Thread: Need help with a query
Sep 9, 2012, 10:22 #1
- Join Date
- Sep 2007
- Kelowna, BC
- 0 Post(s)
- 0 Thread(s)
Need help with a query
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
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!