README

About Query

Query is class which helps us to build SQL query and get result from database.

With using Query class we have next advantages:

All values are properly escaped.
Same query can be executed on different databases.
Easy use of transactions and sub-transactions (aka savepoints)
…

Building quries

Query::select('column')
    ->from('table')
    ->where('column2', '=', 'value')
    ->orderBy('column3', 'asc')
    ->limit(2);

This is an example query which, give us following result:

SELECT `column` FROM `table` WHERE `column2` = ? ORDER BY `column3` ASC LIMIT 2;
-- values: ['value']

First when we want to create query, we call one of next functions which give use specialized Query for our statement:

::select(): Select
::delete(): Delete
::update(): Update
::insert(): Insert
::drop(): Drop
::create(): Create
::alter(): Alter

When we get query, we methods of this query. Because more queries uses same methods, they are stored separated and included as Traits.

Query methods

Each provided method accept values which are known as Query parts. Query part type is written before variable name (only in this documentation).

What can we pass for Query part is described in Query parts chapter.

From

from($table, $alias = null, Column $id1 = null, Column $id2 = null)

From which table we select data.

$q->from('table');
$q->from('table', 't');
$q->from('table t');
$q->from('table2', 't2', 't1.id', 't2.id_table1');
$q->from('table2 t2', 't1.id', 't2.id_table1');
$q->from('table2', 't1.id', 'table2.id_table1');

We can pass subquery too:

$q->from(Query::select()->from('other'), 'alias_must_exists');
// or
$q->from(function() {
    return Query::select()->from('tbl')->whereActive(true);
}, 'alias');

Join

join(Table $table, JoinCondition $condition): Inner join table
leftJoin(Table $table, JoinCondition $condition): Left join table
rightJoin(Table $table, JoinCondition $condition): Right join table

$q->join('table', ['table.id', 'other_table.id']);

Where and Having

where(Column $column, Raw $operator, Value $value, string $how = ‘and’, boolean $not = false)
having(Column $column, Raw $operator, Value $value, string $how = ‘and’, boolean $not = false)

$q->where('column', '=', 'value');
$q->where('column', 'between', [10, 20], 'or', true); // or not between 10-20

We can skip operator and write value instead (but in this case, we cant modify $how and $not values)

$q->where('column', 'value'); // column = value

__call() function simplify where call for us. We can concatenate more words for method name to get what we want: (Order is important!)

or: if we want connect this condition with OR (optional)
not: if we want negate this condition (optional)
where / having: one of them
column name: Column name in camelCase will be transformed into snake_case (optional)

When we call this special method, we need to pass folowing arguments:

column (if it is not passed yet)
operator (required)
value (required)

$q->whereId(1); // operator is skipped
$q->whereId('=', 1); // same thing
$q->whereAge('between', [10, 20]); // AND age between 10 and 20
$q->notWhereAge('between', [10, 20]); // AND NOT age between 10 and 20
$q->orNotWhereAge('between', [10, 20]); // OR NOT age between 10 and 20
$q->orWhere('specialColumn', 3); // AND specialColumn = 3

Operators

Default operators are:

IN: If value is array
IS: If value is null
=: For all other values

Special operators:

BETWEEN: it needs an array of two values for value

All other operators are transformed to upper case. Operator is not escaped or treated specially. It is concatenated to SQL statement like raw value. We can write into whatever we want (spaces and comment too… please don’t do any stupid)

Special use of where - parentheses

If we want determine operators priority, we can use where() function to put conditions in parentheses.

// WHERE active = true AND age = 10 OR age = 11
$q->whereActive(true)
    ->whereAge(10)
    ->orWhereAge(11);

// WHERE active = true AND (age = 10 OR age = 11)
$q->whereActive(true)
    ->where(function ($q) {
        $q->whereAge(10)
            ->orWhereAge(11);
    });

// High order function example
function find_best($sex) { return function($q) use ($sex) { /* ... */ } }
$q->where(find_best('female'));

In this example, all where conditions (not having) is putted into parens. In inner function we still can do whatever we want with query (order by, having, limit, …), if we really want this…

GroupBy

If we use having from previous chapter, we need first group some data.

groupBy(Column $column)

This is it. If we want group more columns, we can call groupBy() more times.

Order

orderBy(Name $column, $dir = ‘asc’)

$q->orderBy('model')
    ->orderBy('year', 'desc');

// ORDER BY model asc, year desc

Limit

limit(int $count, $offset = null): Standard limit
offset(int $offset): If limit is not specified yet it will be set to 1
page(int $page, int $per_page = null): First argument is page number (starting with 1), second argument is page size.

