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.
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
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???
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
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