Shift8 Creative Graphic Design and Website Development

This will probably be the last part of the series, unless I find some other type of query that may not be so obvious or that may be new in a future release of MongoDB. This will also probably contain the most obscure and complex examples.  

Regex With $or
So, on with the good part first. You probably want to create some sort of search for content on your site, be it on the front end for visitors or the back for only admins...It's a common thing for a site and while it's always a good idea to use something like Lucene/Solr/Sphinx if you expect complex searches and lots of them...You can make a quick and dirty search with most databases such as MySQL via a FULL TEXT search. Well, MongoDB does one better and takes advantage of regex, which I posted about in the last article for this series...But this time we're going to use a new operator, the $or operator in conjunction with regex. This is new in MongoDB 1.5.3, so ensure that you at least that version.

What's the $or do? Well, the $or is important because if you make a query with regex or not and want to search multiple fields, you will have all results from both matches be returned. The $or keeps you from getting duplicate results. If you remember in part one we used $elemMatch when dealing with matching multiple conditions within objects. In this example, we aren't working with complex objects, we're just saying go find a regex pattern in the title or the body of some document. Then again, maybe you will need to also apply what you learned about $elemMatch in the previous article with something like this. Right, so get on with it.

// I'm making one call to MongoRegex() but you may need more.
$search_regex = new \MongoRegex('/'.$this->request->data['query'].'/i');

$conditions => array(
     'public' => true,
     '$or' => array(
               array('title' => $search_regex),
               array('directions' => $search_regex)
          )
     )
);

// Now you can put the query together (or define $conditions inline with the find() call)
$recipes = Recipe::find('all', array(
     'fields' => array('title', 'url'),
     'conditions' => $conditions
));

My head exploded at first, but it's quite easy to follow along with. A little tip? Reference the MongoDB documentation, make your queries in a console window and when you get back the results you want...Take the text you entered and print it out on your page where you're making the query. I just print out the guts between the parenthesis. Then with your $conditions array that you build, run json_encode() and also print that out on the screen. It's a good way to see how the array format is getting translated. It makes it a lot easier if you're not a human PHP->JSON translator. For complex objects it helps quite a bit.

So what does that do above? A little context. I have some recipes and I only want those marked as being true for the "public" field to be included in the results first and foremost. You may have similar needs with some sort of publish status. Then we're running a regex on the query string passed via POST, but we're running it on two fields. In my case I have the title for the recipe and then some directions. I may also want to search ingredients too. Of course ingredients is in an object for me, so hey where's that good ol' $elemMatch? Yay, more complexity.

Ok. So sticking with this...What is returned is exactly what you'd expect, any recipe that's publicly visible that has the query in the title or directions (case insensitive match).

Lithium's "like" array key will not work in this case for two fields. It will for one. So in other words:

// Will NOT work
$conditions = array(
     'title' => array('like' => '/chicken/i'),
     'directions' => array('like' => '/cook/i')
);

// WILL work, but won't give us what we're after
$conditions = array(
     'title' => array('like' => '/chicken/i')
);

Again, keep in mind we also need to use the $or operator for a very important reason. We don't want duplicate records in our search results. How about the other operators and other conditions? Sure! You can apply the $nin and $ne and all the other operators too. Those are a little different though. So where we put in $or as if it was a field, we have to use $nin and $ne like the other article explained. Let me write it out again all glued together with the first example here.

$conditions => array(
     'public' => true,
     '$or' => array(
               array('title' => $search_regex),
               array('directions' => $search_regex)
          )
     ),
     'title' => array(
          '$nin' => array('BBQ Chicken', 'More Chicken')
     )
);

You can probably start to imagine how you would build an "advanced" search where you allowed the user to fill out a few different form fields that narrowed the search down. Ok, so let's glue together a query that combines a little of everything that we've gone over in all three articles. This will be as if a visitor came to the site, searched for recipes that were shared with a specific family (let's say their family), had "chicken" in the title/directions, and was not shared with the public. So only a private chicken recipe currently approved and shared with a specific family (group).

$conditions = array(
     'public' => false,
     '$or' => array(
          array('title' => $search_regex),
          array('directions' => $search_regex),
          array('ingredients.ingredient' => $search_regex)
     ),
     'title' => array(
          '$nin' => array('More Chicken')
     ),
     'families' => array(
          '$elemMatch' => array(
               'family_id' => '4c59d5ec7f8b9a6279000000',
               'approved' => true
          )
     )
);

Confused? You will be, in this episode of MongoDB & Lithium...Right, so your queries can get rather complex. Hopefully you won't be confused after reading my article, but to be fair, that's complex even if you were to type it straight up in the console written as JSON. It's no different really, but don't think that you need to make a custom query with Lithium, your query can be represented as a PHP array which really helps you out with dynamic values.

The above query also looks at ingredients, which in my case are within an array. In a production situation I probably would build a more complex regular expression or possibly several. The query could end up even more complex based on what's posted along to my search method. This should really give you some ideas though. How does this impact the database? I'm not really sure. I haven't benchmarked this at all or anything.

The $type Operator
One last query for the road? Sure. This is a simple one and probably very obscure. MongoDB lets you query by data type. Wha?? Crazy right? I personally haven't found a real great use for this yet, but you never know. Here's an example:

$conditions = array(
     'title' => array(
          '$type' => 2
     )
);

That will return every recipe where the title is a string. The $type operator accepts a list of numbers that are assigned to a BSON type. You can see the list of types on the MongoDB documentation page.

That's all for now, hope you enjoyed!