$q->limit(4); // LIMIT 4
$q->limit(4, 6); // LIMIT 4 OFFSET 5
$q->offset(3); // LIMIT 1 OFFSET 3
// ^ Actually limit should be 4, becouse we set it in second row

/* Page size = 10
 *
 * Page:
 * 1. 0-9
 * 2. 10-19
 * 3. 20-29
 */ 
$q->page(3, 10); // LIMIT 10 OFFSET 20

Union

union(Query $q)
unionAll(Query $q)

$q->union(Query::select()->from('table')->limit(1));

// If we are more comfortable with callback, we can use it:
$q->union(function() {
    return Query::select()
        ->from('table')
        ->limit(1);
});

Query parts

Query parts are stored in System/Database/Parts/. We can construct them with $part = OutPart::ensure([‘construct’, ‘values’]); or via constructor $part = new OurPart(‘construct’, ‘values’);

If Part is defined before Query method parameter (in this doc), value will be passed through Part::ensure($value) function. If value is already part (maybe Raw), ensure will not touch it, otherwise value will be constructed into declared Part.

Column::ensure('col'); /* is same as */ Column::ensure(['col']);

$q->where('col', '=', 24);

/*
 * So, because where is declared as: where(Column $c, $op, Value $v)
 * 'col' will be transformed with Column::ensure('col')
 * and 24 with Value::ensure(24);
 */

// If we want create Column like: new Column('table', 'col', 'alias');
$q->where(['table', 'col'], ...);

// becouse we can construct Column like: new Column('table.col alias'):
$q->where('table col');

// If we pass our own part into where, Column::ensure will skip it
$q->where(new Raw('COUNT(*)'), 5)

We can pass alias into where column too, but we don’t want to.

Raw

Arguments: (string $raw_value)

In raw we pass raw sql.

$q->select(new Raw('COUNT(*) count'))->from('table');

Literal

Arguments: (mixed $value)

Literal ensure that passed value will be properly quoted. Following methods are predefined:

Literal::wild(): new Raw(‘*’)
Literal::null(): new Literal(null)
Literal::true(): new Literal(true)
Literal::false(): new Literal(false)

We can pass into literal any value, and it will appear in sql statement:

$q->select(Literal::wild())
    ->where('active', Literal::true())
    ->notWhereLastLogin(Literal::null())
    ->where('string', new Literal('this is string'))
    ->where('age', 'IN', new Literal([20, 21, 22])); // We must write
                                                     // IN operator,
                                                     // becouse
                                                     // Literal is not
                                                     // recognized as
                                                     // array
// SELECT * FROM ? WHERE active = true AND NOT WHERE last_login = null AND string = 'this is string' AND age IN [20, 21, 22]
// We should specify IS operator for NULL too!

If we doesn’t use Literal, Query will transform values into Value part, which uses placeholders.

$q->select(Literal::wild())
    ->where('active', true)
    ->notWhereLastLogin(null)
    ->where('string', 'this is string')
    ->where('age', [20, 21, 22]);
// SELECT * FROM ? WHERE active = ? AND NOT WHERE last_login IS ? AND string = ? AND age IN [?, ?, ?]

Value

Arguments: (mixed $value)

All values passed into Query are transformed into Value. Value is stored into Query bindings and ‘?’ placeholder is inserted into SQL statement.

Fn

Arguments: (string $name, …$args)

Function call:

Fn::count(Column $column = null): (default is Literal::wild())
Fn::groupConcat(Column $column, string $sep = ‘,’): GROUP_CONCAT function

Other function call can be constructed via special __call() method like:

Fn::myOwnFunction(1,2,3): myOwnFunction(1,2,3);
Fn::THIS_IS_FN(1,’string’,new Value(24)): THIS_IS_FN(1,’string’,?);

Column

Arguments: (string $table_or_column, string $column = null, string $alias = null)

Column is part which represent column name with table (optional) and alias (optional).

We can construct it in more ways:

new Column(‘table’, ‘column’, ‘alias’)
new Column(‘table’, ‘column’)
new Column(null, ‘column’, ‘alias’)
new Column(‘table.column alias’)
new Column(‘table.column’, null, ‘alias’)

Becouse what we give into query, is passed through Column::ensure() function, we can define column in next ways:

$q->select('table.column alias', ['table', 'column'], ['t', 'c', 'als'], [null, 'col', 'alias'], 'col alias');

Name

Arguments: (string $name)

Name is like Column, but it doesn’t have table and alias. It is used for column and table names.

JoinCondition

Arguments: (Name $col1, $operator = null, Name $col2)

