Magento2 Collection Multiple Columns as OR

Question

I want to do something very simple, have a collection that returns all customers that have a certain string in their name or email.

Based on how you can use in, I thought I would be able to do something like this

->addFieldToFilter(
    array('email', 'firstname', 'lastname'),
    array("like" => "%{$query}%")
 )

However, I get an error

"Warning: Illegal string offset 'attribute' in vendor\magento\module-eav\Model\Entity\Collection\AbstractCollection.php on line 362"

The MySQL I am essentially aiming for is

SELECT * FROM mage_customer_entity

WHERE firstname LIKE '%tim%' OR email LIKE '%tim%';

Answer 1

Even though you want to use the same search string for each column, you must declare it for all 3 columns like so.

->addFieldToFilter(
    array('email', 'firstname', 'lastname'),
    array(
        array("like" => "%{$partial}%"),
        array("like" => "%{$partial}%"),
        array("like" => "%{$partial}%")
    )
);

In this example $partial is the partial string you are expecting to be present in any of the 3 columns.

To explain this little further, the first parameter is an array of the columns you would like to query against, in the order they will be queried. The second parameter is an array of arrays that define how each column will be queried. The order of the second array is in respect to the first array.

I would also like to point out that customers are an EAV entity. So the produced query will not be a simple one table query. You will have a join for each attribute being pulled into the query results. In this example you will have 4 tables being used; the base table, and 3 attribute tables (even though all 3 attributes live in the varchar table, this table will still be joined 3 times.)

Answer 2

Pattern change in Magento 2.3.x:

Update Code:

$collection = $this->customerCollectionFactory->create();
$collection->addAttributeToFilter(
    [
        ['attribute' => 'email', 'like' => "%{$tim}%"],
        ['attribute' => 'firstname', 'like' => "%{$tim}%"],
        ['attribute' => 'lastname', 'like' => "%{$tim}%"]
    ]
);