首页 \ 问答 \ 在SQL中进行博客搜索,在搜索中添加标签(Blog search in SQL adding the tags in the search)

在SQL中进行博客搜索,在搜索中添加标签(Blog search in SQL adding the tags in the search)

我的博客/标签有3个表格

lm_blog,lm_blog_tags和lm_blog_tag_relationships。

我现在正在尝试添加使用布尔搜索的搜索功能。 我没那么好,但我也无法搜索标签。

SELECT blog.blog_id, title, url, summary, 
GROUP_CONCAT(tags.tag SEPARATOR ':') AS tags, 
MATCH(title) AGAINST ($query IN BOOLEAN MODE) AS title_rel, 
MATCH(summary) AGAINST ($query IN BOOLEAN MODE) AS summary_rel, 
MATCH(article) AGAINST ($query IN BOOLEAN MODE) AS article_rel 
FROM lm_blog AS blog
INNER JOIN lm_blog_tag_relationships AS rel 
ON blog.blog_id = rel.blog_id 
INNER JOIN lm_blog_tags AS tags 
ON rel.tag_id = tags.tag_id
WHERE MATCH(title, summary, article) AGAINST ($query IN BOOLEAN MODE)
GROUP BY blog.blog_id 
ORDER BY title_rel DESC, summary_rel DESC, article_rel DESC, blog.updated DESC 

我已经尝试添加另一个WHERE子句来匹配聚合标记但你不能在where子句中使用组数据。 据我所知,没有办法做任何地方WHERE OR HAVING有...

Heres是我得到的最接近但它检查WHERE和HAVING。 我需要一个WHERE或HAVING

SELECT blog.blog_id, title, url, summary, 
GROUP_CONCAT(tags.tag SEPARATOR ':') AS tags, 
MATCH(title) AGAINST ($query IN BOOLEAN MODE) AS title_rel, 
MATCH(summary) AGAINST ($query IN BOOLEAN MODE) AS summary_rel, 
MATCH(article) AGAINST ($query IN BOOLEAN MODE) AS article_rel 
FROM lm_blog AS blog
INNER JOIN lm_blog_tag_relationships AS rel 
ON blog.blog_id = rel.blog_id 
INNER JOIN lm_blog_tags AS tags 
ON rel.tag_id = tags.tag_id
WHERE MATCH(title, summary, article) AGAINST($query IN BOOLEAN MODE)
GROUP BY blog.blog_id 
HAVING tags LIKE $query 
ORDER BY title_rel DESC, summary_rel DESC, article_rel DESC, blog.updated DESC 

I have 3 tables for my blogs/tags

lm_blog, lm_blog_tags, and lm_blog_tag_relationships.

I am now trying to add a search feature that uses Boolean searching. I got that ok but I cant get it to search the tags as well.

SELECT blog.blog_id, title, url, summary, 
GROUP_CONCAT(tags.tag SEPARATOR ':') AS tags, 
MATCH(title) AGAINST ($query IN BOOLEAN MODE) AS title_rel, 
MATCH(summary) AGAINST ($query IN BOOLEAN MODE) AS summary_rel, 
MATCH(article) AGAINST ($query IN BOOLEAN MODE) AS article_rel 
FROM lm_blog AS blog
INNER JOIN lm_blog_tag_relationships AS rel 
ON blog.blog_id = rel.blog_id 
INNER JOIN lm_blog_tags AS tags 
ON rel.tag_id = tags.tag_id
WHERE MATCH(title, summary, article) AGAINST ($query IN BOOLEAN MODE)
GROUP BY blog.blog_id 
ORDER BY title_rel DESC, summary_rel DESC, article_rel DESC, blog.updated DESC 

I have tried adding another WHERE clause to match against the aggregate tags but you cant use group data in a where clause. As far as I am aware there isn't a way to do a WHERE OR HAVING either...

Heres the closest I have gotten but it checks WHERE and HAVING. I need a WHERE or HAVING

