QueryExecuter

Model class inherits QueryExecuter class (using delegation with the magic method).

Let's learn about QueryExecuter to make good Models.

Index

Creating QueryExecuter

Creating QueryExecuter for customers table

$qe = DB::queryExecuter('customers');

Note: The returned object is a singleton object for each tables.

Find the first record

Find the first record in the specified index order. If you do not specify the index number, the primary key will be used.

Use the primary key, without exception handling

$customer = $qe->first();
$id = $customer->id;  // $customer is a stdClass object.

If $throwException = true, it throws an exception if no record is found.

Use index 1, with exception handling

try {
    $throwException = true;
    $customer = $qe->index(1)->first($throwException);
} catch(\Exception $e) {
    // (omitted)
}

Get all records

Get all records in customer table

$customers = $qe->all(); // $customers is a collection of stdClass.

If results are multiple objects, Collection object will be returned.

Find a record by unique key

find or first can retrieve one record faster, using indexes. If you do not specify an index number, primary key will be used. The parameter of these methods is the value of the key.

Find a record which is id = 3 by the primary key

$customer = $qe->find(3);

Find a record which is name = 'John' by key index 1

$nameKeyNum = 1;
$john = $qe->index($nameKeyNum)->find('John');

Find a record by multiple segments (fields) key

$multiSegKeyNum = 2;
$customer = $qe->index($multiSegKeyNum)->find([3, '1567-900-00']);

Find multiple records at once

Find records id = 3, id = 4 and id = 6 by the primary key.

$customers = $qe->findMany([3, 4, 6]);

Find multiple records by multiple segments (fields) key

$customers = $qe->index(2)->findMany([[3, '1567-900-00'],[9, '1467-700-01']]);

Select fields

Specify some paramaters

$customers = $qe->select('id', 'name', 'note')->all();

Specify an array

$customers = $qe->select(['id', 'name', 'note'])->all();

If there is no select, all fields will be read.

Query conditions

Query methods can be chained. Call get or all method to execute query at the end. The result is always Collection object.

Basics of conditional expressions

The conditional expressions described below are similar to SQL, but not SQL. We explain the unique aspects of Transactd first:

Specify index

Specify the index number with index method. If omitted, primary key is automatically used.

$groupKeyNum = 1;
$qe->index($groupKeyNum);

Specify the value of the index field

Specify the value of index field(s) with keyValue method. keyValue can receive parameters up to 8, and multi-segment keys can be used.

Searching starts at the record specified by this value.

$qe->keyValue(1);

If there is no record specified by this value, the record that has next value becomes the start record.

Specify reject count

In the case of the condition expression contains index field(s) specified by index method, and the following condition is not or, the search will be stopped automatically when a record whose index field(s) value does not satisfy the condition is found.

Also, if the number of records that do not match the condition reaches the reject count, the search will be stopped. The default for reject count is 1.

In the following example, the search will be stopped when the number of records whose name is not 'akio' reaches 10.

$customers = $qe->index(1)->keyValue('')->where('name', 'akio')->reject(10);

Use noBreakReject method to disable stopping the search by the reject count. noBreakReject is an alias for reject (0xffff). It sets the magic number 0xffff to disable rejection.

Where

Find records name = 'John' by the primary key

$customers = $qe->keyValue(0)->where('name', 'John')->noBreakReject()->get();

Find records group >= 3 AND group < 5

$groupKeyNum = 1;
$customers = $qe->index($groupKeyNum)->keyValue(3)
    ->where('group', '>=', 3)
    ->where('group', '<', 5)->reject(1)->get();

WhereNull

Find records note = null

$customers = $qe->keyValue(0)->whereNull('note')->noBreakReject()->get();

WhereNotNull

Find records note != null

$customers = $qe->keyValue(0)->whereNotNull('note')->noBreakReject()->get();

OrWhere

Find records name = 'John' or name = 'Akio' by the primary key

$customers = $qe->keyValue(0)->where('name', 'John')
    ->orWhere('name', 'Akio')
    ->noBreakReject()->get();

OrNull

Find records name = 'John' or name = 'Akio' or note = null by the primary key

$customers = $qe->keyValue(0)->where('name', 'John')
    ->orWhere('name', 'Akio')
    ->orNull('note')
    ->noBreakReject()->get();

OrNotNull

Find records group = 3 or note != null by the primary key

$customers = $qe->keyValue(0)->where('group', 3)
    ->orNotNull('note')
    ->noBreakReject()->get();

WhereColumn

xxColumn methods compare the values of the fields.

Find records note = comment

$customers = $qe->keyValue(0)->whereColumn('note', '=', 'comment')->noBreakReject()->get();