Join condition can be ON (c1 = c2) or USING (c).

echo new JoinCondition('col1', '=', 'col2'); // ON `col` = `col2`
echo new JoinCondition('col1', 'col2'); // same thing
echo new JoinCondition('col'); // USING (`col`)

Column names are automatically escaped, becouse they are transformed into Name part.

Table

Arguments: (Name or Query $table, Name $alias = null)

Table is used to specify source of data. It is used in join method.

$q->join(new Table(Query::select()->from('tbl'), 'alias'));
$q->join(new Table('table', 'alias'));
$q->join(new Table('tbl', 'alias'));
$q->join(new Table('tbl'));

// Becouse join already make Table part, we can skip new Table:
$q->join([Query::select()->from('tbl'), 'alias']);
$q->join(['table', 'alias']);
$q->join(['tbl', 'alias']);
$q->join(['tbl']); /* same as */ $q->join('tbl');

Inserting data

We can insert full row: $data1 = [1, 2, ‘value’];
We can insert key=>value row with default data: $data2 = [‘text’ => ‘value’];

Query::insert('table', $data1);
Query::insert('table', $data2);
// Dont forget to execute query...

// We can insert even more data at once
Query::insert('table', [$data1, $data1, $data1]); // id conflict with maybe
Query::insert('table', [$data2, $data2, $data]);

// But all data in query must have same format (full-row or key-value)

Updating data

$q = Query::update('table', [
    'col' => 'new value',
    'col2' => 'new value',
    'col3' => Fn::CONCAT('col4', 'col5')
]);

// if we forget something
$q->set('ups', null);

// Add filter
$q->whereId(3);

For filters documentation check Where and Having chapter.

Working with schema

For manipulating database schema, we have next queries:

::drop(): Drop
::create(): Create
::alter(): Alter

Droping table

Query::drop('table');
Query::drop('table')->ifExists();

Creating table

$q = Query::create('table_name', function($q) {
    $q->integer('id')->primary();
    // ... other column definitions
});

$q->ifNotExists();
// other table properties

Column types

Query support next column types:

$q->boolean('true_false');
$q->enum('sex', 'male', 'female', 'alien');
$q->set('digits', '0', '1', '2', '3', '4', '5', '6', '7', '8', '9');
$q->smallInt('small');
$q->mediumInt('medium');
$q->integer('int');
$q->bigInt('big');
$q->decimal('decimal', $precision = 10, $scale = 5);
$q->vachar('varchar', 255);
$q->varchar('text');
$q->timestamp('timestamp');
$q->time('time');
$q->date('date');
$q->datetime('date_time');
$q->year('year');

Some of them are not supported on all databases. For enum and set are used varchar on sqlite database. (Check Database/Parts/Sqlite/ColumnDef.php implementation.)

Column modifiers

Columns can have next modifiers:

$col = $q->integer('int_col');
$col->unsigned(); // unsigned value
$col->nullable(); // default is not null
$col->default(24); // default is null
$col->primary(); // primary key
$col->autoincrement();
$col->unique(); // unique index

Column references

We can make reference to other column:

Query::create('table', function($q) {
    $q->integer('id')->primary()->autoincrement();
});

Query::create('table2', function($q) {
    $q->integer('id')->primary()->autoincrement();
    $q->integer('id_table')->references('table.id');
});

Table modifiers

Table can have modifiers too:

$table = Query::create('table', function($q) { /* ... */ });
$table->temporary(); // create table in memory
$table->ifNotExists(); // skip if already exists

$table->option('key', 'value'); // custom option KEY VALUE

// Some of options are predefined
$table->engine('MyIsam'); // ENGINE=MyIsam
$table->charset('utf-8'); // CHARSET SET 'utf-8'
$table->defaultCharset('utf-8'); // DEFAULT CHARSET SET 'utf-8'

Alter table

Alter is buggy and untested, please report bugs.

Query::alter('table')->addColumn('new_column', 'varchar', 255);
Query::alter('table')->addColumn('new_int_col', 'integer')->nullable();

Query::alter('table')->modifyColumn('existing_column', 'text')->nullable(false);
Query::alter('table')->modifyColumn('existing_int_column', 'bigInt')->default(123);

Query::changeColumn('existing_column', 'new_name', 'varchar', 255)->nullable();

Executing queries

If we want execute query without needed any data in result, we simply call:

$q->execute();

Otherwise we use method ->fetch() to get next result. If we want all results in one call we use method ->fetchAll() which works exactly same way exept it returns all result in one array.

