CODEWITHSUNDEEP
mysql
alayli
alayli

Reputation: 345

Multiple tag search

I'm trying to make multiple tag search in my post tags. So I have a table tags_posts (columns are id_tag and id_post).

If user types a few tag to input (I'll seperate and parse them using comma and array), sql query should return all posts that has all tags in user's input.

What I've tried:

SELECT DISTINCT id_post, content, author_id, created, updated, username 
FROM tags_posts 
INNER JOIN posts ON posts.id=tags_posts.id_post 
INNER JOIN users ON users.id=posts.author_id 
WHERE id_tag IN (1,2,3)

But in this case if any one of tag ids has in any post, that posts returns. But I'm looking for all tags for that post.

Upvotes: 0

Views: 1037

Answers (4)

jimmy
jimmy

Reputation: 1

You could use the "IN" statement in MySQL for your parsed tags?

SELECT *
FROM Posts
LEFT JOIN Tag_Posts 
    ON Tag_Posts.Id_post = Posts.id
INNER JOIN Tags
    ON Tags.id = Tag_Posts.Id_tag
WHERE Tags.name IN('PHP','SQL','LAMP')

Upvotes: -1

Phil Cross
Phil Cross

Reputation: 9302

SELECT posts.id, posts.content, posts.created, posts.updated, posts.author_id, users.username 
FROM posts 
INNER JOIN users
    on users.id = posts.author_id 
INNER JOIN tags_posts 
    ON tags_posts.id_post = posts.id
INNER JOIN tags 
    ON tags.id = tags_posts.id_tag 
WHERE tags.name = 'tag1' 
    AND tags.name = 'tag2' 
    AND tags.name = 'tag3'

Upvotes: 0

peterm
peterm

Reputation: 92785

Try

SELECT * 
  FROM posts a JOIN
(
 SELECT p.id
   FROM tag_posts tp JOIN posts p 
     ON tp.post_id = p.id JOIN tags t 
     ON tp.tag_id = t.id
  WHERE t.name IN ('tag1', 'tag2', 'tag3')
  GROUP BY p.id
 HAVING COUNT(DISTINCT t.id) = 3 -- << should the number of tags used in WHERE clause
) q ON a.id = q.id

The HAVING clause ensures that the query returns only posts that have all (three in example) tags.

Here is SQLFiddle demo

Upvotes: 0

cartina
cartina

Reputation: 1419

Pass tag ids to be searched separated by comma in a string say $search and use the following query:

select id_post from tags_posts where id_tag  IN ($search)

Upvotes: -1

Related Questions