CODEWITHSUNDEEP
phpmongodb
hao
hao

Reputation: 655

Mongodb find query with mutiple conditions in PHP

I have three working queries:

  1. To find the rows with keyword in title field

    $cursor = $collection->find(['title' => array('$regex'=>new MongoRegex($title_query))])->sort(array('timestamp'=>-1));

  2. To find the rows with keyword in the author field

    $cursor = $collection->find(['author' => array('$regex'=>new MongoRegex($author_query))])->sort(array('timestamp'=>-1));

  3. To find the rows within a date range 

    $rangeQuery = array('timestamp' => array( '$gte' => $from_Id, '$lte' => $to_Id ));

$cursor = $collection->find($rangeQuery)->sort(array('timestamp'=>-1));

I want to combine the queries into 1&2, 1&3 and 2&3. However I am not able to write the correct query...

Here is my query for combining 1&2:

    $cursor = $collection->find('title' => array('$regex'=>new MongoRegex($title_query)),
           'author' => array('$regex'=>new MongoRegex($author_query)))->sort(array('timestamp'=>-1));

query for combining 1&3: 

     $rangeQuery = array('timestamp' => array( '$gte' => $from_Id, '$lte' => $to_Id ));
     $cursor = $collection->find($rangeQuery, ['title' => array('$regex'=>new MongoRegex($title_query))])->sort(array('timestamp'=>-1));

Can anyone tell me how to write the correct query?

Upvotes: 1

Views: 1045

Answers (1)

jmikola
jmikola

Reputation: 6922

The $regex query operator should not be necessary if you are using the BSON regex type (i.e. MongoRegex in the PHP driver). Let's rewrite the original three queries:

  1. Matching keyword in title, sorted by time descending:

    $collection->find([
  'title' => new MongoRegex($title_query),
])->sort(['timestamp' => -1]);

  2. Matching keyword in author, sorted by time descending:

    $collection->find([
  'author' => new MongoRegex($author_query),
])->sort(['timestamp' => -1]);

  3. Matching within a date range, sorted by time descending:

    $collection->find([
  'timestamp' => [
    '$gte' => $from_Id,
    '$lte' => $to_Id,
  ],
])->sort(['timestamp' => -1]);

There are several errors in the combined query examples you shared in the OP. For the "1&2" query, you were not passing an array as the first argument to find(), so that would have been a syntax error. For the "1&3" query, you're only passing the timestamp range as find() criteria, and the title regex is being incorrected passed as the second find() argument, which is reserved for specifying a project (i.e. which fields to return).

Combining the three queries is actually as easy as merging the criteria arrays. For example, we could combine all three like so:

$collection->find([
  'title' => new MongoRegex($title_query),
  'author' => new MongoRegex($author_query),
  'timestamp' => [
    '$gte' => $from_Id,
    '$lte' => $to_Id,
  ],
])->sort(['timestamp' => -1]);

In some cases, it's not possible to merge criteria for the same field. For that reason, MongoDB has an $and query operator (see the examples for some use cases); however, in the examples above, the criteria is simple enough that you can simply combine the arrays.

Upvotes: 2

Related Questions