php mysql全文搜索多个表通过id加入 [英] php mysql full text search multiple table joined by id

问题描述

我一直在阅读全文搜索一段时间，我已经阅读了一些文章说可能，但他们不提供样例查询，

我'找到一个解决方法，如何搜索使用全文搜索的ID加入的多个表

这是我的最小化的表看起来像

这里是我迄今为止

  $ users = User :: select（'users。*'，' .department_name'）
  - > join（'departments'，'departments.id'，'='，'users.dept_id'）
  - > whereRaw（
MATCH ）AGAINST（？IN BOOLEAN MODE），
 array（$ q）
）
  - > paginate（10）; 
} 
  

如何在搜索时包含 department_name ？请帮助，谢谢提前

解决方案

我觉得你太接近你的结果了。使用查询构建器（但未测试）此代码应该可以工作：

  $ user = DB :: table（'users'） 
  - > selectRaw（'users。*，departments.department_name'）
  - > join（'departments'，'departments.id'，'='，'users.dept_id'）
  - > whereRaw（
MATCH（users.user_name）AGAINST（？IN BOOLEAN MODE）OR 
 MATCH（departments.department_name）AGAINST（？IN BOOLEAN MODE），
数组（$ q，$ q））
） - > paginate（10）; 
  

还可以使用 FULLTEXT 在MySQL表中，而 http：// dev。 mysql.com/doc/refman/5.6/en/fulltext-restrictions.html 。在 MATCH（）子句中使用的列或列列表中有 FULLTEXT index 是个好习惯。 p>

I have been reading on full text search for some time now, I have read some articles that say its possible but they don't provide sample query,

I'm stuck finding a workaround on how to search on multiple table joined by an ID using full text search

this is my minified table look like

here is what i have so far

    $users = User::select('users.*','departments.department_name')
    ->join('departments', 'departments.id', ' =', 'users.dept_id')
    ->whereRaw(
        "MATCH(user_name) AGAINST(? IN BOOLEAN MODE)", 
        array($q)
        )
    ->paginate(10);
}

how can I include department_name when searching? please help, thanks in advance

解决方案

I think you are too close to your result. Using query-builder (but not tested) this code should work:

$user = DB::table('users')
->selectRaw('users.*, departments.department_name')
->join('departments', 'departments.id', ' =', 'users.dept_id')
->whereRaw(
    "MATCH(users.user_name) AGAINST(? IN BOOLEAN MODE) OR
     MATCH(departments.department_name) AGAINST(? IN BOOLEAN MODE)",
    array($q, $q))
)->paginate(10);

There are also some restrictions on where you could use FULLTEXT in MySQL tables and where not http://dev.mysql.com/doc/refman/5.6/en/fulltext-restrictions.html. It is good practise to have FULLTEXT index on column or column list which you use in MATCH() clause.

这篇关于php mysql全文搜索多个表通过id加入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！