Laravel - 多表全文搜索
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21890849/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Laravel - Full Text Search on Multiple Tables
提问by dev7
I am using Laravel 4 and the Eloquent model for a project we are working on. The database conforms to 3NF and everything works great. Also all MySQL tables were switched back from InnoDB to MyISAM since the MySQL version < 5.6 (full text search in InnoDB is only supported from 5.6 and up).
我正在为我们正在进行的项目使用 Laravel 4 和 Eloquent 模型。数据库符合 3NF，一切正常。此外，所有 MySQL 表都从 InnoDB 切换回 MyISAM，因为 MySQL 版本 < 5.6（仅从 5.6 及更高版本支持 InnoDB 中的全文搜索）。
While creating some database search filters I am finding some shortage with using the Eloquent model vs the Query Builder. In specifics, especially when trying to do a full text search on columns from multiple tables (and staying within the Eloquent's object context).
在创建一些数据库搜索过滤器时，我发现使用 Eloquent 模型与 Query Builder 存在一些不足。具体来说，尤其是在尝试对来自多个表的列（并保持在 Eloquent 的对象上下文中）进行全文搜索时。
For simplicity, we have the following database structure:
为简单起见，我们有以下数据库结构：
--projects
--id
--name
--status
--...
--users
--id
--...
--roles
--id
--project_id
--user_id
--...
--notes
--id
--project_id
--user_id
--note
--....
The following code (simplified and minimized for the question) currently works fine, butthe full text search only works for one table (projectstable).
以下代码（针对问题进行了简化和最小化）目前工作正常，但全文搜索仅适用于一个表（projects表）。
if (Request::isMethod('post'))
{
$filters = array('type_id','status','division','date_of_activation','date_of_closure');
foreach ($filters as $filter) {
$value = Input::get($filter);
if (!empty($value) && $value != -1) {//-1 is the value of 'ALL' option
$projects->where($filter,'=',$value);
}
}
$search = Input::get('search');
if (!empty($search)) {
$projects->whereRAW("MATCH(name,description) AGAINST(? IN BOOLEAN MODE)",array($search));
}
}
// more code here...
// some more filters...
// and at the end I am committing the search by using paginate(10)
return View::make('pages/projects/listView',
array(
"projects" => $projects->paginate(10)
)
);
I need to extend the full text search to include the following columns - projects.name,projects.descriptionand notes.note.
When trying to find how to make it with Eloquent we keep on coming back to Query Builder and running a custom query, which will work fine but then we will face these problems/cons:
我需要扩展全文搜索以包括以下列 - projects.name、projects.description和notes.note。当试图找到如何使用 Eloquent 实现它时，我们不断回到 Query Builder 并运行自定义查询，这将正常工作，但随后我们将面临这些问题/缺点：
- Query Builder returns an array while Eloquent returns model objects. Since we are extending each model to include methods, we really don't want to give up the awesomeness of the Eloquent model. And we really don't want to use the Eloquent
Project::find($id)on the return results just to get the object again. - We are chaining the 'where' methods to have any number of filters assigned to it as well as for code re-usability. Seems like mixing Eloquent and Query Builder statement together will break our chaining.
- For the consistency of this project, we want all database queries to stay in Eloquent connotation.
- Query Builder 返回一个数组，而 Eloquent 返回模型对象。由于我们正在扩展每个模型以包含方法，因此我们真的不想放弃 Eloquent 模型的强大功能。我们真的不想
Project::find($id)在返回结果上使用 Eloquent只是为了再次获取对象。 - 我们正在链接“where”方法，以便为其分配任意数量的过滤器以及代码可重用性。似乎将 Eloquent 和 Query Builder 语句混合在一起会破坏我们的链接。
- 为了这个项目的一致性，我们希望所有的数据库查询都停留在 Eloquent 的内涵。
Reading Laravel's documentation and API, I could not find a method to run raw SQL queries using Eloquent. There is whereRAW()but it is not broad enough. I assume that this is a restriction made by design, but it is still a restriction.
阅读 Laravel 的文档和 API，我找不到使用 Eloquent 运行原始 SQL 查询的方法。有，whereRAW()但不够广泛。我认为这是设计的限制，但它仍然是一个限制。
So my questions are:
所以我的问题是：
- Is it possible to run a full text search on columns from multiple tables, only in Eloquent. Every piece of information I came across online, mentions using Query Builder.
- If not, is it possible to use Query Builder searches and returning Eloquent objects? (without the need to run
Project::find($id)on the array results). - And lastly, is it possible to chain Eloquent and Query Builder
wheremethods together, while only committing usingget()orpaginate(10)at a later point.
- 是否可以仅在 Eloquent 中对来自多个表的列运行全文搜索。我在网上看到的每条信息都提到了使用 Query Builder。
- 如果没有，是否可以使用查询生成器搜索并返回 Eloquent 对象？（无需
Project::find($id)对数组结果运行）。 - 最后，是否可以将 Eloquent 和 Query Builder
where方法链接在一起，而只在以后提交 usingget()或paginate(10)。
I understand that Eloquent and Query Builder are different creatures. But if mixing both was possible, or using Eloquent to run raw SQL queries, I believe that the Eloquent model will become much more robust. Using only Query Builder seems to me a bit like under-using the Laravel framework.
我知道 Eloquent 和 Query Builder 是不同的生物。但是，如果将两者混合使用，或者使用 Eloquent 来运行原始 SQL 查询，我相信 Eloquent 模型将变得更加健壮。仅使用 Query Builder 在我看来有点像未充分使用 Laravel 框架。
Hope to get some insights about this since it seems as the forums/community of Laravel is still evolving, even though I find it to be an amazing framework!
希望能对此有所了解，因为 Laravel 的论坛/社区似乎仍在不断发展，尽管我发现它是一个了不起的框架！
Thanks and I appreciate any input you may have :)
谢谢，我感谢您提供的任何意见:)
回答by Felice Ostuni
First of all you can use query scope in your model/s
首先，您可以在模型中使用查询范围
public function scopeSearch($query, $q)
{
$match = "MATCH(`name`, `description`) AGAINST (?)";
return $query->whereRaw($match, array($q))
->orderByRaw($match.' DESC', array($q));
}
this way you can get "eloquent collection" as return
这样你就可以得到“雄辩的收藏”作为回报
$projects = Project::search(Input::get('search'))->get();
then, to search also into notes you can make a more complex scope that join notes and search there.
然后，要搜索笔记，您可以创建一个更复杂的范围，加入笔记并在那里搜索。
回答by Traxo
Not sure if this will help but there is a workaround in innoDB(in versions that support fulltext search), maybe it works for you.
Lets use 'notes' as second table
不确定这是否会有所帮助，但 innoDB 中有一个解决方法（在支持全文搜索的版本中），也许它对您有用。
让我们使用“笔记”作为第二个表格
SELECT MATCH(name,description) AGAINST(? IN BOOLEAN MODE),
MATCH(notes.note) AGAINST(? IN BOOLEAN MODE)
FROM ...
.
.
WHERE
MATCH(name,description) AGAINST(?) OR
MATCH(notes.note) AGAINST(?)