SELECT blog.blog_id, title, url, summary, 
GROUP_CONCAT(tags.tag SEPARATOR ':') AS tags, 
MATCH(title) AGAINST ($query IN BOOLEAN MODE) AS title_rel, 
MATCH(summary) AGAINST ($query IN BOOLEAN MODE) AS summary_rel, 
MATCH(article) AGAINST ($query IN BOOLEAN MODE) AS article_rel 
FROM lm_blog AS blog
INNER JOIN lm_blog_tag_relationships AS rel 
ON blog.blog_id = rel.blog_id 
INNER JOIN lm_blog_tags AS tags 
ON rel.tag_id = tags.tag_id
WHERE MATCH(title, summary, article) AGAINST($query IN BOOLEAN MODE)
GROUP BY blog.blog_id 
HAVING tags LIKE $query 
ORDER BY title_rel DESC, summary_rel DESC, article_rel DESC, blog.updated DESC 

原文:https://stackoverflow.com/questions/19893628
更新时间:2021-06-07 19:06

最满意答案

经过几个小时的搜索后找到答案......实际上很容易。 只需选择带有标签的一个,然后选择一个带有全文搜索并使用UNION DISTINCT加入它们。

SELECT blog.blog_id, title, url, summary, updated, 
GROUP_CONCAT(tags.tag SEPARATOR ':') AS tags, 
MATCH(title) AGAINST ($query IN BOOLEAN MODE) AS title_rel, 
MATCH(summary) AGAINST ($query IN BOOLEAN MODE) AS summary_rel, 
MATCH(article) AGAINST ($query IN BOOLEAN MODE) AS article_rel 
FROM lm_blog AS blog
INNER JOIN lm_blog_tag_relationships AS rel 
ON blog.blog_id = rel.blog_id 
INNER JOIN lm_blog_tags AS tags 
ON rel.tag_id = tags.tag_id
WHERE MATCH(title, summary, article) AGAINST ($query IN BOOLEAN MODE) 
GROUP BY blog.blog_id 

UNION DISTINCT

SELECT blog.blog_id, title, url, summary, updated, 
GROUP_CONCAT(tags.tag SEPARATOR ':') AS tags,  
MATCH(title) AGAINST ($query IN BOOLEAN MODE) AS title_rel, 
MATCH(summary) AGAINST ($query IN BOOLEAN MODE) AS summary_rel, 
MATCH(article) AGAINST ($query IN BOOLEAN MODE) AS article_rel 
FROM lm_blog AS blog
INNER JOIN lm_blog_tag_relationships AS rel 
ON blog.blog_id = rel.blog_id 
INNER JOIN lm_blog_tags AS tags 
ON rel.tag_id = tags.tag_id
GROUP BY blog.blog_id 
HAVING tags REGEXP $query

ORDER BY title_rel DESC, summary_rel DESC, article_rel DESC, updated DESC

它看起来不像我可以使用HAVING ... MATCH ... AGAINST ...所以我将只使用正则表达式,直到找到解决方案。


Found the answer after a few hours of searching...It's actually easy. Just select one with tags and select one with full text searching and join them with a UNION DISTINCT.

SELECT blog.blog_id, title, url, summary, updated, 
GROUP_CONCAT(tags.tag SEPARATOR ':') AS tags, 
MATCH(title) AGAINST ($query IN BOOLEAN MODE) AS title_rel, 
MATCH(summary) AGAINST ($query IN BOOLEAN MODE) AS summary_rel, 
MATCH(article) AGAINST ($query IN BOOLEAN MODE) AS article_rel 
FROM lm_blog AS blog
INNER JOIN lm_blog_tag_relationships AS rel 
ON blog.blog_id = rel.blog_id 
INNER JOIN lm_blog_tags AS tags 
ON rel.tag_id = tags.tag_id
WHERE MATCH(title, summary, article) AGAINST ($query IN BOOLEAN MODE) 
GROUP BY blog.blog_id 

UNION DISTINCT

SELECT blog.blog_id, title, url, summary, updated, 
GROUP_CONCAT(tags.tag SEPARATOR ':') AS tags,  
MATCH(title) AGAINST ($query IN BOOLEAN MODE) AS title_rel, 
MATCH(summary) AGAINST ($query IN BOOLEAN MODE) AS summary_rel, 
MATCH(article) AGAINST ($query IN BOOLEAN MODE) AS article_rel 
FROM lm_blog AS blog
INNER JOIN lm_blog_tag_relationships AS rel 
ON blog.blog_id = rel.blog_id 
INNER JOIN lm_blog_tags AS tags 
ON rel.tag_id = tags.tag_id
GROUP BY blog.blog_id 
HAVING tags REGEXP $query

