Skip to main content

This site requires you to update your browser. Your browsing experience maybe affected by not having the most up to date version.

UncleCheese
5th February 2015

In this lesson, we’ll use the backbone of SilverStripe Framework -- the Object Relational Model (ORM) to syndicate content to the home page of our website.

Introduction to the ORM

Level: Beginner

Duration: 11:47

In this lesson:

The ORM provides a layer of abstraction between your PHP code and the contents of the database. Any time you're reading or manipulating records, the ORM is most likely going to be involved.

ORM Basics

Here's an easy way to conceptualise the ORM:

  • Classes refer to tables
  • Instances of classes refer to records
  • Properties of objects refer to columns

For a class to follow this paradigm, it must be a descendant of DataObject. So far, we've been dealing only with subclasses of Page, which is itself a DataObject (DataObject -> SiteTree -> Page). For generic data types that do not need page functionality, you can go further up the inheritance chain and simply subclass DataObject directly. We'll be talking a lot more about non-page database content in the next tutorial.

Dealing with individual records

Let's imagine we have the following simple DataObject class:

class Product extends DataObject {
    private static $db = array (
      'Title' => 'Varchar(100)',
      'Price' => 'Currency',
    );
 }

The DataObject class gives us four properties to start with:

  • ID - primary key
  • ClassName - a hint to ORM what class should be created for the record. In this case its default value is “Product.”
  • Created - a timestamp of when the record was stored in the database first time
  • LastEdited - a timestamp that is updated every time the record is written to the database.

In adherence to the ORM pattern, creating a record is as simple as creating an instance of your DataObject subclass.

    $product = Product::create();

Keep in mind that DataObjects do not optimistically write to the database.

    echo $product->ID; // 0

In order to persist the record, we need to invoke its write() method.

    $product->write();
    echo $product->ID; // 442

For convenience, the write() method returns the record's ID.

    echo Product::create()->write(); // 443

Further, the create() method can populate the object with data if passed an array.

    $product = Product::create(array('Title' => 'My first product'));

To delete a record, call delete().

    $product->delete();echo $product->ID; // 0

To update it, just modify the properties of the object, then invoke write().

    $product->Price = 2.49;$product->write();

This should all feel very natural once you become acquainted with it.

Dealing with aggregate data

ORMs are essentially just APIs for writing SQL queries. If you've ever worked on a project where SQL was written inline with PHP code, you probably don't have to struggle to see the value in such a tool. In general, database queries and controller/view logic do not mix. We try to avoid this as much as we can, as it can become really redundant. Just look at how much repetition exists between these three queries:

$sql = “SELECT ID, Created, LastEdited, Title, Price FROM Product”;

$sql = “SELECT ID, Created, LastEdited, Title, Price FROM Product WHERE Price > 100”;

$sql = “SELECT ID, Created, LastEdited, Title, Price FROM Product WHERE Price > 100 LIMIT 5”;

On top of that, you need to deal with the boilerplate of suppressing SQL injection and other foundational elements of database integration. Further, if you ever change database platform, the code doesn't ship well.

You might create helper functions to assemble queries like this, but ultimately, this approach doesn't scale well. Frameworks unanimously prefer to hand off database all database work to its own layer.

Because database queries are about lists of records, not individual records, the methods we're going to use are all scoped to the class definition. As stated before, a class is essentially a reference to a table, so this should make sense. In object-oriented programing, methods invoked not against an instance of a class, but rather the class itself, are referred to as static methods. Let's take a look at how this works.

First, we'll get all of our products.

$products = Product::get();

Now, let's get all products that cost $100.

$products = Product::get()->filter(array(
  'Price' => 100
));

Now, let's get the top five most expensive products under $100:

$products = Product::get()
               ->filter(array(
                 'Price:LessThan' => 100
               ))
               ->sort('Price','DESC')
               ->limit(5);

As you can see, all these methods are chainable, with one important caveat: they are immutable. That means that each one of these methods returns a new list. Once you store the list in a variable, you cannot update it. You can only overwrite the previous variable.

$products = Product::get();
$products->limit(5);

echo $products->sql(); // does not contain a limit clause

