tags table search help

[omnibreak]

ok i have 3 tables (simplified for example):

table posts
---------------------------
postid int()
title varchar()
---------------------------

tags
---------------------------
tagid int()
name varchar()
---------------------------

post_tags
---------------------------
tagid int()
postid int()
---------------------------

Ok i want to be able the user to search using multiple tags to find posts. The system only allows for users to look for tags that are already in the tags table, so no need for % like % or anything. Just strait, search = tags.name.

There are 5000 posts so far and there are about 1000 tags, each post can have many tags.

The user can search for as many tags as they like.

First what is the most efficient way of doing this? I cant help the feeling that finding all the related post/tags then all the places where the tag.name = the search is super inefficient.

Finally as there can be multiple tags being searched for is there a way to make results that have all tags be at the top, then ones with multiple tags then results with only one tag.

[r937]

SELECT posts.postid
     , post.title
     , COUNT(*) AS tagged_count
  FROM tags
INNER
  JOIN post_tags
    ON post_tags.tagid = tags.tagid
INNER
  JOIN posts
    ON posts.postid = post_tags.post_id
 WHERE tags.name IN ( 'tall','blonde','smart','hot','rich' )
GROUP
    BY posts.postid
ORDER
    BY tagged_count DESC

[omnibreak]

Thanks this worked fantastically.

I've been trying for about an hour now to try and get all the tags that belong to a certain post within that same query. Been looking at recursive queries and sub queries but cant get my head around it.

Is it even possible????

Basically i want everything to stay the same, but any results have all there tag.name values as well???

Thanks again

[omnibreak]

is there perhaps a way using sql variables, to have a subquery that gets all the results and then creates a column "tag1,tag2,tag3" or "tag1|tag2|tag3" and then i could do the rest with php???? :S

[r937]

i can do this with sql, but it would be quite complex, as it would require additional joins

here's a suggestion

the above query retrieves the ids of all the posts that you want to display, and the descending count of hits

use the list of ids in a second query, which retrieves all the data for each post

separating "search" from "display" logic makes it easier to make future changes

if i were to give you a single query that does it all, you'd only be back here soon asking to make changes to it

[omnibreak]

haha cool, thanks alot yeah, i have it working with an additional query but thought id try and get it in one (the query is on a page being called from an ajax search so wanted to try and make it as efficient as possible).

Think your right being left as two, there is a fair bit more i need to display as well (just kept simple on here) so is prob for the best

Thanks again man, appreciated