Doctrine search a string in multiple columns

Question

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!

Answer 1

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.

Answer 2

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.