omnibreak — 2010-02-09T08:42:37-05:00 — #1
ok i have 3 tables (simplified for example):
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 — 2010-02-09T09:39:43-05:00 — #2
, COUNT(*) AS tagged_count
ON post_tags.tagid = tags.tagid
ON posts.postid = post_tags.post_id
WHERE tags.name IN ( 'tall','blonde','smart','hot','rich' )
BY tagged_count DESC
omnibreak — 2010-02-09T15:55:01-05:00 — #3
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???
omnibreak — 2010-02-09T16:27:04-05:00 — #4
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 — 2010-02-09T16:31:58-05:00 — #5
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 — 2010-02-09T16:37:17-05:00 — #6
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