OrColumn

Find records name = 'John' or note = comment

$customers = $qe->keyValue(0)
    ->where('name', 'John')
    ->orColumn('note', '=', 'comment')->noBreakReject()->get();

WhereIn

This condition will be converted to where or orWhere internally. Therefore it is necessary to specify keyValue and reject.

Find record id = 5 by the primary key

$customers = $qe->keyValue(5)->whereIn('id', [5])->reject(1)->get();

Find records id in 5,7,10,11 by the primary key

$customers = $qe->keyValue(5)->whereIn('id', [5,7,10,11])->reject(10)->get();

WhereNotIn

This condition will be converted to where or orWhere internally. Therefore it is necessary to specify keyValue and reject.

Find records id not in 5,7,10,11 by the primary key

$customers = $qe->keyValue(5)->whereNotIn('id', [5,7,10,11])->noBreakReject()->get();

WhereBetween

Find records id = 5 to 11 by the primary key

$customer = $qe->keyValue(5)->whereBetween('id', [5,11])->get();

If the condition field is a key field, reject is not necessary. If the condition field is not a key field, noBreakReject() is required.

WhereNotBetween

Find records not (id = 5 to 11) by the primary key

$customers = $qe->keyValue(0)->whereNotBetween('id', [5,11])->noBreakReject()->get();

WhereInKey

Find records by specified index.

Find records group = 2 by the group key

$groupKeynum = 1; // Non unique key
$customers = $qe->index($groupKeynum)->whereInKey([2])->get();

Find records by a part of multiple segments key

Find records which has leading segments 3 and4:

$index  = 2;   // Two segment unique key.
$segments = 1; // Number of segments for values.
$customers = $qe->index(2)->whereInKey([3, 4], $segments)->get();

Skip

Exclude first 10 records

$customers = $qe->skip(10)->all();

Take

Only get first 10 records

$customers = $qe->take(10)->all();

Get 10 records from the 21th record

$customers = $qe->skip(20)->take(10)->all();

Field name alias

If you use some aliases, use aliased names in the query.

$qg = DB::queryExecuter('groups');
$qg->setAliases(['name' => 'groupName']);

To check reading parameters

Use queryDescription() instead of get() chunk() cursor() update() delete(), and echo the result.

Get queryDescription

$params = $qe->keyValue(0)
    ->where('name', 'John')
    ->orColumn('note', '=', 'comment')
    ->noBreakReject()
    ->queryDescription();
echo $params;

Result

tablename      : customers
key read       : index = 0: id = 0
key write      : index = 0: id =
conditions     : name >= 'John' or note = [comment],
                 reject = 655361, limit = 0, stopAtLimit = 0

Many records

Chunk

You can split server access if there are many records in result. It can reduce memory usage.

Get 100 rows at once and sum amount

$amount = 0;
$qe->keyValue(0)->where('id' ,'>=', 50)->chunk(100,
    function($records) use (&$amount) {
        foreach($records as $record) {
            $amount += $record->amount;
        }
        return true; // If false is returned, the processing is interrupted.
    });

Cursor

When there are many records, you can use the cursor. It can reduce memory usage.

Sum amount with cursor

$cr = $qe->keyValue(0)->where('id' ,'>=', 50)->cursor();
$amount = 0;
foreach ($cr as $row) {
    $amount += $row->amount;
}

Join

All Transactd Join is done on the client side. You can Join data between different databases or servers.

There are two types of Join:

They are called Table Join and Recordset Join.

With QueryExecuter, first, set table or recordset on right side with join method. Next, call read trigger method. Then left side recordset will be got and joined with right side data.

Table Join

Table Join can be processed faster with the index of the joined table. The following restrictions apply to this method:

Most Join can do this way if the tables and their relationships are designed correctly.

Table InnerJoin

With Table InnerJoin, specify the following two in addition to the usual query conditions:

  1. QueryExecuter instance of joined table (table in recordset - table)
  2. Field name used for join

Note that specify only index and select to joined QueryExecuter. Do not call get.

Join group name by group number

// the table in "recordset - table Join"
$qg = DB::queryExecuter('groups');
$qg->setAliases(['name' => 'groupName'])
    ->select('groupName');
    ->index(0);

// the recordset in "recordset - table Join"
$qe = DB::queryExecuter('customers');
$customers = $qe->keyValue(1)
    ->select('id', 'group')
    ->where('id', '>=', 1)
    ->where('id', '<=', 10)
    ->join($qg, 'group')
    ->get();
Result
+------+-------+-----------+
|   id | group | groupName |
+------+-------+-----------+
|    1 |     1 | Users     |
|    2 |     2 | Guests    |
|    3 |     2 | Guests    |
|    4 |     3 | Unknowns  |
|    7 |     2 | Guests    |
|    8 |     3 | Unknowns  |
|    9 |     1 | Users     |
|   10 |     1 | Users     |
+------+-------+-----------+

In above example, first, get records id = 1-10 from customers table. Next get and join records on groups table with $qg, with using group field as the join key. It is also possible to specify multiple key columns as an array.

Table OuterJoin

Table OuterJoin is same as Table InnerJoin except that it does not remove rows even if there is no corresponding row in the result. Call outerJoin instead of join.

If there is no records to join, these fields will be null.

OuterJoin group name by group number

$qg = DB::queryExecuter('groups');
$qg->setAliases(['name' => 'groupName'])
    ->select('groupName');
    ->index(0);
$qe = DB::queryExecuter('customers');
$customers = $qe->keyValue(1)
    ->select('id', 'group')
    ->where('id', '>=', 1)
    ->where('id', '<=', 10)
    ->outerJoin($qg, 'group')
    ->get();
Result
+------+-------+-----------+
|   id | group | groupName |
+------+-------+-----------+
|    1 |     1 | Users     |
|    2 |     2 | Guests    |
|    3 |     2 | Guests    |
|    4 |     3 | Unknowns  |
|    5 |  NULL | NULL      |
|    6 |  NULL | NULL      |
|    7 |     2 | Guests    |
|    8 |     3 | Unknowns  |
|    9 |     1 | Users     |
|   10 |     1 | Users     |
+------+-------+-----------+

Recordset Join

Recordset Join has almost no restrictions compared to Table Join. You can join two recordset freely. For the join condition, the fields and the relationship between them can be specified.

Recordset InnerJoin

In Recordset Join, get data to be joined as recordset type in advance. To get it, use recordset method instead of get.

Get all group records as recordset type

$qg = DB::queryExecuter('groups');
$group_rs = $qg->setAliases(['id' => 'group_id', 'name' => 'groupName'])
    ->select('group_id', 'groupName')
    ->index(0)
    ->recordset(); // All group records

Join $group_rs and customers (id 1 to 10) with customer.group = groups.group_id

$rq = new RecordsetQuery();
$rq->when('group', '=', 'group_id')
$customers = $qe->keyValue(1)
    ->select('id', 'group')
    ->where('id', '>=', 1)
    ->where('id', '<=', 10)
    ->join($group_rs, $rq)
    ->get();
Result
+------+-------+----------+-----------+
|   id | group | group_id | groupName |
+------+-------+----------+-----------+
|    1 |     1 |        1 | Users     |
|    2 |     2 |        2 | Guests    |
|    3 |     2 |        2 | Guests    |
|    4 |     3 |        3 | Unknowns  |
|    7 |     2 |        2 | Guests    |
|    8 |     3 |        3 | Unknowns  |
|    9 |     1 |        1 | Users     |
|   10 |     1 |        1 | Users     |
+------+-------+----------+-----------+

The join condition is specified with RecordsetQuery. Specify the column name of the joining source as the first parameter, and the column name of the joining destination as the third, to when method. All conditions can be used, not just =. Multiple conditions can also be specified.

You can also join after fetching two recordsets in advance. In this case, use the Recordset::join method.

Join two recordsets $group_rs and $customers_rs with customer.group = groups.group_id

$rq = new RecordsetQuery();
$rq->when('group', '=', 'group_id')
$customers_rs->join($group_rs, $rq);
$customers = $customers_rs->toArray(); // Convert from recordset to object array

With this way, you can remove unnecessary lines in advance using Recordset::matchBy.

Use recordset::toArray to convert from recordset to object array.

Recordset OuterJoin

Recordset OuterJoin is same as Recordset InnerJoin except that it does not remove rows even if there is no corresponding row in the result. Call outerJoin instead of join.

If there is no records to join, these fields will be null.

GroupBy

Use GroupQuery to specify conditions for GroupBy.

Count the number of members in each groups

$gq = new GroupQuery();
$gq->keyField('group')->addFunction(new Count('memberCount'));
$result = $qe->groupBy($gq)->all();
echo 'group ='. $result[0]->group . ' member count = '. $result[0].memberCount;

OrderBy

Sort by group and name

$asc = true;
$customers = $qe
    ->orderBy('group', $asc) // The first sort key
    ->orderBy('name', $asc)  // The second sort key
    ->all();

MatchBy

MatchBy filters records on local. Use RecordsetQuery to specify conditions for it.

Filtering records note = null in local (after fetching)

$rq = new RecordsetQuery();
$rq->whenIsNotNull('note');
$customers = $qe->keyValue(5)
     ->whereBetween('id', [5,11])
     ->matchBy($rq)
     ->get();

Union

Unite recordsets id = 1 to 10 and id = 31 to 40

$rs = $qe->keyValue(1)
    ->where('id', '>=', 1)
    ->where('id', '<=', 10)
    ->recordset();
$customers = $qe->keyValue(31)
    ->where('id', '>=', 31)
    ->where('id', '<=', 40)
    ->union($rs)
    ->get();

Calculate

Let's do some calculations on sales table.

$qa = DB::queryExecuter('sales');

Count

Count sales in '2016-11'

$lines = $qa::index(1)->keyValue('2016-11-01')
    ->where('date', '>=', '2016-11-01')
    ->where('date', '<=', '2016-11-30')
    ->count();

Sum

Sum amount of sales in '2016-11'

$amount = $qa::index(1)->keyValue('2016-11-01')
    ->where('date', '>=', '2016-11-01')
    ->where('date', '<=', '2016-11-30')
    ->sum('amount');

Min

Get the minimum sales in '2016-11'

$amount = $qa::index(1)->keyValue('2016-11-01')
    ->where('date', '>=', '2016-11-01')
    ->where('date', '<=', '2016-11-30')
    ->min('amount');

Max

Get the maximum sales in '2016-11'

$amount = $qa::index(1)->keyValue('2016-11-01')
    ->where('date', '>=', '2016-11-01')
    ->where('date', '<=', '2016-11-30')
    ->max('amount');

Average

Get the average of sales amount in '2016-11'

$amount = $qa::index(1)->keyValue('2016-11-01')
    ->where('date', '>=', '2016-11-01')
    ->where('date', '<=', '2016-11-30')
    ->average('amount');

Snapshot

Read records which saved in same transaction (CONSISTENT_READ) many times with snapshot.

DB::beginSnapshot();
$rs = $qe->keyValue(1)
    ->where('id', '>=', 1)
    ->where('id', '<=', 10)
    ->recordset();

$customers = $qe->keyValue(31)
    ->where('id', '>=', 31)
    ->where('id', '<=', 40)
    ->union($rs)
    ->get();
DB::endSnapshot();

Execute database access

Some operations are queued in QueryExecuter, and will be executed at the trigger method, then it access to the database. Trigger methods are followings:

Execution order

At the trigger operation, queued methods will be executed in the order of queue.

For example:

$results = $qe->groupBy(xx)->orderBy(xx)->all();

Above code means:

$rs = $at->read($q);
$rs->groupBy(xx);
$rs->orderBy(xx);
$results = $rs->toArray();

Write operations

Insert

Insert a new record and get auto-incremented id.

$customer = $qe->insert(['id' => 0, 'name' => 'akio']);
echo $customer->id; // new id will be shown.

Update

Update record(s) that matches the condition.

Update a customer id = 100 name to 'hanako'

$count = $qe->keyValue(100)->update(['name' => 'hanako']);

Update all customers in group = 3 to group = 5

$count = $qe->index(1)->keyValue(3)->where('group', 3)->update(['group' => 5]);

Delete

Delete record(s) that matches the condition.

Delete a customer id = 100

$count = $qe->keyValue(100)->delete();

Delete customers group = 5

$count = $qe->index(1)->keyValue(5)->where('group', 5)->delete();

ServerCursor

Complex updates or deletions can be handled easily and efficiently by using server cursor. The server cursor can move one record at a time in the order of the index, and update or delete with checking the value.

If it is in a transaction, the current record is locked and can be updated safely.

Get server cursor

$op = self::SEEK_EQUAL;
$lockBias = Transactd::LOCK_BIAS_DEFAULT;
$forword = true;
$scr = $qe->index(0)->keyValue(5)->serverCursor($op, $lockBias, $forword);

To get the server cursor, call serverCursor after specifying index and key value. $op parameter which determines the cursor start position receives following values:

Specify the lock option for $lockBias. See Lock controls in Transactd for more details of the lock options.

There are two cursors, forward cursor and backward cursor. The direction is specified by $forward parameter.

SEEK_FIRST and SEEK_LAST ignores $forword parameter, and returns forward cursor or backward cursor.

Get current cursor and move it

Get the value of current record with current method. prev and next method move current record to previous and next.

$scr = $qe->index(0)->keyValue(5)->serverCursor(self::SEEK_EQUAL);
if ($scr->valid()) {                // Check move successful
    $customer = $scr->current();    // Get value
    if ($customer->active) {
        $scr->next();               // Move to next
        if ($scr->valid()) {
            $customer = $scr->current();
            // (omitted)
            $scr->prev();           // Move to previous
            // (omitted)
        }
    }
}

Check if it was able to move with valid method. If you use validOrFail, IOException will be thrown when the cursor is invalid.

$scr = $qe->index(0)->keyValue(5)->serverCursor(self::SEEK_EQUAL);
$scr->validOrFail(); // Throw IOException if the cursor is invalid
$customer = $scr->current();

You can also loop with foreach.

$scr = $qe->index(0)->keyValue(5)->serverCursor(self::SEEK_EQUAL);
foreach($scr as $customer) {
   if ($customer->id > 10) {
       break;
   }
   // (omitted)
}

Update and delete

Update or delete the current record with update or delete.

Updating operation throws IOException if it failed.

$scr = $qe->index(0)->keyValue(5)->serverCursor(self::SEEK_EQUAL);
foreach($scr as $customer) {
   if ($customer->id > 100) {
       break;
   }
   if (!$customer->active) {
       $scr->delete();          // Delete the current record
   } elseif ($customer->zip === '390-831') {
       $customer->zip = '390-0831';
       $scr->update($customer); // Update the current record with $customer
   }
}

Even if you change the key value on update, the position of the current record will not be changed by default. Deleting does not change the position too. Both of them can continue processing.

Insert

Append record with insert.

Inserting operation throws IOException if it failed.

$scr = $qe->index(0)->keyValue(5)->serverCursor(self::SEEK_EQUAL);
if (!$scr->valid()) {
    $customer = new stdClass;
    $customer->name = 'abc';
    $scr->insert($customer); // Append $customer
}

Inserting does not change the position of the current record.

Transaction and lock

Within a transaction, records that are read by write operations or server cursors are always locked. The default is exclusive lock. It is unnecessary to specify the lock for each operation.

For server cursor, you can also specify shared lock for each operation.

You can also specify the lock type and release on start of transaction.

Note: Transactd PHP ORM automatically sorts the reading access to the slave and the writing access to the master.

In transactions, it read-locks only the access to the master. Operations to table which got by queryExecuter::getWritableTable() access to the master. Specifically, they are insert, update and delete operations and server cursor. Other reads, such as find or get, are slave access. They do not read-lock records even in transactions.

However, if the slave host is omitted or the slave and the master are the same host, the table used for read and the table used for write are the same. So it will be read-locked even reading by find or get.

Lock types

There are two lock types for transactions:

row lock is used to lock the read record.

next key lock is used when you want to lock the read record and block inserting next of it.

If you need both within a transaction, specify next key lock.

Lock release

There are two type of lock release for transactions:

single record lock has superior concurrency because it holds a few records locks. Select this type if possible.

Even if you select single record lock, the records that are insert, delete, and update are kept locked until the transaction ends.

Examples of Transaction

Use single record lock trnasaction

try {
    DB::beginTransaction();
    $count = $qe->index(1)->keyValue(5)->where('group', 5)->delete();
    DB::commit();
} catch(\Exception $e) {
    DB::rollBack();
}

Use multi record lock and gap lock trnasaction

try {
    DB::beginTransaction(Transactd::MULTILOCK_GAP);
    $count = $qe->index(1)->keyValue(5)->where('group', 5)->delete();
    DB::commit();
} catch(\Exception $e) {
    DB::rollBack();
}

See InnoDB lock details (Japanese) about details of transaction lock.

Errors and Exceptions

Read operations do not throw exceptions when there is no record, except specifying $throwException = true or using xxxOrFail methods. They throw exception if an error occurs.

Write operations throws exceptions on error.

Get records as PHP hash array

Get records as a PHP hash array.

$recordset = $qe->keyValue(1)->where('id', '>=', 1)->recordset(); // Get as recordset

/* Specify fetch mode to get as array with field name key and field number key */
$recordset->fetchMode = transactd::FETCH_VAL_BOTH; 
$records = $recordset->toArray(); // Get with transactd::FETCH_VAL_BOTH

foreach($records as $record) {
    $id = $record['id'];
    // (omitted)
}

Get Transcatd native objects

You can also use the Transcatd native API easily.

Table

Get a writable table object.

$table = $qe->table();

ActiveTable

Get a readonly (not writable) activeTable object.

$at = $qe->activeTable();

Recordset

Use recordset() instead of get().

$recordset = $qe->keyValue(1)->where('id', '>=', 1)->recordset();

Database

Get the master database object with the default connection

$database = DB::master();

Get the slave database object with Internal connection

$database = DB::connection('Internal')->slave();