Results 1 to 14 of 14
  1. #1
    Join Date
    Jul 2009
    Posts
    32

    Unanswered: need to select articles that share common tags & vise versa

    I have a few dozen articles, and a few hundred tags. I want to do a search for articles that share tags in common.


    I have 3 tables

    tag_id | tag

    tagConnect_id | tagConnect_article | tagConnect_tag

    article_id | article

    ___________________________
    tagConnect_tag == tag_id
    tagConnect_article == article_id


    So say I wanted to see all the articles that are tagged 1, 3, 5 & 7


    And then again also how would I search for all the tags in common on a few given articles.

    I know I could do this easily using subqueries, but if I select dozens of tags, or articles... It becomes one heck of a query, and im sure there is a real way to do this.



    Thanks a ton in advance


    ------------------------------------------------------------
    example

    tags for article 1
    sensor
    electronics
    analog
    light

    tags for article 2
    sensor
    electronics
    digital
    light

    tags for article 3
    sensor
    electronics
    analog
    sound

    returns - sensor, electronics

    Or reverse search, find tags for article 3/ article 2 yields - sensor,
    electronics, light
    Last edited by ameyer; 11-11-09 at 00:54.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT article
      FROM articles
     WHERE article_id IN
           ( SELECT tagConnect_article
               FROM tagConnect
              WHERE tagConnect_tag IN ( 1,3,5,7 )
             GROUP
                 BY tagConnect_article
             HAVING COUNT(*) = 4 )
    note that your tagConnect_id column is useless -- the tagConnect table should have a compound PK consisting of (tagConnect_article,tagConnect_tag)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2009
    Posts
    32
    Awesome thank you. That worked.

    Wondering if something like this is possible:

    say you have 4 articles that are tagged

    A1
    tags: 1,2,4

    A2
    tags: 2,3,4,5

    A3
    tags: 3,4,5

    I want to select all the tags in common who also share 3,4 - so this would return 3,4,5

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by ameyer View Post
    say you have 4 articles that are tagged
    where did the 4th one go?


    Quote Originally Posted by ameyer View Post
    I want to select all the tags in common who also share 3,4 - so this would return 3,4,5
    huh? how does that work?

    i'm afraid i don't understand your example
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jul 2009
    Posts
    32
    This works. Doubt it is efficient though. Any way to simplify it?

    Code:
    SELECT tag
    FROM tags, topic, tagConnect
    WHERE tagConnect_article = topic_id
    AND tagConnect_tag = tag_id
    AND topic
    IN (
    
    	SELECT topic
    	FROM topic
    	WHERE topic_id
    	IN (
    	
    		SELECT tagConnect_article
    		FROM tagConnect
    		WHERE tagConnect_tag
    		IN ( 3, 4 ) 
    		GROUP 
    		BY tagConnect_article
    		HAVING COUNT( * ) =2
    	)
    )
    GROUP BY tag

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    remove the GROUP BY tag at the end of that query and see if it gives the results you want
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jul 2009
    Posts
    32
    Quote Originally Posted by r937 View Post
    where did the 4th one go?



    Quote Originally Posted by r937 View Post
    huh? how does that work?

    i'm afraid i don't understand your example
    Maybe the code I posted above will help.

    But I want:

    A bunch of tags. When I select one, I know what ones I could still select and have results. So If I click on 1,3 and no articles have 1,3,5 then dont show it as an option.

    But if 30 articles have tags 1,3 ...... and 5 articles have tags 1,3,5 then selecting 5 would help hone the search.

    So I just want to see what tags I could add to the search and still get results.


    PS
    Yeah, removing "group by tags" still works

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by ameyer View Post
    A bunch of tags. When I select one, I know what ones I could still select and have results.
    ...
    So I just want to see what tags I could add to the search and still get results.
    read those two lines carefully

    now imagine how confused i am
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jul 2009
    Posts
    32
    wow... Im really bad when I cant draw something. Sorry.


    Say I have 9 buttons - each one representing a tag.

    When I press one, I am given a list of all the articles that are tagged with that tag.

    When I press a second one, the first one is still selected. So now I want a result of articles that are tagged with both. (you already figured this out)


    What I want to know, is before I press another button down, will there any results. This way I am not wasting time looking for combinations that work.

    To do this, I need to know, what tags have not been selected, but who are also attributed to at least one of the articles in that returned list.

    So if I select button 1,2 and are given 20 results. But none of those 20 results are also tagged with 9, I dont want to press it.

    I figure it would be something like:
    if I select button 1,2 and are given 20 results.
    Return a list of tags that can be used in combination with 1,2 and still return results.
    So maybe come back as 4,6 (not 3 or 5)because

    10 articles are tagged 1,2,4
    5 are tagged 1,2,6
    1 is tagged 1,2,4,6

    I dont need to know how many articles would come back. Just what tags could be added to the current selection and still return at least 1 result.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by ameyer View Post
    What I want to know, is before I press another button down, will there any results. This way I am not wasting time looking for combinations that work.
    or combinations that don't work, i.e. don't improve the article search

    okay, i think i understand what you're asking

    the crucial point is this: how am i going to know, for that third button, that it will not refine the search further?

    there has to be some sort of visual indication over that button that says "don't press this button, it will have no additional results"

    so this is something that your application has to figure out when i press the second button

    otherwise, you might as well forget this whole idea and just let me press the third button

    the sql for the third button will probably run faster without all this "lookahead" coding, so you will get the unimproved answer a lot sooner and not waste so much time

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Jul 2009
    Posts
    32
    The visual part I have fixed/ finished. Depending on the amount of tags, there is a different metaphor. But one of them, The buttons just aren't there to be pressed.

    It could be faster to just press and find out here, but with thousands of tags, it may not be. Especially if only one returns a result.


    Quote Originally Posted by r937 View Post
    or combinations that don't work, i.e. don't improve the article search

    okay, i think i understand what you're asking

    the crucial point is this: how am i going to know, for that third button, that it will not refine the search further?

    there has to be some sort of visual indication over that button that says "don't press this button, it will have no additional results"

    so this is something that your application has to figure out when i press the second button

    otherwise, you might as well forget this whole idea and just let me press the third button

    the sql for the third button will probably run faster without all this "lookahead" coding, so you will get the unimproved answer a lot sooner and not waste so much time


  12. #12
    Join Date
    Jul 2009
    Posts
    32
    So... granted Im using less than par code, and just threw this together.
    Here is a proof of concept.
    title
    Last edited by ameyer; 11-12-09 at 01:44.

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by ameyer View Post
    Here is a proof of concept.
    title
    wow, that's pretty cool, that works really well

    nice job

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Jul 2009
    Posts
    32
    Thanks.

    No clue if the query that returns the still active tags is effective. But it sure does the job.

Posting Permissions

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