Fetch has first argument for specify which columns we want in result. If single column is passed single value is returned (scalar) if array is passed, array of values is returned.

$q->select('type', 'count')->from('table');

// Get first column
while ($type = $q->single()) {
    echo "Type: $type\n";
}

// Get one (named) column
while ($res = $q->get('count')) {
    echo "One column (count): $count\n";
}

// Get more columns
while ($res = $q->get(['type', 'count'])) {
    echo "{$res['type']} = {$res['count']}\n";
}

// Get row as object
while ($res = $q->get()) {
    echo "{$res->type} = {$res->count}\n";
}

$q->select('type', 'count')->from('table');

// Get first column
foreach ($q->singleAll() as $type) {
    echo "Type: $type\n";
}

// Get one (named) column
foreach ($q->all('count') as $res) {
    echo "One column (count): $count\n";
}

// Get more columns
foreach ($q->all(['type', 'count']) as $res) {
    echo "{$res['type']} = {$res['count']}\n";
}

// Get row as object
foreach ($q->all() as $res) {
    echo "{$res->type} = {$res->count}\n";
}


// Custom result transformation
$results = $q->all(['type', 'count'], function ($row) {
    return $row['type'] . ' => ' . $row['count'];
});
echo implode("\n", $results);

Working with objects

PHP PDO library has option to pack received data into an object. So if we use this feature we can fetch data like this:

$class = $q->fetch(MyClass::class);
assert($class instanceof MyClass);

But, we want using object to represent one row in database. For this purpose each class must have defined table name and primary key.

Using objects

Table name is class name in snake_case and primary key is ‘id’. We can change that with implement methods which returns table name and primary key.

class MyTable {
    public static function tableName() {
        return 'my_table_v2';
    }

    public static function primaryKey() {
        return 'id_my_table';
    }
}

Even if class doesn’t have these two methods, we can accessing to its table name and primary. But it must extends from QueryObject class.

class QueryObject {
    public static function primaryKey() {
        return 'id';
    }

    public static function tableName() {
        return snake_case(static::class);
    }
}

When we done this, we doesn’t need to remember table names any more:

Query::select()
    ->from(User::class)
    ->whereId(3)
    ->fetch(User::class);

NOTE: id is still column `id`. If we want find user by primary key, we should write: ->where(user::primaryKey(), 3)

In background Name and Column part check if table is a class and replace it with Class::tableName().

$q = Query::select('Obj.id objid', 'name')
   ->from(Obj::class)
   ->join(Obj2::class, ['Obj2.id_obj', 'Obj.id']) // id is not necessary primary
   ->where('Obj2.something', true)
   ->orderBy('Obj2.sort')
   ->fetch(['objid', 'name']);

There is only one problem. Obj::class could have namespaces A\B\Obj. In this case, Query will not found Obj class, because it doesn’t exists in Query package. So, when new object is declared (in from or join method), Query will create alias in one of his sub namespaces. When column is referencing a class, Query will look in this namespace if it exists.

Name conflicts

Maybe you think, what if I use two classes with same name in different namespaces?

namespace General {
    class User { } // tablename = users
}

namespace Deleted {
    class User { } // tablename = deleted_users
}

Query::select()
    ->from(General\User::class)
    ->from(Deleted\User::class)
    ->where('User.id', 3); // which one?

If you really want to have same object names, you can create class_alias(Existing::class, ‘NewName’) and use alias.

There is no problem using objects with same names on different databases. Query handle that properly.

$q1 = Query::select()
    ->from(mysql\User::class)
    ->where('User.id', 1); // referencing Query\Aliases\Mysql\User -> mysql\User

$q2 = Query::select()
    ->from(sqlite\User::class)
    ->where('User.id', 1); // referencing Query\Aliases\Sqlite\User -> sqlite\User

Query::withConnection('mysql', function() {
    $q1->fetch();
});

Query::withConnection('sqlite', function() {
    $q2->fetch();
});

Different connections will have different namespaces (eg: Query\Aliases\<Connection>\<Alias>) for used object aliases. But one connection must have unique names for objects.

This was a long chapter.

Joining with objects

Joins from previous chapter works, but they are not good. We still wneed to know relation between objects, their table names and their id columns.

Query::select()
    ->from(User::class)
    ->join(Group::class, ['group.id', 'user.group_id']);

If we want write this right, we should do it in that way:

Query::select()
    ->from(User::class)
    ->join(Group::class, [Group::class . '.' . Group::primaryKey(),
                          User::class . '.group_id']);

