SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Enthusiast
    Join Date
    Sep 2007
    Location
    Kelowna, BC
    Posts
    73
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Need help with a query

    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!

    V

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by luco.vico View Post
    I need a query that would return stream_id's for streams where the event should be added.
    Code:
    SELECT stream_id
      FROM filters
     WHERE filter = 'city'  AND value IN ( 0 , 1 )
        OR filter = 'venue' AND value IN ( 0 , 3 )
        OR filter = 'org'   AND value IN ( 0 , 2 )
        OR filter = 'tag'   AND value IN ( 0 , 1 )
    GROUP
        BY stream_id
    HAVING COUNT(*) = 4 -- number of filters matched
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Sep 2007
    Location
    Kelowna, BC
    Posts
    73
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you, r937. I will try and report back.

  4. #4
    SitePoint Enthusiast
    Join Date
    Sep 2007
    Location
    Kelowna, BC
    Posts
    73
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    r937 - seems to work perfectly. Not that I am very surprised given your credentials. lol

    Thank you kindly!

  5. #5
    SitePoint Enthusiast
    Join Date
    Sep 2007
    Location
    Kelowna, BC
    Posts
    73
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have a follow-up question. The query seems to work, but not in all cases. The situation where it does not work is when the new event is tagged with more than one matching 'tag'. Since the IN array is dynamically generated based on the new event details, if this is the generated query:

    Code:
    SELECT stream_id
      FROM filters
     WHERE filter = 'city'  AND value IN ( 0 , 1 )
        OR filter = 'venue' AND value IN ( 0 , 3 )
        OR filter = 'org'   AND value IN ( 0 , 2 )
        OR filter = 'tag'   AND value IN ( 0 , 1, 2 )
    GROUP
        BY stream_id
    HAVING COUNT(*) = 4 -- number of filters matched
    Stream that has filters defined as:

    city = 1
    venue = 3
    org = 2
    tag = 1,2

    will not be in the results (since COUNT(*) will be 5).

    And filter defined as:

    city = 1
    venue = 3
    org = 3
    tag = 1,2

    will match, even though 'org' is different, but 2 matching 'tag's will pass it.

    So, how do I adjust the query so that a stream_id is returned only if each filter matches at least once?

    Thank you!

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    would the query make more sense if it were written like this --
    Code:
    SELECT stream_id
      FROM filters
     WHERE filter = 'city'  AND value IN ( 0 , 1 )
        OR filter = 'venue' AND value IN ( 0 , 3 )
        OR filter = 'org'   AND value IN ( 0 , 2 )
        OR filter = 'tag'   AND value IN ( 0 , 1 )
        OR filter = 'tag'   AND value IN ( 0 , 2 )
    GROUP
        BY stream_id
    HAVING COUNT(*) = 5 -- number of filters matched
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Enthusiast
    Join Date
    Sep 2007
    Location
    Kelowna, BC
    Posts
    73
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmm, I don't think that will work:

    If I have a stream with filters:

    city = 1
    venue = 3
    org = 2
    tag = 1

    And an event added that generates the modified query (city = 1, venue = 3, org = 2, tag = array(1,2)), it won't match since it will have COUNT(*) = 4.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by luco.vico View Post
    And an event added that generates the modified query (city = 1, venue = 3, org = 2, tag = array(1,2))
    you cannot put "array(1,2)" into a query statement, it's not valid sql

    what you want is to count the number of different value tag/value pairs

    so in that case it should be 5
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    How about moving the checks to the HAVING?
    Code:
    SELECT stream_id
    FROM filters
    GROUP
        BY stream_id
    HAVING  
        MAX(CASE WHEN filter = 'city'  AND value IN ( 0 , 1 ) THEN 1
                 ELSE 0
            END) = 1
    AND MAX(CASE WHEN filter = 'venue' AND value IN ( 0 , 3 ) THEN 1
                 ELSE 0
            END) = 1
    ...

  10. #10
    SitePoint Enthusiast
    Join Date
    Sep 2007
    Location
    Kelowna, BC
    Posts
    73
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    you cannot put "array(1,2)" into a query statement, it's not valid sql

    what you want is to count the number of different value tag/value pairs

    so in that case it should be 5
    guido2004: I'll try that one

    Sorry for being confusing - I'm not putting array(1,2) into the SQL...that was my way of saying that the event was tagged with 2 tags having IDs 1 and 2.
    -Victor

  11. #11
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,178
    Mentioned
    63 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by r937 View Post
    Code:
    SELECT stream_id
      FROM filters
     WHERE filter = 'city'  AND value IN ( 0 , 1 )
        OR filter = 'venue' AND value IN ( 0 , 3 )
        OR filter = 'org'   AND value IN ( 0 , 2 )
        OR filter = 'tag'   AND value IN ( 0 , 1 )
    GROUP
        BY stream_id
    HAVING COUNT(*) = 4 -- number of filters matched
    Is there a performance issue that you are using this method rather than just using AND for all 4 criteria?

  12. #12
    SitePoint Enthusiast
    Join Date
    Sep 2007
    Location
    Kelowna, BC
    Posts
    73
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If using AND instead of OR, I think you won't get any matches because no single record will have a filter 'city' AND filter 'venue' AND etc
    -Victor

  13. #13
    SitePoint Enthusiast
    Join Date
    Sep 2007
    Location
    Kelowna, BC
    Posts
    73
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    How about moving the checks to the HAVING?
    Code:
    SELECT stream_id
    FROM filters
    GROUP
        BY stream_id
    HAVING  
        MAX(CASE WHEN filter = 'city'  AND value IN ( 0 , 1 ) THEN 1
                 ELSE 0
            END) = 1
    AND MAX(CASE WHEN filter = 'venue' AND value IN ( 0 , 3 ) THEN 1
                 ELSE 0
            END) = 1
    ...

    This seems to work. I'll keep testing and see if there are use cases where it fails. Thanks!!
    -Victor


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •