Shift8 Creative Graphic Design and Website Development

Ok, so this will probably be a whole big long series because there are a real good handful of different types of queries you can make with MongoDB. Combine that with the complex objects that you can store in the database and you really can get quite complex. Of course I also urge you to think about database design even if MongoDB is so easy that you dive in and just simply forget to think and draw out a schema map. I mean you have regex and all sorts of crazy stuff available. Check out this MongoDB documentation page for more.

So how does one access all this using the friendly Model::find() in Lithium? If you're like me, you're probably coming from CakePHP and the find() will make a lot of sense. You'll be comfortable specifying the conditions key and order, etc. But you won't know what to make about some more of the complex things. So in this series I'm going to provide some simple examples of how to go and form those advanced queries using the Lithium framework.

The Task at Hand
So today I came across a situation where I have an array of data stored in MongoDB. I'll give you the exact context, this is for my Family Spoon project. This is the recipe record holding an array of data for all the families it's being shared with...Or requesting to be shared with upon admin approval. So this "families" array has multiple items each with keys of "family_id" and "approved" and "request_date" and "approval_code" and what not. All different data types right? Strings and booleans and timestamps, oh my! So on some overview admin page we want the user to see all these recipes that they need to either approve or reject. We also need to only show to other people only the approved recipes that are being shared on some other page.

So you're talking about your typical AND operator in MySQL. You're saying go get me records where the family_id is this AND the approved field is that. Great and using the JSON we use dots to get into those arrays and it translates to:

$conditions = array('families.family_id' => 123, 'families.approved' => true);

Or does it? Mwuahahaha....No it actually won't give you the results you expect. What that will do is give you all recipes where the family id is 123 and all recipes that are true. I know what that sounds like...BUT...not where both are strictly the case. So you're getting all these recipes that aren't approved, what gives? That's kinda more like an OR situation with MySQL right?

A little check into the MongoDB docs under the "Reaching into Objects" section should help clarify. It says blah blah blah, "subobjects have to match exactly", blah blah blah. Crap. So then what? 

$elemMatch To the Rescue!
Oh, we use this $elemMatch thingy. It allows us to specify these conditions for more complex objects how we would expect. The question is does Lithium have some array key called 'elemMatch' to use like 'conditions'?? No it doesn't and you don't need to make some custom query...You can use find(). So here's where the thinking cap went on...Er, the trial and error cap. Lithium DOES look for $ in conditions. So how do we write them? Like this:

$conditions = array('families' => array('$elemMatch' => array('family_id' => 123, 'approved' => true)));

Make sense? I don't think it's real obvious, but it's pretty simple looking at it. It's just that it's not well documented (yet). So this will build out the query the way it needs to be and return the results you'd expect, we wouldn't see any "approved" = false recipes in the results. Note that in this case we also are not using any dot notation. Keep in mind that running conditions like the following are also perfectly valid and work.

$conditions = array('families.family_id' => 123);

It just will give you simply all recipes with the family id of 123. So in some cases you will use the dot notation and in other special cases you'll need to see some of those operators with the dollar sign. So, fortunately, Lithium does allow us to use those operators and you do not need to make any sort of custom query and forfeit the use of the find() method. Hooray! Just keep one very important thing in mind...Your find() call may not be database agnostic anymore so if you're using multiple database types or want to make an app database agnostic, watch out. More to come soon.