This section considers the cmsModel class methods allowing to extract entries from the database.

All code examples imply that an action takes place in the model’s context (i.e. inside your model’s class inherited from cmsModel).

To fully understand the methods that are described here, you should be familiar with SQL requests.

There are several ways to get a row from the database table, depending on the options for searching a row.

All the methods return an array that contains the received entry’s fields or false, if an entry is not found.

getItemById($table_name, $id, $item_callback=false)

Example:

$comment = $this->getItemById('comments', $comment_id);

getItemByField($table_name, $field_name, $field_value, $item_callback=false)

Example:

$comment = $this->getItemByField('comments', 'is_deleted', 1);

getItem($table_name, $item_callback=false)

The options are set with the help of filters (see the Filtration section)

Example:

$comment = $this->
                filterEqual('is_deleted', 0)->
                filterLike('content', ‘%text%')->
                getItem('comments');

Use the following method to extract several rows from a table (or several joined tables):

get($table_name, $item_callback=false, $key_field='id')

Example:

$all_comments = $this->get('comments');

In this request, the $all_comments array will contain all rows from the comments table. You can limit the results by options and number. See below

Before requesting the get() method, you can query the following method to learn how many rows it will return:

getCount($table_name, $by_field='id')

The method returns the count.

Example:

$comments_count = $this->getCount('comments');

When counting, all previously set filters are taken into consideration (see the Filtration section).

The rows received from the base with the help of either the getItem() or get() method, can be processed before returning them. The processor is defined with the help of an anonymous function that is passed as the $item_callback parameter:

function($item, $model) { }

The function gets two input parameters:

The function can process the fields in the $item array or add new ones. After that it should return the array back:

$items = $this->get('my_table', function($item, $model){
 
    $item['date'] = date('d.m.Y', strtotime($item['date']));
 
    return $item;
 
});

In this example, the processor is used to bring the date field to a proper format in each entry. In fact, you should not do so, because it is the template’s task to format the date.

Most often, such processor is used to parse complex fields. For example, if one of the fields contains an array when saving an entry to the base (see the section below), it will be automatically transfromed to the YAML format and will be saved as a text. When extracting such an entry, you should manually perform a reverse transformation.

An example. Suppose, there is the my_table table with the id (int), title (varchar), and options (text) fields. We add an entry to the table to make the options field store several values at once:

$new_item = array(
    'title' => ‘New entry',
    'options' => array(
        'first' => 1,
        'second' => 2
    )
);
 
$new_item_id = $this->insert('my_table', $new_item);

Then we extract this entry back:

$loaded_item = $this->getItemById('my_table', $new_item_id);

In this case, we can expect the $new_item and $loaded_item arrays to be identical. However, it is not so. The options field in the $loaded_item array will be a text field, but not a nested subarray. For this reason, we should transform it back to an array. You can do this by means of a processor:

$loaded_item = $this->getItemById('my_table', $new_item_id, function($item, $model){
    $item['options'] = cmsModel::yamlToArray($item['options']);
    return $item;
});

The yamlToArray() method converts the YAML text back to an array. In this example, the array at output will be the same as the array at input.

The processor works the same for the get() method, the only difference being that it processes not one row but all rows received in turn.

You can use the following method to receive the value of a certain row’s field:

getField($table_name, $row_id, $field_name)

An example – we will get the date of a comment with ID35:

$pub_date = $this->getField('comments', 35, 'pub_date');

Rather than specifying a specific row ID, you can use a set of one or several filters. The following method is used to get the field values from a row, selected by filters:

getFieldFiltered($table_name, $field_name)

If the applied filters return more than one row, the specified field will be returned from the first row in the set of results.

An example – we are going to get the ID of a comment added by an administrator on a specific day:

$id = $this->
    filterEqual('user_id', 1)->
    filterEqual('pub_date', '2014-12-03')->
    getFieldFiltered('comments', 'id');

When reading the entries, the model can apply different filters that are described in details here.

The model can set the sorting order before extracting the rows with the help of the get() method.

orderBy($field, $direction)

An example – we will get a list of all users by sorting them by registration date descendingly (from new to old):

$users = $this->orderBy('date_reg', 'desc')->get('{users}');

As you can see in the example, the orderBy method supports “chains of requests”, in other words, we can request any other method of the $this object right after it.

orderByList($list)

An example – we are going to get all comments by sorting them by their rating and date:

$ordering = array(
    array('by' => 'rating', 'to' => 'desc'),
    array('by' => 'pub_date', 'to' => 'desc'),
);
 
$comments = $this->orderByList($ordering)->get('comments');

Before extracting the rows with the help of the get() method, the model can limit the number of results.

limit($from, $howmany=15)

This method is similar to the “LIMIT” condition in SQL-requests. Let’s consider an example. Suppose, we add a request that will return 100 rows from the base. If we specify a limit before making the request:

$this->limit(5, 10);

in the results, we will have not 100 rows but only 10 (from 5th to 15th).

You have to calculate the first row’s number for each page to get a paginated list of entries. You can use the following method to avoid manual manipulations:

limitPage($page, $perpage=15);

For example, such limit:

$this->limitPage(3, 10);

it will return the rows from 21 to 31 inclusively.

An example – we will get 10 latest comments (by date):

$latest_comments = $this->orderBy('pub_date', 'desc')->limit(10)->get('comments');

When extracting entries with the help of the get() method, the model can request several tables in the database at once, joining them. This is similar to the work of JOIN-conditions in SQL-requests.

There are several methods available:

join($table_name, $as, $on)

joinLeft($table_name, $as, $on)

joinLeftInner($table_name, $as, $on)

joinLeftOuter($table_name, $as, $on)

joinRight($table_name, $as, $on)

joinRightInner($table_name, $as, $on)

joinRightOuter($table_name, $as, $on)

joinInner($table_name, $as, $on)

joinOuter($table_name, $as, $on)

Each method adds the corresponding JOIN to the generated SQL-request. These methods should be requested right before receiving the results, i.e. before requesting the get() method.

The $as parameter sets the pseudonym (alias) of the joining table, usually, it is the first letter (or several letters) of the title.

The $on parameter describes the join condition (table relations) in SQL-format. Herewith, it is assumed that the source table has the «i» alias.

Once table relations are defined, you should specify which fields from the joined table you want to see in the search results. Use the following method for this:

select($field, $as)

Let's consider an example. There is the menu table in the base that contains a list of menus available on the site (main, side, bottom, etc.). These menus’ items are stored in the menu_items table . Each item in the menu_items table has the menu_id field that contains the id of a menu, to which this item belongs.

Suppose, we want to receive a list of all menu items and for each item we need a menu title:

// we link the menu table
// we assign the "m" alias to it
// the source table has the "i" alias
$this->join('menu', 'm', 'm.id = i.menu_id');
 
// we want to add the title (menu title) field from the menu table to results
// however, since the menu_items table has the title (item title) field too,  
// we will use another title - menu_title
$this->select('m.title', 'menu_title');
 
// as a result, we get the menu_items items
$menu_items = $this->get('menu_items');
 
// now we can sort all menu items
foreach($menu_items as $item){
    // and each of them will have the menu_title field available
    // received from a joined table
    echo $item['menu_title'];
}

Go to Editing Entries

Go to Filtration

Back to ”Models”

Back to Documentation for Developers