Querying the database

Once you have the model and the repository, you can query the database using the repository.

The results will be returned as a collection of the model defined in the repository.

e.g.:

$query = \ByJG\MicroOrm\Query::getInstance()
    ->table('users')
    ->fields(['id', 'name'])
    ->where('name like :part', ['part' => 'A%']);

// Will return a collection o 'Users'
$collection = $repository->getByQuery($query);

or, you can also get a single record by its ID:

$myModel = $repository->get(1);

Some cases you need to query a database using join and want to return a collection of objects from different tables.

<?php
$query = \ByJG\MicroOrm\Query::getInstance()
    ->table('order')
    ->join('item', 'order.id = item.orderid')
    ->where('name like :part', ['part' => 'A%']);

// Will return a collection of Orders and Items:
// $collection = [
//     [ $order, $item ],
//     [ $order, $item ],
//     ...
// ];
$collection = $orderRepository->getByQuery(
    $query,
    // Add additional mappers to the query
    [
        $itemRepository->getMapper()
    ]
);

QueryBasic Object

The query basic object contains the essential methods to query the database. It can be used in the Union class, and can be converted to a Updatable object.

Methods

table(string $tableName, string $alias = null)

The table to query.

fields(array $fields)

Array of fields to retrieve. if not set, will retrieve all fields.

e.g.:

->fields(['id', 'name'])

field(string $field, string $alias = null)

A single field to retrieve. You can set an alias for the field.

e.g.:

->field('username', 'login');

where(string $where, array $params)

The where clause. You can use placeholders in the where clause.

e.g.:

->where('name like :part', ['part' => 'A%'])

The placeholders can be named or unnamed. If you use named placeholders, you need to pass an associative array with the values.

If you use unnamed placeholders, you need to pass an array with the values in the same order as the placeholders.

• Named placeholders are defined by a colon followed by the placeholder name;
• Unnamed placeholders are defined by a question mark. The arguments are positional

whereIsNull(string $field)

Add a WHERE field IS NULL condition.

e.g.:

->whereIsNull('deleted_at')

whereIsNotNull(string $field)

Add a WHERE field IS NOT NULL condition.

e.g.:

->whereIsNotNull('email')

whereIn(string $field, array $values)

Add a WHERE field IN (values) condition.

e.g.:

->whereIn('status', ['active', 'pending'])

join(string $table, string $on, string $alias = null)

Join another table.

e.g.:

->table('order', 'o')
->join('item', 'o.id = i.orderid', 'i')

leftJoin(string $table, string $on, string $alias = null)

Left Join another table.

rightJoin(string $table, string $on, string $alias = null)

Right Join another table.

crossJoin(string $table, string $alias = null)

Cross Join another table.

joinRelated(string $table), leftJoinRelated(string $table), rightJoinRelated(string $table)

Join a table (or a model class) deriving the ON condition from the registered parentTable relationships. See Auto Discovering Relationship.

distinct()

Add DISTINCT to the select.

groupBy(array $field)

Group by a field or an array of fields.

e.g.:

->groupBy(['field1', 'field2'])

having(string $filter)

Having clause for filtering grouped results.

e.g.:

->having('count(field1) > 10')

Query Object

The Query object extends the QueryBasic object and adds more methods to query the database.

Methods

orderBy(array $field)

Order by a field or an array of fields.

e.g.:

->orderBy(['field1', 'field2'])

limit(int $start, int $limit)

Return $limit records starting at the zero-based offset $start. The second argument is the number of records, not an end position.

e.g.:

->limit(10, 20)                   // 20 records, starting at the 11th
->limit(start: 10, limit: 20)     // the same, with named arguments

top(int $top)

Get the first N records.

e.g.:

->top(10)

forUpdate()

Add FOR UPDATE to the select, locking the rows read.

Union Object

The Union object is used to combine two queries. Since the Union operation is a set operation, the queries must have the same fields and the order, group by, and limit must be defined by the Union object.

e.g.

$query1 = \ByJG\MicroOrm\Query::getInstance()
    ->table('users')
    ->fields(['id', 'name'])
    ->where('name like :part', ['part' => 'A%']);

$query2 = \ByJG\MicroOrm\Query::getInstance()
    ->table('customers')
    ->fields(['id', 'name'])
    ->where('name like :part', ['part' => 'A%']);

$union = \ByJG\MicroOrm\Union::getInstance()
    ->addQuery($query1)
    ->addQuery($query2)
    ->orderBy(['name'])
    ->limit(10, 20);