Doctrine在多列中搜索字符串

我希望你能帮忙:)

这是表格的样子:

+------------+----------------+------------------+---------+
| firstName  | lastName       | email            | etc...  |
+------------+----------------+------------------+---------+
| John       | Doe            | john@doe.com     | etc...  |
+------------+----------------+------------------+---------+
| John       | Michaels       | john@michaels.es | etc...  |
+------------+----------------+------------------+---------+

这是代码的样子:

if($_GET['search-customers'] != '') {
    $busqueda = $_GET['search-customers'];
    $query->andWhere("(c.firstName LIKE '%$busqueda%' OR c.lastName LIKE '%$busqueda%' OR c.email LIKE '%$busqueda%')"); 
}

有了QUERY:

如果我在搜索框中输入:John,它会给我2个结果.好

如果我在搜索框中输入:John D,它不会给我任何结果.失败

好吧,我明白,当我输入" John D "时,它会尝试在firstName中找到第一个(不匹配),并且它与lastName或email不匹配.

我该如何组合它们？

它的想法是在所有可能性中找到完整的字符串.

谢谢!

我将使用MySQL的全文搜索功能为您提供不同的替代方案.让我们开始准备桌子:

ALTER TABLE persons ADD FULLTEXT (`firstname`, `lastname`);

现在,firstname并lastname要通过全文以搜索匹配使用的列:

SELECT * FROM persons
WHERE MATCH (firstname,lastname)
AGAINST ('John D' IN NATURAL LANGUAGE MODE);

结果将是:

+------------+----------------+------------------+---------+
| firstName  | lastName       | email            | etc...  |
+------------+----------------+------------------+---------+
| John       | Doe            | john@doe.com     | etc...  |
+------------+----------------+------------------+---------+
| John       | Michaels       | john@michaels.es | etc...  |
+------------+----------------+------------------+---------+

两个为什么？因为John(作为一个单词)被发现,然而John Doe在第一行,因为与搜索术语有很多相似之处.

说,这可以让这个工具与Doctrine一起使用.我假设你的模型看起来像这样:

class Person{

    /** @column(type="string", name="firstname")*/
    protected $firstName;

    /** @column(type="string", name="lastname")*/
    protected $lastName;

    /** @column(type="string")*/
    protected $email;
}

让我们创建搜索功能:

public function search($term){
    $rsm = new ResultSetMapping();

    // Specify the object type to be returned in results
    $rsm->addEntityResult('Models\Person', 'p');

    // references each attribute with table's columns 
    $rsm->addFieldResult('p', 'firstName', 'firstName');
    $rsm->addFieldResult('p', 'lastName', 'lastname');
    $rsm->addFieldResult('p', 'email', 'email');

    // create a native query
    $sql = 'select p.firstName, p.lastname, p.email from persons p
            where match(p.firstname, p.lastname) against(?)';

    // execute the query
    $query = $em->createNativeQuery($sql, $rsm);
    $query->setParameter(1, $term);

    // getting the results
    return $query->getResult();
}

最后,例如:

$term = 'John D';
$results = search($term);
// two results
echo count($results);

补充说明:

在mysql 5.7之前,只能将Full-Text添加到MyISAM表中.

只有可以被索引CHAR,VARCHAR或者TEXT列.

IN NATURAL LANGUAGE MODE在搜索中使用时,当结果表示< 50%记录时,mysql返回空结果.