Building a "matching" query with ORs

Need Help
1

I have a search query that uses matching. The main table is called Transactions and it has a belongsToMany association with table Customers, aliased as Clients, through TransactionsCustomers. I’m merely trying to match a string that could be in two different fields.

Example:

$query->matching(“Clients”, function($q) use($clients) {
return $q->where([“Clients.C_CustomerName like ‘%$clients%’”]);
});

This works from an produces SQL like this:
INNER JOIN customers Clients ON (Clients.C_CustomerName like ‘%houser%’ AND Clients.C_IDCustomer = (TransactionsCustomers.customer_id))

but when I add an orWhere like so:

$query->matching(“Clients”, function($q) use($clients) {
return $q->where([“Clients.C_CustomerName like ‘%$clients%’”])
->orWhere([“Clients.C_EntityName like ‘%$clients%’”]);
});

Or just put them both on the same line:

$query->matching(“Clients”, function($q) use($clients) {
return $q->where([“Clients.C_CustomerName like ‘%$clients%’ OR Clients.C_EntityName like ‘%$clients%’”]);
});

I get SQL like this:

INNER JOIN customers Clients ON
(Clients.C_CustomerName like ‘%houser%’ OR Clients.C_EntityName like ‘%houser%’ OR Clients.C_IDCustomer = (TransactionsCustomers.customer_id))

The problem is that this is all ORs which matches everything. It going to return every transaction with a Client associated with it.

How do I code this query so it works properly? It probably should produce SQL something like:

INNER JOIN customers Clients ON
((Clients.C_CustomerName like ‘%houser%’ OR Clients.C_EntityName like ‘%houser%’ ) AND Clients.C_IDCustomer = (TransactionsCustomers.customer_id))

2

I could be wrong but I think you want to do something like this:

$query = $articles->find()
->where([‘title LIKE’ => ‘%First%’])
->andWhere(function ($exp) {
return $exp->or_([
‘author_id’ => 2,
‘is_highlighted’ => true
]);
});

3

I think typing it out made it all make sense.

Here’s what worked:

$query->matching(“Clients”, function($q) use($clients) {
return $q->where([“(Clients.C_CustomerName like ‘%$clients%’ OR Clients.C_EntityName like ‘%$clients%’)”]);
});

I had to add parenthesis into the query string so it would work properly.