5.4. Models

5.4.1. Introduction

Trex abstract model is very straight forward. In order to shorten the learning curve we’ve decided not to use an object-relational mapping framework. If you wish to use an external framework for storing and retrieving data, please download and install Propel.

We recommend using SQL for retrieval and management of data. SQL was adopted as a standard by the ANSI and ISO. By using SQL, you avoid adding an extra layer of complexity to the system, and you don’t lose syntax validity checking and highlighting. Also, coding standards helps ensure that the queries are easily maintained.

“Dealing with complexity is an inefficient and unnecessary waste of time, attention and mental energy. There is never any justification for things being complex when they could be simple.” - Edward de Bono

Database Abstraction Layer

Creole is the default database abstraction layer. It abstracts PHP’s native db-specific API to create more portable code while also providing developers with a clean and fully object-oriented interface based loosely on the API for Java’s JDBC.

Model Architecture

In Trex, models are classes extending the Model class and they are always defined in the app/modules/<module_name>/model directory. You might have a model class that contains functions to insert, update, and retrieve data.

Example: Basic prototype for a Model class

class <module_name>Model extends Trex_Model
{
	public function <method>(<args>)
	{
	}
}

There are different ways of structuring and organizing your data:

  • Using Creole Wrapper Class.
  • Using Creole API.
  • Using PDO Wrapper Class (Zend_Db). Note: PDO ships with PHP 5.1.

5.4.2. Using Creole Wrapper Class

The Creole Wrapper class provides methods for interacting with Creole easily and securely.

Example: Using Creole Wrapper Class

class NewsModel extends Trex_Model
{
        // select(string statement [, array placeholder])
        public function selectNewsItemsByGroupId($groupId)
        {
                $statement = "SELECT * FROM news_items WHERE group_id = ?";
                $placeholder = array($groupId);

                // @return mixed Result set on success, false on failure
                return $this->getDb()->select($statement, $placeholder);
        }

        // insert(string table_name, array fields [, bool on_duplicate_key_update])
        public function insertActivationCode($userId, $activationCode)
        {
                $fields = array(
                        "user_id" => $userId,
                        "activation_code" => $activationCode,
                );

                // @return mixed Affected rows on success, false on failure
                return $this->getDb()->insert("activation", $fields);
        }

        // update(string table_name, array fields, array where_clause)
        public function updatePassword($userId, $password, $salt)
        {
                $fields = array(
                        "password" => $password,
                        "salt" => $salt,
                );

                $where = array(
                        "statement" => "user_id = ?",
                        "placeholder" => array($userId),
                );

                // @return mixed Affected rows on success, false on failure
                return $this->getDb()->update("user", $fields, $where);
        }

        // delete(string table_name, array where_clause)
        public function deleteActivationCode($userId)
        {
                $where = array(
                        "statement" => "user_id = ?",
                        "placeholder" => array($userId),
                );

                // @return mixed Affected rows on success, false on failure
                return $this->getDb()->delete("activation", $where);
        }
}

You should always use the “?” place holder to add values to a statement, as this will prevent SQL injection attacks by ensuring that data is converted to the proper type, and when appropriate escaped and quoted.

Read more about: Database_Wrapper_Creole

5.4.3. Using Creole API

Creole is inspired by Java’s JDBC API. If you are familiar with this API, then no doubt much of this will look similar.

Example: Using Creole API

class NewsModel extends Trex_Model
{
        public function selectNewsItemsByGroupId($groupId)
        {
                $db = $this->getCreole();

                // prepared statement
                $stmt = $db->prepareStatement("SELECT * FROM `news_items` WHERE group_id = ?");
                $stmt->setInt(1, $groupId);
                $stmt->setLimit(10);
                $stmt->setOffset(5);

                return $stmt->executeQuery();
        }
}

The first parameter to !Model::getCreole() is a flags parameter. The following flags are currently supported by Creole:

Flag Description
self::PERSISTENT Open persistent database connection
self::COMPAT_ASSOC_LOWER Always lowercase the indexes of assoc arrays (returned by functions like *_fetch_assoc())
self::COMPAT_RTRIM_STRING Trim whitepace from end of string column types
self::COMPAT_ALL Enable all compatibility constants

These flags are bits, so you may specify more than one by adding (or using bitwise | operator) them together. Example:

$conn = $this->creole(self::PERSISTENT | self::COMPAT_ASSOC_LOWER);

5.4.4. Invoking Models

A Model class can be loaded and accessed from anywhere within a project. To load a model you can use the getModel() method. This method provides a singleton instance of the Model class.

Example: Within a Module

class News extends Trex_Module
{
        public function actionIndex()
        {
                $groupId = 3;
                $rs = $this->getModel()->selectNewsItemsByGroupId($groupId);

                foreach ($rs as $row) {
                        // $row is an assoc array
                        print_r($row);
                }
        }
}

5.4.5. Invoking Shared Models

To load a Model class from a different module, you can use the getModel() method and pass the name of the module as a parameter:

  • mixed getModel ( [string module_name] )

Example: Within a Module

class News extends Trex_Module
{
        public function actionIndex()
        {
                $rs = $this->getModel("articles")->selectLatestArticles();

                foreach ($rs as $row) {
                        // $row is an assoc. array
                        print_r($row);
                }
        }
}

Example: Within a Project

Trex_Loader::loadModel("articles");

Next page: Getting Column Values

Pages: 1 2