Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Doctrine search a string in multiple columns

I hope you can help :)

This is how the table looks:

+------------+----------------+------------------+---------+
| firstName  | lastName       | email            | etc...  |
+------------+----------------+------------------+---------+
| John       | Doe            | [email protected]     | etc...  |
+------------+----------------+------------------+---------+
| John       | Michaels       | [email protected] | etc...  |
+------------+----------------+------------------+---------+

This is how the code looks:

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

With that QUERY:

  • If I input: John in the search box, it gives me the 2 results. OK
  • If I input: John D in the search box, it doesn't give me any result. FAIL

All right, I understand, When I type "John D", it try to find first in firstName (doesn't match) and also it doesn't match lastName or email.

How can I combine them?

The idea its to find the complete string in all possibilities.

Thanks!

like image 908
Dani Polo Avatar asked Dec 10 '15 01:12

Dani Polo


2 Answers

I will provide you a different alternative using MySQL's Full-Text Search Functions. Lets begin to prepare the table:

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

Now, firstname and lastname are columns to be used by full-text in order to search for matches:

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

The result will be:

+------------+----------------+------------------+---------+
| firstName  | lastName       | email            | etc...  |
+------------+----------------+------------------+---------+
| John       | Doe            | [email protected]     | etc...  |
+------------+----------------+------------------+---------+
| John       | Michaels       | [email protected] | etc...  |
+------------+----------------+------------------+---------+

Why both? Because John (as a word) was found, however John Doe is in the first row because has much similitude with the term of search.

Say, that lets apply this tool with Doctrine. I will assume that your model looks like this:

class Person{

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

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

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

Lets create the search function:

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();
}

Finnally, and example:

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

Additional notes:

  1. Before mysql 5.7, Full-Text only can be added just to MyISAM tables.
  2. Only can be indexed CHAR, VARCHAR, or TEXT columns.
  3. When using IN NATURAL LANGUAGE MODE in a search, mysql returns an empty resultse when the results represent < 50% of the records.
like image 196
manix Avatar answered Oct 11 '22 12:10

manix


Maybe you could use the explode function like this:

$busqueda = $_GET['search-customers'];
$names = explode(' ',$busqueda);
if(count($names)>1){
  $query->andWhere("(c.firstName LIKE '%{$names[0]}%' AND c.lastName LIKE '%{$names[1]}%')"); 
}else{
  $query->andWhere("(c.firstName LIKE '%$busqueda%' OR c.lastName LIKE '%$busqueda%' OR c.email LIKE '%$busqueda%')"); 
}

but, using like %word% is inefficient, because it can't use index.

like image 26
ZuoTao.Chou Avatar answered Oct 11 '22 13:10

ZuoTao.Chou