Joomla Table Class for CRUD Operations

The Joomla Table class provides a framework which enables you to do CRUD operations and more on database tables. This class can be used for single record only and it doesn't support SQL operations on multiple records.

Table is an abstract class with methods which support interaction with a general database table. While developing your component, you need to define your own class which inherits from Table. Here, you need to specify the name of the database table you want to access.

Namespace:

use Joomla\CMS\Table\Table;

Initialisation

First, call the static function Table::getInstance() passing 2 parameters:

  1. type: This is the name of the entity you are going to be accessing in your table. For example, "Contact", "User". Joomla will look for a php file with this name when it is looking for your class which extends Table.
  2. prefix: This is a string with which the "type" is prefixed to create the name of your class. This parameter is optional, the default is "JTable".

Basic Table Operations

After the initialisation is completed, you will have a table reference you can use to perform database CRUD operations on your database table.

1. load($key, $reset)

The Table::load() reads the record from the database and sets up class properties whose name is the same as the database field name, giving them the value of that field from the database record.

2. bind($src, $ignore)

The Table::bind() sets up those class properties from the $src associative array.

3. check()

You can include any validation you want to perform before the data is written to the database. The Table check() method is blank.

4. store()

The Table store() code will take the Table properties, match them to the column names of your table and write them to the database using a SQL INSERT or SQL UPDATE statement depending upon whether the primary key already exists or not.

5. save()

This method is a combination of bind(), check() and store() methods.

6. delete($key)

This method deletes the database record whose primary key is given by the $key.

7. 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 and time.

8. 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 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.

CRUD Operations

The above methods can be used insert, update or delete database records.

1. Insert Record

  • bind() to set the field values.
  • check() to perform any validation.
  • store() to save the new record to the database.

2. Update Record

  • load() to load the existing record from the database, passing the primary key of the record.
  • bind() to set the new values for the fields.
  • check() to perform any validation.
  • store() to save the new values to the database.

3. Delete Record

  • load() to load the record.
  • delete() to delete it.

Ordering

Many Joomla items include the concept of ordering. This is governed by a field called ordering in the database table. The Joomla Table class has three methods which relate to ordering.

1. getNextOrder(string $where)

This determines the max value of the ordering field among the records included by the optional where clause $where, and returns max + 1. This function is useful if you are inserting a record into your table and you want it to appear at the end.

2. 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. This function is useful if you want to insert a record at the start. Set the ordering field to 0 (zero) and then use reorder() method.

3. move(integer $delta, string $where)

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

Reflection Methods

There are several methods which provide information about the table which is being accessed.

1. getTableName

It returns the name of the table.

2. getKeyName

It returns the name of the primary key field.

3. getPrimaryKeys

It returns an array of the primary keys and values.

4. getFields

It returns an array of the names of the columns of the database table.

5. hasPrimaryKey

It checks if the primary key has a value set.

6. hasField($name)

It checks if the table has a field of that $name.

Reserved Column Names

Much of the functionality associated with the Table class relies upon certain columns being given specific names.

  • checked_out, checked_out_time
  • hits
  • ordering
  • published
  • publish_up
  • asset_id
  • id
  • title
  • alias
  • language
  • access
  • params
  • created_user_id, created_time
  • modified_user_id, modified_time
  • catid
  • parent_id, lft, rgt, level