Note: the sql() method is rarely needed. It is typically only used for debugging.`

This is a very common mistake that people make with the ORM. If we were writing jQuery, the above approach would have applied both methods to the instance, but the ORM does not follow that pattern. Immutable data structures are becoming increasingly more common in web frameworks.

Let's update that code so that both clauses apply.

$products = Product::get()->sort('Price', 'DESC');
$products = $products->limit(5);

echo $products->sql(); // contains a sort clause and a limit clause

Notice that we can either chain the methods on creation or overwrite the variable each time. Both achieve the same result.

About lazy loading... meh, maybe later.

Perhaps one of the strongest features of the SilverStripe ORM is its lazy loading of data. This is to say that a query is not executed until it absolutely has to be, resulting in not only fewer queries, but also more efficient queries.

Let's look at the following example:

    $products = Product::get();

Common thinking would tell us that the get() method we invoked would execute a query similar to this:

    “SELECT ID, Created, LastEdited, Title, Price FROM Product”

In actuality, no query is run when this method is called. Rather, SilverStripe just makes a note that at some point, you may be interested in getting all the products.

Let's take it a step further:

    $products = Product::get();
    foreach($products as $product) {
        echo $product->Title;
    }

Now we have run a query, because the foreach loop has told the ORM that the we need the records, and it's time to act. Up until then, the list of products was merely an idea.

What's so great about this? Several things. One is that we have far fewer “wasted” queries. For example:

    $articles = ArticlePage::get();
    $articles = $articles->filter(array('Author' => 'Aaron'));
    $articles = $articles->limit(5);

    foreach($articles as $article) {
      // ...
    }

Even though we're invoking several methods on the query, only one is actually executed.

Further, lazy loading presents the ORM with an opportunity to optimise the query just before it executes.

    $products = Product::get();
    echo $products->count();

Rather than returning something like a sizeof() on the resulting array of records, the ORM is smart enough to see that all you really want is a count, and it executes something like the following:

    “SELECT COUNT(*) FROM Product”

Typically in SilverStripe, it is a <% loop %> block on your template, if not a foreach in your controller, that actually tells the query to run.

Using the ORM in a controller

Let's now take all of this into practice and add a custom database query to a controller. Looking at the home page, we see there is a section where the latest articles are syndicated. To get these to display, we'll need to write a method in our controller to fetch the list. Let's call it LatestArticles.

class HomePage_Controller extends Page_Controller {

  public function LatestArticles() { 
    return ArticlePage::get()
               ->sort('Created', 'DESC')
               ->limit(3);
  } 
}

There is no need to pass this method into the template. Because it's in the controller and defined as public, we can access it using $LatestArticles. Let's update HomePage.ss to loop through the articles.

<h1 class="section-title">Recent Articles</h1>
<div class="grid-style1">
  <% loop $LatestArticles %>
    <div class="item col-md-4">
        <div class="image">
            <a href="$Link">
                <span class="btn btn-default"> Read More</span>
            </a>
            $Photo.CroppedImage(220,148)
        </div>
        <div class="tag"><i class="fa fa-file-text"></i></div>
        <div class="info-blog">
            <ul class="top-info">
                <li><i class="fa fa-calendar"></i> $Date.Format('j F, Y')</li>
                <li><i class="fa fa-comments-o"></i> 2</li>
                <li><i class="fa fa-tags"></i> Properties, Prices, best deals</li>
            </ul>
            <h3>
                <a href="$Link">$Title</a>
            </h3>
            <p><% if $Teaser %>$Teaser<% else %>$Content.FirstSentence<% end_if %></p>
        </div>
    </div>
    <% end_loop %>
</div>

There's one minor improvement we can make to this function. Right now, the limit of three records is hardcoded in the controller, which isn't very configurable. The reason we're limiting the result set is due to the constraints imposed by the layout, so it makes more sense to assign this value on the template.

Update the LatestArticles function to accept a $count parameter, and set its default value to 3. Pass this parameter into the limit() method.

class HomePage_Controller extends Page_Controller {

  public function LatestArticles($count = 3) {
    return ArticlePage::get()
                 ->sort('Created', 'DESC')
                 ->limit($count);
  }
}

Now, on the template, simply use <% loop $LatestArticles(3) %>.

Questions and Feedback

Great work. This is great the Tutorials and SilverStripe is fantastic the best. Keep it up. Just a few things as a total beginner.

LESSON 3 The copy & paste gives and a sleepless night. <% loop $Menu(1) %>

  • $MenuTitle
  • <% end_loop %>

    Also the text link at the bottom of Lesson 3 gives a 404 Error.

    Lesson 5-9 I start looking for the static HTML to do the lesson with on the lesson page rather than go to the next lesson to find it.

    Hope this helps you with some feedback, it's truly a great system.

    Cheers John PS. Fancy a Kiwi being "DRY". I haven't mentioned the cricket or Sonny Bill yet Bro.

    by john at 05:06pm, 20 March 2015

    Lesson 8 is key , shows how to push data to the DB and return it. I don't however find any of the code examples in the GIT or how to execute them. Is all of this done within the context of the web browser?

    by Steve at 01:06pm, 10 April 2015

    Author

    Hi, Steve,

    If you want to see the completed code for the lesson, simply check out the next adjacent lesson, e.g. lesson-9, which starts with the completed state of Lesson 8.

    We should probably clarify that a bit, so that the branches are named lesson-8-start and lesson-8-end.

    by UncleCheese at 04:27pm, 11 April 2015

    Oh you Uncle Cheese,

    $me=>'Programmer', $me.I_was=> 'HelpDesk/SysAdmin',

    $me->getBigThankYou()->owner;

    yeah i know it's all wrong, but I like to write like this...

    Can we have also your PPT ??? are useful I think.

    Thank you very much for your job!

    by Andrew at 09:58am, 8 October 2015

    Hi Uncle Cheese,

    at first thank you for this great series of tutorials. I realy tried hard to find the database zip files. Are they still available?

    Thanks Greg

    by Gregor303 at 05:40am, 6 March 2017

    Stuck on something? Have something to share? Don't be shy!

    Keep learning!

    Working with Files and Images

    In the previous lesson, we started adding some basic custom fields to our Article pages. We’ll now continue working on getting those pages more integrated with...

    Adding custom fields to a page

    In the previous lesson, we developed a structure for our Travel Guides section that provides a list view of articles, each with a link to their...

    The holder/page pattern

    In this tutorial we’re going to focus on the Travel Guides section of our website for this topic. As we can see in the designs, there...