Contao Query builder

This extension provides a query builder based on the aura/sqlquery.

Install

You can install this library using Composer. It requires at least PHP 5.5 and Contao 3.2.

$ php composer.phar require netzmacht/contao-query-builder:~1.0

Documentation

Please refer to the aura/sqlquery documentation to understand the basic usage.

The Contao integration adds easy execution of the created statements and DI integration using c-c-a/dependency-container.

Basic usage

<?php

$factory = $GLOBALS['container']['query-builder.factory'];

// Creates insert query implementing interface Netzmacht\Contao\QueryBuilder\Query\Insert
$insert  = $factory->newInsert();

// Creates insert query implementing interface Netzmacht\Contao\QueryBuilder\Query\Update
$update  = $factory->newUpdate();

// Creates insert query implementing interface Netzmacht\Contao\QueryBuilder\Query\Select
$select  = $factory->newSelect();

// Creates insert query implementing interface Netzmacht\Contao\QueryBuilder\Query\Delete
$delete  = $factory->newDelete();

// Executing the statement
$result = $statement->execute();

Extended features

Though this extension is based on aura/sqlquery it provides some extra features.

Query conditions

If you have complex where conditions you can pass an callback which generates a separate condition object.

<?php

// Generates "category = 2 AND (date > ? OR highlighted = 1)"
$query
    ->where('category = 2')
    ->where(
        function (Netzmacht\Contao\QueryBuilder\Condition $condition) {
            $condition->orWhere('date > ?', time());
            $condition->orWhere('highlighted = 1');
        }
    );

Where in statements

As Contao does not use PDO as driver you have to manually create whereIn statements. So that whereIn and orWhereIn are provided for queries and for the conditions.

<?php

// Generates "category = 2 AND (date > ? OR highlighted = 1)"
$query
    ->whereIn('category', [2, 3])
    ->whereIn('author', [3, 4, 2]);
    

Differences

Though you can use named bind values the generated statement does only contain ? placeholders as Contao only support these.