Table Advanced API Guide

From Joomla! Documentation

Introduction

This is one of a series of API guides, which aim to help you understand how to use the Joomla APIs through providing detailed explanations and sample code which you can easily install and run.

This is a companion guide to the Table Basic API Guide, and covers more advanced topics related to the Table class functionality.

These aspects are particularly relevant if you are developing a Joomla component and you have an associated database table or tables where you store your data.

The methods described below are all instance methods of the Joomla Table class, so you have to initialise your own table class as described in the Table Basic API Guide before you can use them.

The sample module code at the end of this guide demonstrates use of several of these functions.

Checkout / Checkin

Checkout capability avoids the unexpected results which can arise when 2 users edit the same record simultaneously. The Joomla pattern is that whenever a component displays an "edit" form for a selected record (allowing the user to edit the record), then at that time the record is updated to set the checked_out field to the userid of the user editing the record, and the checked_out_time field to the current date/time. Another user viewing the same record then sees a padlock symbol against the "locked" record and is prevented from editing it.

When the user editing the record successfully saves his/her changes, or cancels the edit, then the record is "checked in" by resetting the checked_out and checked_out_time fields.

checkOut(integer $userId, mixed $pk = null)

if the table has columns called checked_out and checked_out_time then this function sets the checked_out field to the passed-in $userId and the checked_out_time field to the current date/time for the record identified by the primary key $pk (or if $pk is null then the currently stored primary key is used, eg from the last load() call).

checkIn(mixed $pk = null)

if the table has columns called checked_out and checked_out_time then this function resets the checked_out and checked_out_time fields for the record identified by the primary key $pk (or if $pk is null then the currently stored primary key is used, eg from the last load() call).

isCheckedOut(integer $userid)

