JDatabase, SQL statement template

Question

Is there a way to use a statement template, like by Hibernate HQL?

SQLQuery sql=s.createSQLQuery("SELECT AVG(RATING) as r, COUNT(*) as c FROM RATINGS WHERE ADVENTURE_ID = ?");
sql.setParameter(0, adventureId);

Or if we are not talking about ORMs, then like by PDO?

$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)");
$stmt->bindParam(':name', $name);
$stmt->bindParam(':value', $value);

I really don't want to concatenate strings and quote the injected data manually, that is so disappointing in 2017.

$db = JFactory::getDbo();
$db->setQuery('SELECT params FROM #__extensions WHERE name = ' . $db->quote('com_democompupdate'));

or even worse

$query->select($db->quoteName(array('user_id', 'profile_key', 'profile_value', 'ordering')));
$query->from($db->quoteName('#__user_profiles'));
$query->where($db->quoteName('profile_key') . ' LIKE '. $db->quote('\'custom.%\''));
$query->order('ordering ASC');

I don't really need ORM in this project, the db will be MySQL forever, I just need a way to get connection, begin transactions and execute statement templates.

Note that I don't want to create a new PDO connection, I'd like to do this with a JDatabase connection. I don't want the component to know anything of the db account.

Answer 1

I found there is a $query->bind method, so it is possible to create a prepared statement, or whatever we call it. But in my case this is not that simple. I have version 3.4.3, I am not the site maintainer, so I don't want to change the configuration or migrate to a newer version. I don't want to experiment on the production server and I don't have a development environment for PHP, only notepad. This is because I am working only on a small component and I don't want to buy IDEs like PHPStorm just to end this at most 5 days project. I test in production too. I understand this is far from perfect...

In the configuration I found that $dbtype = 'mysqli'. Afaik. mysqli supports prepared statements too, but that does not necessarily mean the mysqli driver supports it too.

In the early Joomla 3.x in 2014 only SQLite and Oracle had PDO based drivers, and the other drivers did not support prepared statements.

I found that my Joomla version is from 2015, and this feature was added to the mysqli driver in 2016. I wrote a simple QueryTemplate class, I'll use that as workaround. I strongly recommend to everybody else to migrate to a new version if it is possible and never test your code in production environment! :D :D :D

I found that my version supports transactions. At least that is okay. I decided to wrap the joomla class, but this is not mandatory, you can use the template without that.

JoomlaQueryTemplate.php:

namespace Canteen\infrastructure;

use Canteen\infrastructure\iTemplate;
use JFactory;
use Exception;

class JoomlaQueryTemplate implements iTemplate {

    public function __construct($template){
        if (!is_string($template))
            throw new Exception('Invalid SQL template.');
        $this->template = $template;
    }

    public function evaluate($data){
        return preg_replace_callback('/:(\w+)/usD', function ($match) use ($data) {
            $param = $match[1];
            if (!array_key_exists($param, $data))
                throw new Exception('Not given param: '.$param);
            $value = JFactory::getDbo()->quote($data[$param]);
            return $value;
        }, $this->template);
    }

}

JoomlaConnection.php

namespace Canteen\infrastructure;

use Canteen\infrastructure\JoomlaQueryTemplate;
use JFactory;

class JoomlaConnection implements iConnection {

    protected $connection;

    public function __construct(){
        $this->connection = JFactory::getDbo();
    }

    public function execute($template, $data = array()){
        $queryTemplate = new JoomlaQueryTemplate($template);
        $query = $queryTemplate->evaluate($data);
        $this->connection->setQuery($query);
        $this->connection->execute();
    }

    public function getId(){
        return $this->connection->insertid();
    }

    public function query($template, $data = array()){
        $queryTemplate = new JoomlaQueryTemplate($template);
        $query = $queryTemplate->evaluate($data);
        $this->connection->setQuery($query);
        $this->connection->execute();
    }

    public function isEmpty(){
        $rowsCount = $this->connection->getNumRows();
        return $rowsCount == 0;
    }

    public function getMany(){
        return $this->connection->loadObjectList();
    }

    public function getOne(){
        return $this->connection->loadObject();
    }

    public function getValues(){
        return $this->connection->loadColumn();
    }

    public function getValue(){
        return $this->connection->loadResult();
    }

    public function beginTransaction(){
        $this->connection->transactionStart();
    }

    public function commit(){
        $this->connection->transactionCommit();
    }

    public function rollback(){
        $this->connection->transactionRollback();
    }

}

The connection is injected into my repos and app services. So the app services can handle transactions and the repos can send sql queries. For example:

public function readStatistics(){
    $statisticsDTO = new CustomerStatisticsDTO();
    try {
        $this->connection->beginTransaction();
        $statisticsDTO->setTotalCount($this->repository->countCustomers());
        $statisticsDTO->setActiveCount($this->repository->countActiveCustomers());
        $statisticsDTO->setSuspendedCount($this->repository->countSuspendedCustomers());
        $this->connection->commit();
    }
    catch (Exception $exception){
        $this->connection->rollback();
        throw $exception;
    }
    $statisticsDTO->setPassiveCount($statisticsDTO->getTotalCount() - $statisticsDTO->getActiveCount());
    $statisticsDTO->setOrderingCount($statisticsDTO->getActiveCount() - $statisticsDTO->getSuspendedCount());
    return $statisticsDTO;
}

and

public function countSuspendedCustomers(){
    $today = new DateTime('today');
    $this->connection->query(
        'SELECT COUNT(`#__canteen_customers`.`user_id`) AS `result` '.
        'FROM `#__canteen_customers` '.
        'WHERE '.
            '0 < ('.
                'SELECT count(`#__canteen_suspensions`.`suspension_id`) AS `active_suspension_count` '.
                'FROM `#__canteen_suspensions` '.
                'WHERE '.
                    '`#__canteen_customers`.`user_id` = `#__canteen_suspensions`.`user_id` AND '.
                    '`#__canteen_suspensions`.`suspension_from` <= :date AND '.
                    '(`#__canteen_suspensions`.`suspension_to` IS NULL OR `#__canteen_suspensions`.`suspension_to` >= :date)'.
            ') AND '.
            '`#__canteen_customers`.`customer_active` = TRUE',
        array(
            'date' => $today->format('Y-m-d')
        )
    );

    return (int) $this->connection->getValue();
}

(I know that I don't have to rollback by select statements only, but I just copy-pasted that part of code, and it does not do any harm. It would be better with unit of work I guess, but I am learning that pattern later.)

I am pretty sure that the original joomla query builder api is good as well, but I felt more natural to use prepared statements, because I did not have to learn how to translate between the query builder code and the resulted sql. I don't want to use for example pgsql later, so it is okay for me to write sql instead of generating it. Ofc. if you can choose, then install the newest joomla version, which supports prepared statements, and use that instead of this.