Skip to main content
L
Lee Crockett
Known Participant
May 19, 2009
Answered

looping query

  • May 19, 2009
  • 1 reply
  • 1573 views

I can't quite seem to grasp the logic of this.  I have a bunch of tags for each of my blog entry, and am trying to filter by tags.

I want to do a query that lists only the blogs that have all the specified tags.  I have a table of blog entries, a table of tags, and I have a table of links, it has only the ID and two columns, one is the blogID and one is the tagID

I have a value list which are all the tags the user has requested in the session scope (SESSION.blogTags)

This query gets me all the blog posts which have ANY of the user requested tags

    <cfquery name="rsTaggedBlogs" datasource="committedsardine">

        SELECT blogID

        FROM blogTagLink

        <cfif SESSION.blogTags NEQ "">WHERE tagID IN(#SESSION.blogTags#)</cfif>

    </cfquery>

    <cfset taggedBlogs = ValueList(rsTaggedBlogs.blogID)>

I am then query for my blog entries using the taggedBlogs value list.  What I want to do is get only the blog IDs that have ALL the tags as opposed to ANY of the tags.

I'm thinking that some form of cfloop is used here, but just can't seem to get my head around it.  Any help would be greatly appreciated.

This topic has been closed for replies.
Correct answer mack_
)
GROUP BY blogID
HAVING count(tagID) = #ListLen( SESSION.blogTags )#
]]>

The addition that I made is to count the number of tags per blog and

make sure it's the same number as the number of tags in the session

variable.

Mack

1 reply

M
mack_Correct answer
Participating Frequently
May 19, 2009
)
GROUP BY blogID
HAVING count(tagID) = #ListLen( SESSION.blogTags )#
]]>

The addition that I made is to count the number of tags per blog and

make sure it's the same number as the number of tags in the session

variable.

Mack

L
Lee CrockettAuthor
Known Participant
May 19, 2009

Thanks Mack:

It doesn't seem to work, though I suspect it's probably my code and

not yours. Here is my complete code for this state:

<!-if searching by tags->

SELECT tags.tagName, tagsID FROM tags WHERE tagsID IN(#SESSION.blogTags#) = 0 SELECT blogID FROM blogTagLink WHERE tagID IN(#SESSION.blogTags#) ]]>

<!-get the information for the specified blog->

SELECT blog.blogID, blog.storyID, blog.blogDate, blogStories.storyID, blogStories.blogTitle, SUBSTRING(blogStories.blogBody,1,200) AS blogBody, images.imageName FROM blog, blogStories, images WHERE blog.storyID = blogStories.storyID AND images.imageID = blog.photoID AND blog.blogDate < "#todayDate#" AND blog.deleted = 'n' AND CONCAT(blogStories.blogBody, blogStories.blogTitle) LIKE '%#SESSION.blogKeywords#%' AND blog.blogID in(#taggedBlogs#)]]>

ORDER BY blog.blogDate DESC

</cfquery

M
mack_
Participating Frequently
May 19, 2009

Your code was eaten by the big forum monster, please repost it (if

you're responding via e-mail you can use bbcode tags around your

code).

--

Mack

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices