Lithium: Getting Stuff From Your Database

In my last post about the Lithium framework, I remarked that I found the user guide to be a little bit difficult to use.  I don’t like being one of those people that complains and then runs off to go complain about the shortcomings of something else.  That is not the spirit of open source in my opinion, and I would rather spend my time contributing than complaining.  As a result, I have spent some time working on user guide contributions, and have also come to learn that there is a new user guide in the works which looks to be very good.

With that in mind, today I want to provide a tutorial on how to do some basic interaction with a database in Lithium, and I am going to rely on MySQL as a database for the purposes of this tutorial, although you have other options like MongoDB, as an example.

Data Retrieval Tutorial

First, let’s establish the existence of the database and table we are going to be working with.  The name of the database is test and the table name is blog_posts.  The table name is very important because if you follow the Lithium convention of naming tables, your work later on will be much easier.  The rule is: All lowercase plural table names with an underscore for a space will translate back to a CamelCase class name.  So in our example, by default, a class named BlogPosts will map by default to a table called blog_posts.  So our blog_posts table consists of:


Moving on…
The next task at hand is to create our model.  You have 2 choices.  Yes, that’s right, there are 2 ways to create your model.  The first is to just go into your IDE and create a file in the \app\models directory.  I like things that match up, so I would create the BlogPosts.php file and the code would consist of the following:

Pretty straightforward, you just have to remember to declare your namespace properly and extend the core Model class using the fully qualified name.  No big deal.  The other way you can do it, if you have set up the command line interface feature for Lithium is to type li3 create model BlogPosts  on the command line and the \app\models\BlogPosts.php file will automatically be created with the following code:

I do not think it needs to be said, but I am going to anyway:  Why wouldn’t you use the CLI for this?  Beyond the pure savings of time, it will absolutely prevent you from messing up a namespace or some other part of the code.  The Lithium CLI is your friend…get to know it.

Ok, now we have a model and we want to get a list of all of our blog posts, so we should probably create a method in the model to do that, right?  WRONG!  Just go to your controller and get the data you need. I am going to use the PagesController.php file that comes with Lithium for demonstration purposes; it comes with a method called view().

This is pretty much the way you will see it done in the user guide.  The call to BlogPosts::find('all') creates an object that you can work with.  As simple as that is, what I really want is a quick way to get data from the table into an array that I can send to populate the view, and this just doesn’t do it that way.  Fortunately, Lithium makes this pretty easy to accomplish:

By simply adding the to('array')  method call at the end, we get a very clean multidimensional array where the first key is the id of the post, and the next dimension of keys are the field names from the blog_posts table.

That is the basic layout of how Lithium works for simple data retrieval, and the reality is that users are able to get an array of data with one CLI  command and one line of code.  That, to me at least, is pretty efficient.

Filtering Results

Most of the time, getting all records is not very realistic.  Typically we are looking for records that meet a certain set of criteria.  What we really need in this tutorial is an explanation of how to limit results from the database based upon a set of given conditions.  The second parameter in the find method allows you set options for your query.  The settable elements are:

  • ‘conditions’: associative array of where conditions.  e.g. array(‘status’ => ‘draft’)
  • ‘fields’: Fields to be retrieved
  • ‘order’ => ASC or DESC to specify how the records are to be ordered
  • ‘limit’ => Number of records to return
  • ‘page’ => records for pagination of data
  • ‘with’ => array of relationship names to include in the query

Applied practically, let’s say that we want to get only the posts that are in a published status.There are a couple of ways to do this.  First, you can use the find() method just as I have described above and apply a filter:

This simply adds a condition to the results to only retrieve records that are in a published status.

Dynamic Finder Methods

Lithium takes another step and allows you to use dynamically generated finders that require no programming or setup on the part of the user.  This is extremely powerful when you want to quickly get records based on a single field filter/condition.  Let’s look at the same example, but with a dynamic finder:

The code above  instructs Lithium to get all records that have a published status and send the records to an array.  Keep in mind, that in every example, we have done nothing to the model other than define it as shown above.  We have not added the findAllByStatus() method to the model, Lithium has natively interpreted the method call and turned it into the desired result.


This is just the basic overview of data retrieval in Lithium.  There are more tools available, just as default query options for your model and building custom finders.  These topics are covered in the Lithium manual and for those who are interested in getting the maximum functionality from the framework.  No matter how you decide to use it, Lithium provides some very flexible and intuitive data retrieval options right out of the box that can have you spending less time writing methods in your models and more time getting your application finished.

Happy Coding


, ,

  1. No comments yet.

Comments are closed.