ORDER BY title_rel DESC, summary_rel DESC, article_rel DESC, updated DESC

It doesn't look like I can use HAVING ... MATCH ... AGAINST ... so I will just use a regexp for this until i find a solution.

相关问答

更多

多标签搜索查询(Multiple tags search query)

以下应该工作。 SELECT media_id FROM tagXmedia WHERE tag_id IN (SELECT id FROM tag WHERE name IN ('home','hawaii')) GROUP BY media_id HAVING COUNT(tag_id) = 2; 如果你想让它匹配不止两个标签,你可以很容易地添加它们。 请记住在HAVING子句中更改2 。 我认为tagXmedia中的所有行都是唯一的。 如果不是,则必须将DISTINCT添加到COUNT部分 ...

如何优化搜索SQL查询?(How to optimize the search SQL query?)

我们可以得到名称与过去标签类似的所有boosk,并根据相似性添加自定义ORDER BY。 如果name包含标签+1,如果不是0.如果name包含所有3个标签,如果只有一个和,则sum为3。 SELECT DISTINCT id FROM books where name LIKE '%shakespeare%' OR name LIKE '%tragedy%' OR name LIKE '%hamlet%' ORDER BY IF(INSTR(name, 'shakespeare' ...

根据标签搜索(Search depending on tags)

你的$ row中没有名为$ data的变量: $stmt->execute(['tag' => $tag]); while($row = $stmt->fetch()){ $title = $row['title']; echo $title; } you don't have a variable named $data in you while, its $row: $stmt->execute(['tag' => $tag]); while($row = $ ...

在SQL中进行博客搜索,在搜索中添加标签(Blog search in SQL adding the tags in the search)

经过几个小时的搜索后找到答案......实际上很容易。 只需选择带有标签的一个,然后选择一个带有全文搜索并使用UNION DISTINCT加入它们。 SELECT blog.blog_id, title, url, summary, updated, GROUP_CONCAT(tags.tag SEPARATOR ':') AS tags, MATCH(title) AGAINST ($query IN BOOLEAN MODE) AS title_rel, MATCH(summary) A ...

关系中的SQL搜索(SQL search in relations)

正如您自己提到的,您的第一个查询的问题是您返回了几个重复的行。 不奇怪,因为我猜表A和表B&C之间的关系是一对多的 。 在您的下一次尝试中,您添加了DISTINCT ,这确实会删除重复项,但常规联接(或内部联接)将仅返回数据可以连接的匹配项,即两个联接表中存在数据的位置。 介绍LEFT JOIN : SELECT DISTINCT fields FROM A LEFT JOIN B ON id = B.aid LEFT JOIN C ON id = C.aid WHERE fields L ...

Linq SQL搜索 - 来自带有更多匹配项的标签(Linq SQL Search - From tags with more matches)

标签列表: List<TagObj> tags = new List<TagObj>() { new TagObj() { Id = 1, QuestionId = 1, Tag = "news" }, new TagObj() { Id = 2, QuestionId = 1, Tag = "sports" }, new TagObj() { Id = 3, QuestionId = 1, Tag = "famous" }, new TagObj() { Id = ...

SQL和许多列中的搜索(SQL and search in many column)

SQL中的OR语句必须用于比较不同的断言,例如something = 'something' OR other_thing = 'other_stuff' 。 在您的情况下,您应该使用OR关键字,如下所示: @items.where('txt_1 LIKE ? OR txt_2 LIKE ? OR keywords LIKE ?', "%#{search_name}%","%#{search_name}%", "%#{search_name}%") 但由于您使用相同的值( search_nam ...

为什么全文搜索不能与Laravel 5.1中的标签一起使用?(Why wont full text search work in conjunction with tags in Laravel 5.1?)

我想到了。 我不得不像这样使用if语句 public function index(Request $request) { if($query = $request->get('q') ) { $posts = Post::where('title', 'LIKE', "%{$query}%") ->orwhere('subtitle', 'LIKE', "%{$query}%")->get(); $videos = Vid ...

我如何在SQL中实现布尔标签搜索?(How can I implement a boolean tag search in SQL?)

很难说不知道你的架构,但这样的事情会起作用: select article_id from articles inner join tag t1 on t1.article_id=articles.article_id and t1.tag='included_tag' inner join tag t2 on t2.article_id=articles.article_id and t2.tag='another_included_tag' left outer join tag t3 on ...

搜索所有值之间的SQL(Search Through All Between Values SQL)

这将是明显的答案...... SELECT * FROM <YOUR_TABLE_NAME> a WHERE @theInput between a._BEGIN and a._END 如果数据是字符串(假设这里我们不知道什么是DB)你可以添加它。 Declare @searchArg VARCHAR(30) = CAST(@theInput as VARCHAR(30)); SELECT * FROM <YOUR_TABLE_NAME> a WHERE @searchArg betwee ...

相关文章

更多

最新问答

更多
  • 如何从远程文件拉取文件而不覆盖本地文件?(How do I pull files from remote without overwriting local files?)
  • Reactjs:状态改变时重新渲染iframe(Reactjs: re-renders iframes when state changed)
  • 奇怪的网址,以及跟随php页面流程的困难(odd url, and difficulty in following the php page flow)
  • 标签活动无效(Tab Activity is not working)
  • JavaME合适的语法编译器建议?(JavaME-suitable grammar compiler recommendations?)
  • 指定参数(Specifying arguments)
  • 可以通过Ruby插件或控制台覆盖Sketchup中的键盘快捷键吗?(Can one override keyboard shortcuts in Sketchup through the a Ruby Plugin or Console?)
  • 计算Java EE Web App中用户数的最佳方法(Best way to count number of users in a Java EE web App)
  • 无法使用templateUrl加载cordova中的外部模板(unable to load external templates in cordova with templateUrl)
  • PHPExcel:写入期间无法使用缓存(PHPExcel: Unable to use cache during write)
  • 在javascript中嵌套这个指针(nested this pointer in javascript)
  • 谁跟领航致远培训过,有问题问下啊
  • 控制器要求在入门时下载(Controller ask to download on entry)
  • 未能通过conda安装Asyncio(Failure to install Asyncio via conda)
  • 如何查找已完成项目的总长度?(How to find length of total completed items?)
  • 如何检查OleInitialize是否已被调用?(How to check if OleInitialize has already been called?)
  • SQL在特定范围内返回列中具有最大值的行(SQL Returning rows with max value in column, within a specific range)
  • preg_match从url获取id(preg_match get the id from url)
  • 如何在运算符中为make方程转换perl变量?(How to convert a perl variable in a operator for make equations?)
  • 在导航上方添加空格/标题。(Add a white space/ header above navigation.)
  • MeetingItem已保存;(MeetingItem saved; but change now shown in Calendar)
  • c#vb:我们应该使用System.Lazy进行资源密集型任务吗?(c# vb: Should we use System.Lazy for resource-intensive task? (when threading is not needed))
  • 为什么在armeabi代码中使用armeabi-v7a代码?(Why use armeabi-v7a code over armeabi code?)
  • 获取请求的自定义标头(Java HTTP)(Fetching a custom header of a request (Java HTTP))
  • 是否可以在嵌套的if语句中从varchar转换为numeric以动态评估参数?(Is it possible to convert from varchar to numeric within a nested if statement in order to dynamically evaluate a parameter?)
  • 如何将Html.ActionLink转换为链接到Ajax调用的按钮?(How to convert from Html.ActionLink to a button linked to Ajax call?)
  • 应用程序如何处理Windows符号链接?(How are Windows symbolic links treated by the apps?)
  • html,js,css在jsfiddle中工作,但不在sharepoint中(html, js, css works in jsfiddle but not in sharepoint)
  • 从Ruby脚本调用Elasticsearch Rest API(Calling Elasticsearch Rest API from Ruby script)
  • 如何将嵌套setTimeouts转换为承诺(How to convert nested setTimeouts to promises)