need to select articles that share common tags & vise versa

ameyer · #1 (**permalink**) 11-10-09, 23:29

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

r937 · #2 (**permalink**) 11-11-09, 03:26

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)

ameyer · #3 (**permalink**) 11-11-09, 09:54

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

r937 · #4 (**permalink**) 11-11-09, 10:30

Quote:

Originally Posted by ameyer

say you have 4 articles that are tagged

where did the 4th one go?

Quote:

Originally Posted by ameyer

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

ameyer · #5 (**permalink**) 11-11-09, 10:30

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

r937 · #6 (**permalink**) 11-11-09, 10:33

remove the GROUP BY tag at the end of that query and see if it gives the results you want

ameyer · #7 (**permalink**) 11-11-09, 10:37

Quote:

Originally Posted by r937

where did the 4th one go?

Quote:

Originally Posted by r937

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

r937 · #8 (**permalink**) 11-11-09, 10:50

Quote:

Originally Posted by ameyer

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

ameyer · #9 (**permalink**) 11-11-09, 11:37

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.

r937 · #10 (**permalink**) 11-11-09, 11:50

Quote:

Originally Posted by ameyer

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

ameyer · #11 (**permalink**) 11-11-09, 11:57

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

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