Skip to content

Latest commit

 

History

History
131 lines (111 loc) · 6.9 KB

3. Active Table.md

File metadata and controls

131 lines (111 loc) · 6.9 KB
Raw

PHPFUI\ORM Active Table

While an Active Record represents a single row in the database table, an Active Table represents the entire table. \PHPFUI\ORM\Table allows you to find, select, update, insert and delete multiple records at a time.

Active Tables are easy to manipulate in code and free you from constructing SQL statements with plain text.

The following things can be set programatically:

  • Join
  • Select
  • Where
  • Having
  • Limit
  • Union
  • Group By
  • Order By

WHERE and HAVING Conditions

Conditions for WHERE and HAVING are created using the \PHPFUI\ORM\Condition class.

Equal is the default for a Condition

The following code sets up an equal ('=') condition:

$condition = new \PHPFUI\ORM\Condition('lastName', 'Rubble'));

You can add an AND or OR clause to the condition easily:

$condition->and('firstName', 'Barney');

The above will produce the PDO equivalent of lastName = 'Rubble' AND firstName = 'Barney'

You can also OR in a condition like:

$condition->or('firstName', 'Fred');

The above will now produce the PDO equivalent of lastName = 'Rubble' AND firstName = 'Barney' OR firstName = 'Fred'

You can parenthesize a condition by adding to another condition:

$conditionA = new \PHPFUI\ORM\Condition('lastName', 'Rubble');
$conditionA->and('firstName', 'Barney');
$conditionB = new \PHPFUI\ORM\Condition('lastName', 'Flintstone');
$conditionB->and('firstName', 'Fred');
$condition = new \PHPFUI\ORM\Condition();
$condition->or($conditionA);
$condition->or($conditionB);

The above will produce the PDO equivalent of '(lastName = 'Rubble' AND firstName = 'Barney') OR (lastName = 'Flintstone' AND firstName = 'Fred')'

Other operators

You can also make conditions with the following operators as the third parameter to \PHPFUI\ORM\Condition:

For example:

$conditionA = new \PHPFUI\ORM\Condition('lastName', 'R', new \PHPFUI\ORM\Operator\GreaterThanEqual());
$conditionA->and('lastName', 'S', new \PHPFUI\ORM\Operator\LessThan());
$conditionB = new \PHPFUI\ORM\Condition('lastName', 'F', new \PHPFUI\ORM\Operator\GreaterThanEqual());
$conditionB->and('lastName', 'G', new \PHPFUI\ORM\Operator\LessThan());
$condition = new \PHPFUI\ORM\Condition();
$condition->or($conditionA);
$condition->or($conditionB);

The above will produce the PDO equivalent of (lastName >= 'R' AND lastName < 'S') OR (lastName >= 'F' AND lastName < 'G')

The In and NotIn operators require the second parameter to Condition to be an array. The Like and NotLike operators will respect the % and _ characters, but you are responsible for putting them in the correct positions.

Literal and Field classes

Sometimes you need to compare something to a SQL constant or call a function. You can use new \PHPFUI\ORM\Literal('CURRENT_TIMESTAMP()').

If you need to specify a table for a field to make it unique, you can use new \PHPFUI\ORM\Field('order.order_id', 'orderId'). The second parameter is the AS clause.

Once you have a condition

You can set the table to use a WHERE or HAVING condition:

$orderTable = new \App\Table\Order();
$orderTable->setWhere($whereCondition)->setHaving($havingCondition);
$cursor = $orderTable->getRecordCursor();

Select fields

By default all fields (*) are selected for a table. You can add specific selects with addSelect. Use the second parameter for the as option. Or specify a complete Select clause with setSelectFields().

Limits and Offsets

You can specify a start offset by calling setOffset($offset). The number of records to return is setLimit($limit). For easy pagination, you can use setLimit($limit, $page) where $page is the page number (zero based) to start on. The offset will be computed by the $limit specified.

Group By and Order By

You can specify Group By and Order By with:

  • addGroupBy($field), add an additional group by clause
  • setGroupBy($field), resets the group by clause to the field specified
  • addOrderBy($field), add an additional order by clause, defaults to ascending, use the second parameter to specify 'desc'
  • setOrderBy($field), resets the order by clause, defaults to ascending, use the second parameter to specify 'desc'

Joins

Joins on a the primary key of the joined table can be easily accomplished with:

$orderDetailTable = new \App\Table\OrderDetail();
$orderDetailTable->addJoin('order');

If a more complicated on condition is required, you can pass a \PHPFUI\ORM\Condition object as the second parameter to addJoin

The third parameter is the join type (LEFT, INNER, OUTER, RIGHT, FULL, CROSS). Default is LEFT.

The forth parameter is the AS option.

Unions

Unions can be implimented by configuring another table with a matching number of selects and then calling addUnion. Unions can have full Join, Where, Group By, Having, Order By and Limit clauses.

$table = new \Tests\App\Table\InventoryTransactionType();
$table->addSelect('inventory_transaction_type_id', 'id');
$table->addSelect('inventory_transaction_type_name', 'name');
$table->addUnion(new \Tests\App\Table\OrderDetailStatus());
$table->addUnion(new \Tests\App\Table\OrderStatus());
$table->addUnion(new \Tests\App\Table\OrderTaxStatus());
$table->addUnion(new \Tests\App\Table\PurchaseOrderStatus());
$table->addOrderBy('name');

Explain

You can get the execution plan for the current \PHPFUI\ORM\Table query with the getExplainRows method. The results of this are dependent on the underlying database and can change.