Uff… and we still need to know `group_id’ column everywhere in code.

Relation definitions

Basic idea is to write join definition into model and then use it all over the code.

class User extends QueryObject {
    protected class refGroup() {
        return Reference::toOne(Group::class, 'id_group');
    }
}

class Group extends QueryObject {
    protected class refUsers() {
        return Reference::toMany(User::class, 'id_group');
    }
}

Now we can start joining:

Query::select('g.name')
    ->from(User::class)
    ->join('User.group g');
// and
Query::count()
    ->from(Group::class, 'g')
    ->join('Group.users')
    ->where('g.name', 'Guest')
    ->single();

Problems:

[ ] Table can be aliased
[ ] …

Devel

class Model {
    public function tableName() {
        return 'ime_tabele';
    }
    public function primary() {
        return 'ID_ime_tabele';
    }
}

class Query {
    private $components = [];   // Unused...
    private $bindings = [];
    private $sources = [
        'table or alias' => 'source info',
        'alias' => 'table_name',
        'table_name2' => 'table_name2',
        // 'g' => new Source('groups', 'g')
    ];
    private $select = [
        'name' => 'whatever(*)',
        'count' => new Column(),
        'xxx' => new Raw()
    ];
}

abstract class Source {
    private $name;

    public function __construct($name) {
        $this->name = $name;
    }

    public function name() {
        return $this->name;
    }

    abstract public function primary();
    abstract public function table();

    public function debug() {
        echo "Table: `" . $this->name() . "` primary: `" . $this->primary() . "`\n";
    }
}

class TableSource extends Source {
    private $table;

    public function __construct($table, $name = null) {
        parent::__construct($name ?: $table);
        $this->table = $table;
    }

    public function primary() {
        return 'id';
    }

    public function table() {
        return $table;
    }
}

class ModelSource extends TableSource {
    private $model;

    public function __construct($model, $name = null) {
        $this->setModel($model);
        parent::__construct($model::tableName(), $name);
    }

    private function setModel($model) {
        // if (class_exists($model) and is_subclass_of($model, QueryObject::class)) {
        if (class_exists($model)) {
            $this->model = $model;
        } else {
            throw new Exception("Wrong class name '$model'"); // Should be QueryException
        }
    }

    public function primary() {
        $m =  $this->model;
        return $m::primary();
    }

    public function table() {
        $m = $this->model;
        return $m::tableName();
    }
};

$s1 = new ModelSource(Model::class);
$s2 = new ModelSource(Model::class, 'alias');
$s3 = new TableSource('tabela', 'alias');
echo "\n";
$s1->debug();
$s2->debug();
$s3->debug();

Table: `ime_tabele` primary: `ID_ime_tabele`
Table: `alias` primary: `ID_ime_tabele`
Table: `alias` primary: `id`

Query

class Query {
    // private $components = [];   // Unused...
    private $bindings = [];
    private $sources = [
        'table or alias' => 'source info',
        'alias' => 'table_name',
        'table_name2' => 'table_name2',
        // 'g' => new Source('groups', 'g')
    ];

    // private $select = [
    //     'name' => 'whatever(*)',
    //     'count' => new Column(),
    //     'xxx' => new Raw()
    // ];

    public function from($source, $alias = null) {
        if (class_exists($source)) {
            // it is model
            $source = new ModelSource($source, $alias);
        } else {
            $source = new TableSource($source, $alias);
        }

        $sources[$source->name()] = $source;
    }
}

Relations

Note taken on [2017-05-01 Mon 21:12]
Popravi query, da bo selektal samo prvo from tabelo (v primeru modelov)
Note taken on [2017-05-01 Mon 21:11]
‘id’ v querijih ni nujno primarni kljuc

class Person as QueryObject {
    public static function relGroup() {
        return self::reference()
            ->hasOne(Group::class, 'group_id');
    }

    public static function relPosts() {
        return self::reference()
            ->hasMany(Post::class, 'person_id');
    }
}

Query::select()
    ->from(Person::class)
    ->join('Person.group g')
    ->join('Person.posts posts')
    ->groupBy(Person::class)
    ->where('g.name', 'Regular user')
    ->having(Fn::count('posts.!id!'), '>', 10)
    ->all();

QueryObject

If we want using objects in Query, they must extends QueryObject. If we want different tablename or primary key for object, we can override tableName() or primaryKey() function.

In case we want totaly different rules to generate tablenames and primary keys, we can use our own QueryObject class which extends original QueryObject.

class QueryObject {
    public static function tableName() {
        return camel_case(drop_namespace(QueryObject::class));
    }

    public static function primaryKey() {
        return 'id';
    }
}

silverengine / reflectorm

Maintainers

Details