How to do an sql query in Moongodb with php?

Question

How do the following query sql in Mongo?

select * from myDB WHERE nm_pesquisa LIKE "%SAO%" AND tp_m = "A" OR tp_m = "H";

I tried it this way but without success:

$query = [
    'nm_pesquisa' => ['$regex' => MDB::Regex($nm)],
    'tp_m' => "A",
    '$or' => [['tp_m' => "A"]]
];

Answer 1

First (even if your problem is not current) your query is "wrong":

select * from myDB WHERE nm_pesquisa LIKE "%SAO%" AND tp_m = "A" OR tp_m = "H";

You have a OR next to a AND, mysql might even work, but it sure seems wrong to me or would bring perhaps some unexpected result or missing some result, anyway I believe that actually would be expected:

select * from myDB WHERE nm_pesquisa LIKE "%SAO%" AND (tp_m = "A" OR tp_m = "H");

A detail the FROM does not select the bank, maybe you already know, it selects the table, of course you can select a table in a specific bank, but still it is the table, just to explain, is that myDB there maybe doesn’t make sense, anyway I assume it’s a typo.

Now let’s go to OR and AND in Mongodb, basically you’d have to use $or to resolve and implement both conditions of OR of SELECT inside it, your query will probably look like this:

Note: I kept the myDB, but it is the document

Note: I used /.*SAO.*/ which is the regex

db.myDB.find({
   "nm_pesquisa": { $regex: /.*SAO.*/ },
   $or: [
      {"tp_m": "A"}, {"tp_m": "H"}
   ]
})

With native PHP libs: http://php.net/manual/en/set.mongodb.php, I think it should stay that way:

 $regex = new MongoDB\BSON\Regex('.*SAO.*');

If it were case-insensitive it would be so:

$regex = new MongoDB\BSON\Regex('.*SAO.*', 'i');

So then just pass the $regex thus in the filter and the conditions of the OR for tp_m are within $or => [...]

$filter = [
    'nm_pesquisa' => $regex,
    '$or' => [
         [ 'tp_m' => 'A' ], [ 'tp_m' => 'H' ]
    ]
];

If I have wrong something let me know, I never worked with mongoDB, what I answered above was based on documentation, so I may have understood something wrong