Database queries

Queries in Joomla are not written as an SQL string. They are assembled with a builder — an object with the methods select(), from(), where(). It inserts the table prefix, quotes names and protects against SQL injection. The models of the previous lesson already used the builder; let us go through it piece by piece.

Where to get the database object

The third way needs the lines use Joomla\CMS\Factory; and use Joomla\Database\DatabaseInterface;.

A simple query

// components/com_hello/src/Model/PostsModel.php, inside a model method
$db = $this->getDatabase();
 
$query = $db->getQuery(true)
    ->select($db->quoteName(['id', 'title']))
    ->from($db->quoteName('#__hello_posts'));
 
$posts = $db->setQuery($query)->loadObjectList();

getQuery(true) creates a new empty query. quoteName() quotes column and table names. setQuery() hands the query to the database, and loadObjectList() runs it and returns an array of objects: $posts[0]->title.

Conditions, ordering, limit

$query = $db->getQuery(true)
    ->select($db->quoteName(['id', 'title', 'created']))
    ->from($db->quoteName('#__hello_posts'))
    ->where($db->quoteName('published') . ' = 1')
    ->order($db->quoteName('created') . ' DESC')
    ->setLimit(10);

Several where() calls are joined with AND. setLimit(10) keeps ten rows; the second argument is how many rows to skip.

Values from the user

Anything that came from outside is not glued into the query but bound as a parameter. Let us add a title search to the list model:

// components/com_hello/src/Model/PostsModel.php
use Joomla\CMS\Factory;
use Joomla\Database\ParameterType;
 
protected function getListQuery(): QueryInterface
{
    $db = $this->getDatabase();
 
    $query = $db->getQuery(true)
        ->select($db->quoteName(['id', 'title', 'created']))
        ->from($db->quoteName('#__hello_posts'))
        ->where($db->quoteName('published') . ' = 1')
        ->order($db->quoteName('created') . ' DESC');
 
    $search = Factory::getApplication()->getInput()->getString('q', '');
 
    if ($search !== '') {
        $search = '%' . $search . '%';
 
        $query->where($db->quoteName('title') . ' LIKE :search')
            ->bind(':search', $search);
    }
 
    return $query;
}

The query holds the :search placeholder, and the value is passed separately with bind(). Open index.php?option=com_hello&view=posts&q=first — one post remains in the list. For numbers state the type: ->bind(':id', $id, ParameterType::INTEGER).

Reading the result

For example, the number of published posts:

$query = $db->getQuery(true)
    ->select('COUNT(*)')
    ->from($db->quoteName('#__hello_posts'))
    ->where($db->quoteName('published') . ' = 1');
 
$total = (int) $db->setQuery($query)->loadResult();

Inserting and updating

$post = (object) [
    'title' => 'New post',
    'body' => 'Text of the post',
];
 
$db->insertObject('#__hello_posts', $post, 'id');
 
$post->title = 'Corrected title';
$db->updateObject('#__hello_posts', $post, 'id');

Object properties are table columns. The third argument is the primary key column: after the insert $post->id holds the number of the new row, and updateObject() uses it to find the row to change.

Deleting

$id = 5;
 
$query = $db->getQuery(true)
    ->delete($db->quoteName('#__hello_posts'))
    ->where($db->quoteName('id') . ' = :id')
    ->bind(':id', $id, ParameterType::INTEGER);
 
$db->setQuery($query)->execute();

Queries that return nothing are run with execute(). Pass a variable to bind(), not an expression: the method takes the value by reference.

Common mistake

A value is glued into the query text: ->where('title = "' . $title . '"'). A quote in $title breaks the query, and a crafted string lets someone read or wipe other data — this is SQL injection. Always use bind(). And never write a real prefix such as abc12_: on another site it is different — only #__.