You can use this method to find if it's "safe" for the current user to update the currently loaded record. This method returns false if:

  • the checked_out field of this record indicates not checked out (eg field has a value of 0), or,
  • the checked_out field has the same value as $userid (ie it's checked out to this user), or,
  • the checked_out field indicates that the record is checked out to another user, but that user doesn't have a current session record.

In these cases it's arguably "safe" to update the values in the record and check it in.

(The absence of a session record indicates that the user has logged out, or that his/her session has expired and Joomla has subsequently performed a cleanup operation on the session records. But if that user had been editing a record and left it in a checked out state, then it will continue to be checked out if the user logs out or just closes the browser tab, and the record is still shown with a padlock symbol against it. However, you can argue that in that case it's safe to override the checkout.)

Despite what the API definition says, you shouldn't call this method statically.

The Joomla Table class doesn't include checkIn or checkOut functionality when you call methods such as load(), bind() or store(), but if you use save() as a shorthand for these then checkIn() is attempted within that method.

On the other hand, if your component controller and model inherit (either directly or indirectly) from the Joomla MVC FormController and FormModel then you are likely to find that the presence of columns called checked_out and checked_out_time in your table will result in checkout / checkin functionality being incorporated automatically, as outlined in J3.x:Developing an MVC Component/Adding Checkout.

ACL Assets

Joomla provides the ACL (Access Control List) framework for defining permissions which specify whether or not each user group may create, update or delete items; see for example J3.x:Access_Control_List_Tutorial, J3.x:Developing_an_MVC_Component/Adding_ACL. These permissions may be set within the admin backend within the permissions tabs of the edit forms, and can be at several levels:

  • at an overall component level (specified on the Global Configuration page)
  • for individual categories of a component (specified when you edit a category of that component)
  • for individual items of a component (eg article, contact), specified when editing that component item

Joomla stores all these permissions as "rules" within the assets table, and the name field defines which of the above 3 cases the asset record refers to. For example, for com_content possible values of the name field are as follows:

  • "com_content" – the ACL rules related to performing actions upon articles in general
  • "com_content.category.2" – the ACL rules related to articles having a category which has id=2
  • "com_content.article.4" – the ACL rules related to the article with id=4.

If a component allows setting ACL permissions at the individual record level, then the record should have a column called asset_id, and the value in this field of a record will be the id of the associated record in the assets table (ie the asset_id field is a foreign key, pointing to the asset record in the assets table).

The Table class has 2 public functions and 3 protected functions associated with managing assets.

getRules()

You might expect that after you had called load() to load a record you could use this function to find the associated asset rules. However it doesn't (it just returns null unless a previous setRules() call has been made) and to find the existing rules you need to read the associated assets record yourself. So it's arguably of little value outside core Joomla development.

setRules(mixed $input)

This function enables you to define the ACL rules which should be applied against this record. In array form a set of rules would be like the example below:

array("core.edit" => array(10 => 1, 11 => 0), "core.delete" => array(10 => 0, 11 => 1))

where 10 and 11 are ids of usergroups, and 1 is Allowed and 0 is Denied. In this example:

  • core.edit entry: usergroup 10 is allowed to edit the item, while usergroup 11 is denied edit access
  • core.delete entry: usergroup 11 can delete the item, while usergroup 10 is denied delete access

The $input parameter can be rules in the above array format, or in the equivalent json-encoded format, or in the Joomla Rules object format which is generated from passing either form to the Rules class constructor.

If you call setRules() to set the ACL permissions which should be applied to the record, and then call store() to save the record to the database, then the Table class functionality will update also the associated record in the assets table; you don't have to do this yourself.

However, in writing new assets records the Table class needs to know an additional 3 items of information, as you can see by browsing the assets records using phpmyadmin, for example. These 3 items of information it requests by calling 3 protected functions, which Joomla expects that you will provide in your own table class which inherits from Joomla Table. These are

  • _getAssetName() – to provide the name field – this is usually of the form "com_example.item.12"
  • _getAssetTitle() - to provide the title field – this is usually the title of the com_example item
  • _getAssetParentId() – to provide the position of the asset record in the asset hierarchy (implemented as a tree structure in the assets table using the Nested Model), by returning the parent asset id. For a com_example item this would usually be the record with just "com_example" as the name, ie the general ACL permissions for the com_example component.

Examples of these are in the sample module code at the end of this guide, and in J3.x:Developing_an_MVC_Component/Adding_ACL.

As an aside, note that Joomla itself doesn't use the above functionality for handling editing of the permissions within the admin backend. Instead it uses the following approach:

  • The permissions section of the admin edit form is output from a Rules form field type, one of the Joomla Standard form field types.
  • The PHP code of the Rules form field type prefills the existing permissions (in the edit form) by reading the asset data from the assets table (using the value inserted into the hidden asset_id field in the form to find the first asset record, and then traversing up the tree)
  • When a permission is changed in an HTML select element in the form, then an ajax call is made to the server to update the asset record. The values for the other fields (name, title, parent id) are inferred from the URL parameters
  • When Save etc is pressed to submit the form, then all the HTML select elements in the permissions tab are set to disabled, so that their values are not passed to the server in the HTTP POST. (This is done in the "submitbutton" javascript inserted within the component edit.php layout file.)

If you use the above paradigm, then you can avoid the necessity of using the setRules() functionality and having to provide methods for the above 3 protected functions.

Ordering

Many Joomla items include the concept of ordering, for example, modules have an order in which they are displayed in each possible template position. This is governed by a field called ordering in the database table, and when items are selected from the database the SQL query includes an ORDER BY `ordering` clause if they're to be output in order.

The Joomla Table class has 3 methods which relate to ordering.

getNextOrder(string $where)

This determines the max value of the ordering field among the records included by the optional where clause $where, and returns this max + 1.

Don't include the word "WHERE" in the $where string.

This function is useful if you're inserting a record into your table and you want it to appear at the end of the group of records identified in the where clause - you can set your ordering value to what is returned by getNextOrder(). If you want it to appear at the start you can set the ordering field to 0 (zero) and then use reorder() below.

reorder(string $where)

This function reads the set of N records defined by the where clause $where, using ORDER BY `ordering` and then writes them back with the ordering fields nicely numbered from 1 to N.

move(integer $delta, string $where)

This function basically finds the record with the next greater (if $delta is positive) or lesser (if $delta is negative) value of the ordering field, and it then swaps the ordering values of the two records.

Despite what the API definition says, the magnitude of $delta is irrelevant – the function always just moves the record by 1 in the ordering sequence.

This function might be useful if you provided users with a user interface which allowed them to move records up or down by one. However, Joomla now provides a more sophisticated mechanism built using javascript for dragging a record to a new position. When a record is dragged the javascript recalculates the ordering values of all the displayed records and sends the updated ordering values to the server in an ajax request, and they are then written to the database. This approach is used in J3.x:Developing_an_MVC_Component/Adding_Ordering

As an aside, the Table class doesn't support the case where you want to implement a tree structure in your component database table rather than simple ordering. To implement a tree structure you should instead inherit from Nested rather than Table, as outlined in J3.x:Developing_an_MVC_Component/Adding_Levels.

Publish

publish(mixed $pks = null, integer $state = 1, integer $userId)

This sets the publish field to $state for the set of records identified by the $pks array. It also sets the publish_up field (if it exists) to the current date/time. However the condition for doing this is if the currently loaded record has a blank publish_up value, rather than if the affected record has a blank publish_up value. (Looks like a bug).

If $pks is an array(field1 => value1, field2 => value2, …) then this gets mapped to the SQL where clause WHERE field1 = value1 AND field2 = value2, … to identify the records to be updated. If $pks is absent then just the current loaded record is affected.

Any records in this set which are checked out to another user are excluded. If there are no such records then any records of the affected set which are checked out to the current are checked in.

Note that this API isn't used within the core Joomla application. For one reason, it doesn't check that the user can perform the "core.edit.state" action on individual records before editing the state. It's probably better to follow the core Joomla code and use the publish() method with the MVC AdminModel class, rather than use this API.

Hits

hit(mixed $pk = null)

A hits counter is usually used to count the number of times a webpage is visited. In Joomla you can record how many times a component is displayed on the frontend by using adding a hits column to the component's database table, and calling this function each time the component is displayed.

The function simply increments the value in the hits field in the record identified by the $pk key, or in the currently loaded record if $pk is null.

Observers

Joomla implements an observer (aka publish and subscribe) pattern, where classes can register to get notified of events. In the case of Joomla Table, you can write an observer class which gets notified at the start and end of load(), store() and delete() operations.

To do this you write an observer class with 3 things:

  1. a static method createObserver($table) which then creates an instance of the observer class using new. You call this static createObserver($table) method from your table class, passing your $table class instance.
  2. a constructor __construct($table) which calls $table->attachObserver($this) to set this observer object as an observer of the table instance $table. You can also (optionally) store this $table reference locally, so that you can access it later.
  3. methods onBeforeLoad(), onAfterStore() etc which get called at the start / end of the code in Table load(), store() and delete() methods. You only have to write the methods associated with the notifications which you want.

See the sample module code at the end of this guide for a concrete example. The Joomla version history and tags features utilise this mechanism, and you can find further explanatory information at J3.x:Developing an MVC Component/Adding Versioning and J3.x:Developing an MVC Component/Adding Tags.

$table->attachObserver($observer)

Use this function to set the $observer instance to be an observer of your $table class instance. Then when the Table load() method runs you'll get $observer->onBeforeLoad() and $observer->onAfterLoad() methods invoked if they exist. Similiarly for store() and delete().

$table->getObserverOfClass(string $observerClass)

This returns the instance of your observer class which is observering the $table instance. Note that the parameter is the name of the observer class, not the name of the observed class.

Reflection Methods

There are several reflection-like methods which provide information about the table which is being accessed

getTableName – returns the name of the table "#__modules" for example

getKeyName – returns the name(s) of the primary key field(s)

getPrimaryKeys – returns an array of the primary key(s) and value(s)

getFields – returns an array of the names of the columns of the database table

hasPrimaryKey – checks if the primary key has a value set

hasField($name) – checks if the table has a field of that $name (since Joomla 3.9.11)

Reserved Column Names and Aliases

Much of the functionality associated with the Table class relies upon certain columns being given specific names, such as "ordering", "checked_out" and "asset_id". The presence of a column named with one of these special names is sufficient to switch on functionality in the Table class.

For a number of these names it is possible to set up an alias, so that if the field in which you store your published state is called "state" then you can use setColumnAlias() to set up an alias, for example,

$table->setColumnAlias('published', 'state');

The corresponding function is,

$alias = $table->getColumnAlias('published');

which using the example above would return the string "state".

In this way you are free to follow your own column naming convention but at the same time get access to Joomla core functionality. However, it's my opinion that you shouldn't take that approach, and instead you should name your columns to follow exactly the Joomla approach. The reason is that while Joomla helps you out with aliases here, there are other areas of code where aliases for those fields may not be supported, either now or in the future. In particular Joomla core javascript code assumes that certain fields are given certain names, and doesn't in general support aliases. An exception to the above is using the name "state" for the published state of a record instead of "published". Because com_content and other core Joomla components use "state" you are pretty safe taking that approach, because new code will always have to support those core components.

For reference, the fields with special meanings are listed in the table below

Field names Meaning
The following fields can be aliased in the Table class
checked_out, checked_out_time the userid to which the record is checked out, and the checkout date/time
hits the number of hits for that item
ordering the integer reflecting the record's order
published the published state of the item (Unpublished / Published / Trashed etc). Some core Joomla components use "state" for this column
publish_up the date/time the record was first published
The following fields have special meanings within Table, but cannot be aliased
asset_id the foreign key pointing to the associated assets record
The following fields have special meanings elsewhere in Joomla (this isn't an exhaustive list)
id the autogenerated id of the table
title the title of the item
alias the alias of the item
language the language code
access the id of the Access Level
params a JSON-encoded string of parameters
created_user_id, created_time userid of user who created the item, and date/time
modified_user_id, modified_time userid of user who modified the item, and date/time
catid id of the assigned category
parent_id, lft, rgt, level fields associated with nested tables

Sample Module

The example module below incorporates several aspects described above. It uses its own record in modules table to illustrate Table method calls, specifically:

  • using isCheckedOut() to check if a record is checked out or not
  • defining the ACL rules for the module record using setRules(), including defining the 3 protected functions to provide values for fields in the assets table
  • setting up an observer class using attachObserver(), and confirming this using getObserverOfClass()
  • use of the reflection method getTableName()
  • use of the some of the ordering methods

If you are unsure about development and installing a Joomla module then following the tutorial at Creating a simple module will help.

In a folder mod_advanced_table create the following 4 files:

The module manifest file mod_advanced_table.xml

<?xml version="1.0" encoding="utf-8"?>
<extension type="module" version="3.1" client="site" method="upgrade">
    <name>Advanced Table demo</name>
    <version>1.0.1</version>
    <description>Code demonstrating advanced use of Joomla Table class</description>
    <files>
        <filename module="mod_advanced_table">mod_advanced_table.php</filename>
		<filename>demomodules.php</filename>
		<filename>myobserver.php</filename>
    </files>
</extension>

The main module code mod_advanced_table.php

<?php
defined('_JEXEC') or die('Restricted Access');

use Joomla\CMS\Factory;
use Joomla\CMS\Table\Table;

$app = Factory::getApplication();
$input = $app->input;
$user = Factory::getUser();

echo "Module id is {$module->id}<br>";

// Tell Joomla to look in the current (module) directory for our table class file
Table::addIncludePath(__DIR__);
$moduleTable = Table::getInstance('DemoModules', 'Table', array());

if ($moduleTable->load($module->id))
{
	echo "<br>Module title is {$moduleTable->title}<br>";
	// checkout/checkin
	if ($moduleTable->isCheckedOut($user->id))
	{
		echo "<br>module record isCheckedOut call returned true<br>";
	}
	else
	{
		echo "<br>module record isCheckedOut call returned false<br>";
	}
	// ACL rules
	if ($rules = $moduleTable->getRules())
	{
		$rulesString = (string) $rules;
		echo "<br>ACL Rules: $rulesString <br>";
	}
	else
	{
		echo "<br>getRules() didn't return anything<br>";
	}
	$userGroups = $user->getAuthorisedGroups();
	$randomIndex = array_rand($userGroups);
	$newRule = array("core.edit" => array($userGroups[$randomIndex] => 1));
	echo "Setting rules to " . json_encode($newRule) . "<br>";
	$moduleTable->setRules($newRule);
	$moduleTable->store();
	// ordering
	$where = 'POSITION = "' . $moduleTable->position . '"';
	echo "<br>Next order value: " . $moduleTable->getNextOrder($where) . "<br>";
	
	$moduleTable->move(2, $where);
	echo "Ordering value is now: {$moduleTable->ordering}<br>";
	
	$where .= " and published = 1";
	$moduleTable->reorder($where);
}

The code for the table class, accessing the modules table demomodules.php

<?php
defined('_JEXEC') or die('Restricted access');
use Joomla\CMS\Table\Table;
class TableDemoModules extends Table

{
protected $_jsonEncode = array('params');

	function __construct(&$db)
	{
		parent::__construct('#__modules', 'id', $db);
		require 'myobserver.php';
		MyObserver::createObserver($this);
	}
	
	function check()
	{
		$this->note .= " added via module";
	}
	
	protected function _getAssetName()
	{
		return "com_modules.module." . $this->id;
	}
	
	protected function _getAssetTitle()
	{
		return $this->title;
	}
	
	protected function _getAssetParentId(Table $table = null, $id = null)
	{
		$assets = self::getInstance('Asset', 'JTable', array('dbo' => $this->getDbo()));
		$assets->load(array("name" => "com_modules"));
		return $assets->id;
	}
}

The code for the observer class myobserver.php

<?php

defined('JPATH_PLATFORM') or die;

class MyObserver implements JObserverInterface
{
	// Usually you would define your class as
	// class MyObserver extends AbstractObserver
	// and then the $table variable and a basic constructor would be included for you
	protected $table;
	public function __construct($table)
	{
		$table->attachObserver($this);
		$this->table = $table;
		$observers = $table->getObserverOfClass("MyObserver");
		$observersClass = get_class($observers);
		echo "<br>In MyObserver constructor.<br>";
		echo "<br>Object returned from getObserverOfClass() has class {$observersClass}<br>";
	}
	
	public static function createObserver($table, $params = array())
	{
		$observer = new self($table);
		return $observer;
	}

	public function onAfterStore(&$result)
	{
		echo "<br>In observer onAfterStore()<br>";
		if ($result)
		{
			echo "Got table store method notification for table {$this->table->getTableName()}, id {$this->table->id}<br>";
		}
	}
}

Zip up the mod_advanced_table directory to create mod_advanced_table.zip.

Within your Joomla administrator go to Install Extensions and via the Upload Package File tab select this zip file to install this sample mod_advanced_table module.

Make this module visible by editing it (click on it within the Modules page) then:

  1. making its status Published
  2. selecting a position on the page for it to be shown
  3. on the menu assignment tab specify the pages it should appear on

When you visit a site web page then you should see the module in your selected position.

The module includes the following functionality

  1. It outputs the id from the $module variable which is made available inside the module code. This is just so that you can use the Joomla admin back-end Modules page to confirm this id is what is expected.
  2. It sets up the DemoModules instance for accessing the #__modules table. Inside the constructor it runs the myobserver.php code which calls attachObserver() to set itself up as an observer of the TableDemoModules instance, and then confirms it's set up ok by calling getObserverOfClass() and outputting the result.
  3. It outputs $moduleTable->title, to confirm that it's read the #__modules record ok.
  4. It calls isCheckedOut() to determine if the record is checked out or not. Within the admin back-end you can edit the record to check it out, using the same or a different userid from the front end, and see the result.
  5. It calls getRules(), just to demonstrate that nothing is returned. You can copy this code to after the call to setRules() to see the difference.
  6. Using setRules() it configures a rule which is to set the permission "core.edit" to Allowed for a random user group to which the current user belongs, and then applies the changes with a store() call. You can confirm the result using the admin back-end or by examining the corresponding assets record using phpmyadmin.
  7. The store() call is notified to the observer onAfterStore() method, which outputs a message.
  8. The message includes using the getTableName() method to confirm that this relates to the #__modules table.
  9. It uses getNextOrder() to determine the value which should be assigned to the ordering field if you wanted this module to be at the bottom in your assigned module position. It then uses move() to move the module down one position. You can confirm its ordering has changed via the admin back-end, but the change may not be visible in the front end if you have modules at that position which are not published.
  10. Finally it tidies up the ordering values for modules displaying in that module position, but excluding from consideration the modules which aren't published.