MySQL 搜索查询 - 也从标签搜索:(
因此,我有一个允许用户提交代码的网络应用程序。提交内容存储在 code
表中,其中的几列是全文索引的。到目前为止,这就是我执行搜索的方式。
但是,用户可以使用任意数量的标签来提交提交的内容 - 我希望这些也包含在搜索中(但是,全部都在一个查询中......)。标签存储在表 tags
中,并且有一个名为 code_tags
的交集表,用于存储 code_id
和 tag_id
>。标准的东西。
我的“旧”搜索查询是这样的:
SELECT *
FROM code
WHERE MATCH (title, summary, code) AGAINST ('$searchterm')
$searchterm
是通过 PHP $_POST 获取的。
所以我尝试编写更多的“高级”查询:
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')
但是这一切所做的只是返回...什么都没有。即使输入了完全有效的搜索词。
所以我注释掉了最后一行:
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')
这将返回数据库中的每个提交。但是,同一行的重复次数与它的标签数相同(唯一的区别是每个返回行中的标签)。
例如:
所以,最后,我想我会很聪明,并对标签进行 GROUP_CONCAT:
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')`
这有两个相当大的问题。
- 将最后一个
AND MATCH
行注释掉后,仅返回一行(包含code
表中第一个条目的所有详细信息 - 并且 taggroup 列出了每个标签,对于每个 ! - 包含最后一个
AND MATCH
行后,我收到以下错误:'where 子句'中的未知列'taggroup'
- 该死!
提交 :S
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.
- With the last
AND MATCH
line commented out, only one row is returned (with all the details of the first entry in thecode
table - and taggroup lists every tag, for every submission! - 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
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
原因如下:
...不返回任何结果是因为您没有任何
code
表记录,其标题/摘要/代码匹配“php”并且与>CODE_TAGS
或TAGS
表。切换到 ANSI-92 JOIN 语法,尝试:如果没有返回任何内容,那么您的问题是满足全文搜索的记录都与
CODE_TAGS
表中的任何内容都不相关 - 您需要先添加关联才能工作。如果将 JOIN 添加到 TAGS 表会影响任何内容,那么应该会有所启发:The reason the following:
...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 theCODE_TAGS
orTAGS
tables. Switching to ANSI-92 JOIN syntax, try: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:不知道如何同时选择
tags.*
和GROUP_CONCAT
但自从我现在使用 MySQL 以来已经有一段时间了,无论如何,将您的数据加入并按你想要的列应该可以工作。下面的例子。Not sure how you can select
tags.*
andGROUP_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.