MySQL Search Query - Searching from tags, too :(

Question

So, I've got a webapp that lets users submit code. The submissions are stored in the code table, and a couple of columns of that are Fulltext-Indexed. This is how I have been performing searches up until now.

But, users can submit their submissions with as many tags as they like - and I'd like these to be included in the search too (but, all in one query...). The tags are stored in the table tags, and there's an intersection table called code_tags that stores the code_id and the tag_id. Standard stuff.

My 'old' search query was this:

SELECT *
  FROM code
 WHERE MATCH (title, summary, code) AGAINST ('$searchterm')

$searchterm was fetched via PHP $_POST.

So I tried to write a bit more of an 'advanced' query:

SELECT code.*, 
       code_tags.*, 
       tags.*, 
       tags.tag
  FROM code, code_tags, tags
 WHERE code_tags.code_id = code.id
   AND tags.id = code_tags.tag_id
   AND MATCH (title, summary, code) AGAINST ('$searchterm') 

But all this did was return... nothing. Even when a perfectly valid search term was entered.

So I commented out the last line:

SELECT code.*, code_tags.*, tags.*, tags.tag
  FROM code, code_tags, tags
 WHERE code_tags.code_id = code.id
   AND tags.id = code_tags.tag_id
-- AND MATCH (title, summary, code) AGAINST ('php') 

This returns every submission in the database. But, the same row is repeated as many times as there are tags for it (the only difference being, the tag in each returned row).

E.G:

So, finally, I thought I'd be clever and GROUP_CONCAT the tags:

SELECT code.*, code_tags.*, tags.*, GROUP_CONCAT(tags.tag SEPARATOR ' ') AS taggroup
  FROM code, code_tags, tags
 WHERE code_tags.code_id = code.id
   AND tags.id = code_tags.tag_id
-- AND MATCH (title, summary, code, taggroup) AGAINST ('php')`

There are two pretty big problems with this.

1. With the last AND MATCH line commented out, only one row is returned (with all the details of the first entry in the code table - and taggroup lists every tag, for every submission!
2. With the last AND MATCH line included, I get the following error: Unknown column 'taggroup' in 'where clause' - damn!

So, what am I meant to do? :S

Answer 1

Not sure how you can select tags.* and GROUP_CONCAT at the same time but been a while since I was working with MySQL now, anyway join your data in and group by the columns you want should work. Example below.

SELECT code.id, code.title, GROUP_CONCAT(tags.tag SEPARATOR ' ')
  FROM code
 INNER JOIN code_tags ON code.id = code_tags.code_id
 INNER JOIN tags ON code_tags.tag_id = tags.id
 WHERE MATCH (code.title, code.summary, code.code) AGAINST ('php')
 GROUP BY code.id, code.title

Answer 2

The reason the following:

SELECT code.*, code_tags.*, tags.*, tags.tag
  FROM code, code_tags, tags
 WHERE code_tags.code_id = code.id
   AND tags.id = code_tags.tag_id
   AND MATCH (title, summary, code) AGAINST ('php') 

...doesn't return any results is that you don't have any code table records whose title/summary/code match "php" AND have relations to either the CODE_TAGS or TAGS tables. Switching to ANSI-92 JOIN syntax, try:

SELECT c.*, ct.*
  FROM CODE c
  JOIN CODE_TAGS ct ON ct.code_id = c.id
 WHERE MATCH (title, summary, code) AGAINST ('php')

If nothing is returned, then you're problem is that none of the records that satisfy the Full Text Search are related to anything in the CODE_TAGS table -- you'll need to add associations before it will work. That should shine some light on if adding the JOIN to the TAGS table will affect anything:

SELECT c.*, ct.*
  FROM CODE c
  JOIN CODE_TAGS ct ON ct.code_id = c.id
  JOIN TAGS t ON t.id = ct.tag_id
 WHERE MATCH (title, summary, code) AGAINST ('php')