README

The necessary thing to work with a database

Impressive ORM based on Active Record.

Philosophy

In software development, I use the rule: everything is perfectly simple, and always looking for nonsense options for solving tasks. Impressive ORM is not exception. It is extremely simple. Where other ORM consist of dozens of classes with complex hierarchies of inheritance, there are only a few Impressive ORM that can be counted on fingers. In my opinion, this is enough for many real programs. Be realistic: most of us do not create Google, Instagram, Facebook. We work on small, medium-sized projects, with an emphasis on simplicity and rapid development than on endless flexibility and function.

Installation

This library is available through gitlab.otakoyi.com at this moment. In future it will be moved to github and parked in Packagist.

Setup and configuration

First, require vendor/autoload.php

Then create instance of Connection and setup it. This is used by PDO to connect to your database. For more information, please see the PDO documentation.

 <?php
  
  $con = new \Impressive\ORM\Connection('mysql:host=localhost;dbname=orm');
  $con->set('username', 'root');
  $con->set('password', '****');
  $con->set('driver_options', [PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"]);
  
  // return instance of Collection
  $con->set('return_collections', true);
  
  // enable log for debugging
  $con->set('logging', true);
  
  // add connection 
  \Impressive\ORM\DB::addConnection($con);

You can put in config more than PDO options.

For example: $con->set('some_key', 'some value');

You can add more than one connection.

Example:

 <?php

  $con = new \Impressive\ORM\Connection('mysql:host=localhost;dbname=master');
  $con->set('username', 'user');
  $con->set('password', '****');
  $con->set('driver_options', [PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"]);
  
  // return instance of Collection
  $con->set('return_collections', true);
  
  // enable log for debugging
  $con->set('logging', true);
  
  // add connection 
  \Impressive\ORM\DB::addConnection($con);
  
  $con = new \Impressive\ORM\Connection('mysql:host=localhost;dbname=slave');
  $con->set('username', 'user');
  $con->set('password', '****');
  $con->set('driver_options', [PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"]);
  
  // return instance of Collection
  $con->set('return_collections', true);
  
  // enable log for debugging
  $con->set('logging', true);
  
  // add connection 
  \Impressive\ORM\DB::addConnection($con, 'slave');

Collections

$con->set('return_collections', true);

Collections of results can be returned as an array (default) or as a collections.

It is recommended that you setup your projects to use collections as they are more flexible.

PDO Driver Options

As you saw, you can set driver options. Because some databases allow set options. For more information, see the PDO documentation.

For example, to force the MySQL driver to use UTF-8 for the connection:

$con->set('driver_options', [PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"]);

PDO Error Mode

Option: error_mode

This can be used to set the PDO::ATTR_ERRMODE setting on the database connection class used by Impressive ORM. It should be passed one of the class constants defined by PDO.

For example:

$con->set('error_mode', PDO::ERRMODE_WARNING);

The default setting is PDO::ERRMODE_EXCEPTION. For full details of the error modes available, see the PDO set attribute documentation.

PDO object access

After a statement has been executed by any means, such as ::save() or ::rawExecute(), the PDOStatement instance used may be accessed via DB::getLastStatement(). This may be useful in order to access PDOStatement::errorCode(), if PDO exceptions are turned off, or to access the PDOStatement::rowCount() method, which returns differing results based on the underlying database. For more information, see the PDOStatement documentation.

Identifier quote character

Option: identifier_quote_character

Set the character used to quote identifiers (eg table name, column name). If this is not set, it will be auto detected based on the database driver being used by PDO.

Column ID

By default, the ORM assumes that all your tables have a primary key column called id. There are two ways to override this: for all tables in the database, or on a per-table basis.

Option: pk

This setting is used to configure the name of the primary key column for all tables. If your column id is called primary_key, use:

$con->set('pk', 'primary_key');

Option: column_overrides

This setting is used to specify the primary key column name for each table separately. It takes an associative array mapping table names to column names. If, for example, your column names include the name of the table, you can use the following configuration:

`<?php $con->set('column_overrides', [

  'user'  => 'user_id',
  'group' => 'group_id',

]); `

Limit clause style

Option: limit_clause_style

You can specify the limit clause style in the configuration. This is to facilitate a MS SQL style limit clause that uses the TOP syntax.

Acceptable values are DB::LIMIT_STYLE_TOP_N and DB::LIMIT_STYLE_LIMIT.

If the PDO driver you are using is one of sqlsrv, dblib or mssql then ORM will automatically select the DB::LIMIT_STYLE_TOP_N for you unless you override the setting.

Query logging

Option: logging

ORM can log all queries it executes. To enable query logging, set the logging option to true (it is false by default).

When query logging is enabled, you can use two static methods to access the log. DB::getLastQuery() returns the most recent query executed. DB::getQueryLog() returns an array of all queries executed. It is very comfortably for debugging.

Query logger

Option: logger

> You must enable logging for this setting to have any effect.

It is possible to supply a callable to this configuration setting, which will be executed for every query that ORM executes. In PHP a callable is anything that can be executed as if it were a function. Most commonly this will take the form of a anonymous function.

This setting is useful if you wish to log queries with an external library as it allows you too whatever you would like from inside the callback function.

`$con->set('logger', function($log_string, $query_time) {

  d($log_string . ' in ' . $query_time);

}); `

Query caching

Option: caching

ORM can cache the queries it executes during a request. To enable query caching, set the caching option to true (it is false by default).

$con->set('caching', true);

Query builder

Impressive ORM provides a fluent interface to enable simple queries to be built without writing a single character of SQL.

All Impressive ORM queries start with a call to the table static method on the DB class. This tells the DB which table to use when making the query.

Note that this method **does not escape its query parameter and so the table name should not be passed directly from user input.

Method calls which add filters and constraints to your query are then strung together. Finally, the chain is finished by calling either findOne() or find(), which executes the query and returns the result.

Let’s start with a simple example.

Say we have a table called users. Here an table structure:

CREATE TABLE users ( id int(10) UNSIGNED NOT NULL, name varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, surname varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, ses_id varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, email varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, password varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, status enum('active','blocked','deleted','hold') COLLATE utf8mb4_unicode_ci NOT NULL, created_at timestamp NULL DEFAULT NULL, updated_at timestamp NULL DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

and little php helpers for debug

function d(... $args)
{
  echo '<pre>'; var_dump(... $args); echo '</pre>';
}

function dd(... $args)
{
  d(... $args); die;
}

Single user

$person = \Impressive\ORM\DB::table('users')->where('name' , 'Oleh')->orderByDesc('name')->findOne(); d($person);

Get all users:

$users = \Impressive\ORM\DB::table('users')->whereEqual('name' , 'Oleh')->orderByDesc('name')->select('id')->selectAs("CONCAT(name ,' ', surname  ) as username")->find();

d(\Impressive\ORM\DB::getLastQuery());

echo "<ul>";

foreach ($users as $user) {
//    d($user);
    echo "<li># $user->id    $user->name</li>";
}

echo "</ul>";

Find user by ID

$person = \Impressive\ORM\DB::table('users')->findOne(11);
d(\Impressive\ORM\DB::getLastQuery());
d($person);

Search users by parameter and update:

$users = \Impressive\ORM\DB::table('users')->limit(10)->offset(11)->where(['role'=>'developer', 'status' => 'active'])->find();

d(\Impressive\ORM\DB::getLastQuery());

foreach ($users as $user) {

    $user->ses_id = '999';
    $user->save();

}

Get results as array:

$users = \Impressive\ORM\DB::table('users')->limit(10)->findArray();
dd($users);

Get a collection:

$users = \Impressive\ORM\DB::table('users')->limit(10)->findCollection();
dd($users);

Using limit:

$users = \Impressive\ORM\DB::table('users')->whereNotLike('name', 'Maks')->limit(10)->findCollection();
dd($users); 

$users = \Impressive\ORM\DB::table('users')->whereIdIs(15)->limit(10)->find();
dd($users);

$users = \Impressive\ORM\DB::table('users')->whereIn('id',[1,2,3,6])->limit(10)->find();
dd($users);

$users = \Impressive\ORM\DB::table('users')->select('id','name')->distinct()->find();
dd($users);

$users = \Impressive\ORM\DB::table('users')
    ->join('user_assoc', "users.id=user_assoc.user_id")
    ->select('users.id','users.name', 'user_assoc.network')
    ->distinct()
    ->limit(3)
    ->find();
dd($users);

Counting results

$users_count = DB::table('person')->count();

Filtering results

Impressive ORM provides a family of methods to extract only records which satisfy some condition or conditions. These methods may be called multiple times to build up your query, and DB fluent interface allows method calls to be chained to create readable and simple-to-understand queries.

Caveats

Only a subset of the available conditions supported by SQL are available when using Impressive ORM. Additionally, all the WHERE clauses will be ANDed together when the query is run. Support for ORing WHERE clauses is not currently present.

These limits are deliberate: these are by far the most commonly used criteria, and by avoiding support for very complex queries, the Impressive ORM codebase can remain small and simple.

Some support for more complex conditions and queries is provided by the whereRaw and rawQuery methods (see below). If you find yourself regularly requiring more functionality than ORM can provide, it may be time to consider using a more full-featured ORM.

### Equality: where, whereEqual, whereNotEqual

By default, calling where with two parameters (the column name and the value) will combine them using an equals operator (=). For example, calling where('name', 'Taras') will result in the clause WHERE name = "Taras".

If your coding style favours clarity over brevity, you may prefer to use the whereEqual method: this is identical to where.

The whereNotEqual method adds a WHERE column != "value" clause to your query.

You can specify multiple columns and their values in the same call. In this case you should pass an associative array as the first parameter. The array notation uses keys as column names.

 $users = DB::table('users')
     ->where(array(
         'name' => 'Taras',
         'email'  => 'some@emai.com'
     ))
 ->find();

Shortcut: `whereIdIs`

This is a simple helper method to query the table by primary key. Respects the ID column specified in the config. If you are using a compound primary key, you must pass an array where the key is the column name. Columns that don’t belong to the key will be ignored.

Shortcut: `whereIdIn`

This helper method is similar to whereIdIs, but it expects an array of primary keys to be selected. It is compound primary keys aware.

Less than / greater than: whereLt, whereGt, whereLte, whereGte There are four methods available for inequalities:

Less than: $users = DB::table('users')->whereLt('age', 10)->find_many();
Greater than: $users = DB::table('users')->whereGt('age', 5)->find_many();
Less than or equal: $users = DB::table('users')->whereLte('age', 10)->find_many();
Greater than or equal: $users = DB::table('users')->whereGte('age', 5)->find_many();

String comImpressive ORMion: `whereLike` and `whereNotLike`

To add a WHERE ... LIKE clause, use:

$users = DB::table('users')->whereLike('name', '%Tar%')->find();

Similarly, to add a WHERE ... NOT LIKE clause, use:

$users = DB::table('users')->whereNotLike('name', '%Tar%')->find();

Multiple OR’ed conditions

You can add simple OR’ed conditions to the same WHERE clause using whereAnyIs. You should specify multiple conditions using an array of items. Each item will be an associative array that contains a multiple conditions.

$users = DB::table('users')
     ->whereAnyIs(
         [    
             ['name' => 'Taras', 'age' => 10],
             ['name' => 'Vitalik', 'age' => 20],
             ['name' => 'Vlad', 'age' => 12]
         ]
     )
     ->find();

Will build:
SELECT * FROM users WHERE (( name = 'Taras' AND age = '10' ) OR ( name = 'Vitalik' AND age > '20' ) OR ( name = 'Vlad' AND age > '12' ));

Set membership: `whereIn` and `whereNotIn`

To add a WHERE ... IN () or WHERE ... NOT IN () clause, use the whereIn and whereNotIn methods respectively.

Both methods accept two arguments. The first is the column name to compare against. The second is an array of possible values. As all the where... methods, you can specify multiple columns using an associative array as the only first parameter.

$users = DB::table('users')->whereIn('name', array('A', 'B', 'C'))->find();

Working with NULL values: `whereNull` and `whereNotNull`

To add a WHERE column IS NULL or WHERE column IS NOT NULL clause, use the whereNull and whereNotNull methods respectively. Both methods accept a single parameter: the column name to test.

Raw WHERE clauses

If you require a more complex query, you can use the whereRaw method to specify the SQL fragment for the WHERE clause exactly. This method takes two arguments: the string to add to the query, and an (optional) array of parameters which will be bound to the string. If parameters are supplied, the string should contain question mark characters (?) to represent the values to be bound, and the parameter array should contain the values to be substituted into the string in the correct order.

This method may be used in a method chain alongside other where methods as well as methods such as offset, limit and orderBy. The contents of the string you supply will be connected with preceding and following WHERE clauses with AND.

`$users = DB::table('users')

        ->where('name', 'Alex')
        ->where_raw('(age = ? OR age = ?)', array(20, 25))
        ->orderByAsc('name')
        ->find();`

// Will creates SQL: SELECT * FROM users WHERE name = "Alex" AND (age = 20 OR age = 25) ORDER BY name ASC;

You must wrap your expression in parentheses when using any of ALL, ANY, BETWEEN, IN, LIKE, OR and SOME. Otherwise the precedence of AND will bind stronger and in the above example you would effectively get WHERE (name = "Alex" AND age = 20) OR age = 25

Note that this method only supports “question mark placeholder” syntax, and NOT “named placeholder” syntax. This is because PDO does not allow queries that contain a mixture of placeholder types. Also, you should ensure that the number of question mark placeholders in the string exactly matches the number of elements in the array.

If you require yet more flexibility, you can manually specify the entire query. See Raw queries below.

Limits and offsets

Note that these methods **do not escape their query parameters and so these should not be passed directly from user input.

The limit and offset methods map pretty closely to their SQL equivalents.

$users = DB::table('users')->where('role', 'admin')->limit(5)->offset(10)->find();

Ordering

Note that these methods **do not escape their query parameters and so these should not be passed directly from user input.

Two methods are provided to add ORDER BY clauses to your query. These are orderByDesc, orderByAsc, orderByRaw and orderBy, each of which takes a column name to sort by. The column names will be quoted.

$users = DB::table('users')->where('role', 'admin')->orderByAsc('name')->limit(5)->offset(10)->find();

Grouping

Note that this method **does not escape it query parameter and so this should not by passed directly from user input.

To add a GROUP BY clause to your query, call the groupBy method, passing in the column name. You can call this method multiple times to add further columns.

$users = DB::table('users')->where('role', 'admin')->groupBy('name')->limit(5)->offset(10)->find();

It is also possible to GROUP BY a database expression:

$users = DB::table('users')->where('role', 'admin')->groupByRaw('raw expression')->limit(5)->offset(10)->find();

Having

When using aggregate functions in combination with a GROUP BY you can use HAVING to filter based on those values.

HAVING works in exactly the same way as all of the where functions in DB. Substitute where for having* to make use of these functions.

$users = DB::table('users')->where('role', 'admin')->havingNotLike('name', 'Petro')->limit(5)->offset(10)->find();

You can use havingEqual, havingNotEqual, havingIdIs, havingLike, havingGt, havingLt, havingGte, havingLte, havingIn, havingNotIn, havingNull, havingNotNull, havingRaw

Result columns

By default, all columns in the SELECT statement are returned from your query. That is, calling:

$users = DB::table('users')->find();

Will return SELECT * FROM users;

To select specify columns, use select() method. You can put in select more than one parameter in select. For example:

$users = DB::table('users')->select('id','name')->find();

Will return SELECT id, name FROM users;

If you want add alias for column, use:

$count = DB::table('users')->selectAs('COUNT(*)', 'count')->find();

Will return SELECT COUNT(*) AS count FROM users;

DISTINCT

To add a DISTINCT keyword before the list of result columns in your query, add a call to distinct() to your query chain.

$users = DB::table('users')->distinct()->select('role')->find();

Joins

Impressive ORM has a family of methods for adding different types of JOINs to the queries it constructs:

Methods: join, innerJoin, leftOuterJoin, rightOuterJoin, fullOuterJoin.

Each of these methods takes the same set of arguments. The following description will use the basic join method as an example, but the same applies to each method.

The first two arguments are mandatory. The first is the name of the table to join, and the second supplies the conditions for the join. The recommended way to specify the conditions is as an array containing three components: the first column, the operator, and the second column. The table and column names will be automatically quoted.

For example:

$results = DB::table('users')->join('users_meta', array('users.id', '=', 'users_meta.user_id'))->find();

It is also possible to specify the condition as a string, which will be inserted as-is into the query. However, in this case the column names will not be escaped, and so this method should be used with caution.

The join methods also take an optional third parameter, which is an alias for the table in the query. This is useful if you wish to join the table to itself to create a hierarchical structure. In this case, it is best combined with the table_alias method, which will add an alias to the main table associated with the ORM, and the select method to control which columns get returned.

`$results = DB::table('users')

  ->tableAlias('u1')
  ->select('u1.*')
  ->select('u2.name', 'parent_name')
  ->join('users', array('u1.parent', '=', 'u2.id'), 'u2')
  ->find();`

Raw JOIN clauses

If you need to construct a more complex query, you can use the rawJoin method to specify the SQL fragment for the JOIN clause exactly. This method takes four required arguments: the string to add to the query, the conditions is as an array containing three components: the first column, the operator, and the second column, the table alias and (optional) the parameters array. If parameters are supplied, the string should contain question mark characters (?) to represent the values to be bound, and the parameter array should contain the values to be substituted into the string in the correct order.

If you require yet more flexibility, you can manually specify the entire query. See Raw queries below.

Aggregate functions

There is support for MIN, AVG, MAX and SUM in addition to COUNT (documented earlier).

To return a minimum value of column, call the min() method.

$users = DB::table('users')->min('age')->find();

The other functions (AVG, MAX and SUM) work in exactly the same manner. Supply a column name to perform the aggregate function on and it will return an integer.

Raw queries

If you need to perform more complex queries, you can completely specify the query to execute by using the rawQuery method. This method takes a string and optionally an array of parameters. The string can contain placeholders, either in question mark or named placeholder syntax, which will be used to bind the parameters to the query.

$people = DB::table('users')->rawQuery('SELECT u.* FROM users u JOIN role r ON u.role_id = r.id WHERE r.name = :role', array('role' => 'traine'))->find();

The DB class instance(s) returned will contain data for all the columns returned by the query. Note that you still must call for_table to bind the instances to a particular table, even though there is nothing to stop you from specifying a completely different table in the query. This is because if you wish to later called save, the DB will need to know which table to update.

Using rawQuery is advanced and possibly dangerous, and DB does not make any attempt to protect you from making errors when using this method.

Raw SQL execution using PDO

By using this function you’re dropping down to PHPs PDO directly. Impressive ORM does not make any attempt to protect you from making errors when using this method.

You’re essentially just using DB to manage the connection and configuration when you implement rawExecute().

It can be handy, in some instances, to make use of the PDO instance underneath ORM to make advanced queries. These can be things like dropping a table from the database that ORM doesn't support and will not support in the future.

This method directly maps to PDOStatement::execute() underneath so please familiarise yourself with it’s documentation.

Dropping tables

This can be done very simply using rawExecute().

DB::rawExecute('DROP TABLE users')

Getting the PDO instance

>By using this function you’re dropping down to PHPs PDO directly. DB does not make any attempt to protect you from making errors when using this method. You’re essentially just using DB to manage the connection and configuration when you implement against getDb().

`$pdo = DB::getDb(); foreach($pdo->query('SHOW TABLES') as $row) {

 var_dump($row);

Objects and CRUD

Getting data from objects

Once you’ve got a set of records (objects) back from a query, you can access properties on those objects (the values stored in the columns in its corresponding table) in two ways: by using the get method, or simply by accessing the property on the object directly:

$user = DB::table('person')->find(5);

// The following two forms are equivalent

$name = $user->get('name'); $name = $user->name;

You can also get the all the data wrapped by an ORM instance using the as_array method. This will return an associative array mapping column names (keys) to their values.

The toArray method takes column names as optional arguments. If one or more of these arguments is supplied, only matching column names will be returned.

$user = DB::table('person')->create();

$user->name = 'Robin';

$user->surname = 'Good';

$user->age = 50;

// Returns array('name' => 'Robin', 'surname' => 'Good', 'age' => 50)

`$data = $user->toArray();`

`// Returns ['name' => 'Robin', 'age' => 50]`

`$data = $user->toArray('name', 'age');`

Updating records

To update the database, change one or more of the properties of the object, then call the save method to commit the changes to the database. Again, you can change the values of the object’s properties either by using the set method or by setting the value of the property directly. By using the set method it is also possible to update multiple properties at once, by passing in an associative array:

$user = DB::table('users')->find(5);

// The following two forms are equivalent
$user->set('name', 'Tom');
$user->age = 20;

// This is equivalent to the above two assignments
$user->set['name' => 'Tom', 'age'  => 20]);

// Synchronise the object with the database
$user->save();

Properties containing expressions

$user = DB::table('users')->find(5);

$user->setExpr('updated_at', 'NOW()');

$user->save();

Creating new records

To add a new record, you need to first create an “empty” object instance. You then set values on the object as normal, and save it.

$user = DB::table('users')->create();

$user->name = 'Alex';

$user->age = 40;

$user->save();

After the object has been saved, you can call its id() method to find the autogenerated primary key value that the database assigned to it.

Properties containing expressions

It is possible to set properties on the model that contain database expressions using the setExpr method.

$user = DB::table('users')->create();

$user->name = 'Alex';

$user->age = 40;

$user->setExpr('created_at', 'NOW()');

$user->save();

The added column’s value will be inserted into query in its raw form therefore allowing the database to execute any functions referenced - such as NOW() in this case.

Checking whether a property has been modified

To check whether a property has been changed since the object was created (or last saved), call the isDirty() method:

$name_has_changed = $user->isDirty('name'); // Returns true or false

Deleting records

To delete an object from the database, simply call its delete method.

$user = DB::table('users')->find(5);
$user->delete();

To delete more than one object from the database, build a query:

DB::table('users')->where('status', 'ban')->deleteMany();

Transactions

DB doesn’t supply any extra methods to deal with transactions, but it’s very easy to use PDO’s built-in methods:

$task =  \Impressive\ORM\DB::table('tasks')->first(50);

if($task){

    \Impressive\ORM\DB::beginTransaction();

    $task->project_id=22;
    $status = $task->save();

    if($status){
        \Impressive\ORM\DB::commit();
    } else {
        \Impressive\ORM\DB::rollback();
    }

}

// OR 

$task =  \Impressive\ORM\DB::table('tasks')->first(50);

if($task){

   $res = \Impressive\ORM\DB::transaction(function() use ($task) {

        $task->project_id=22;

        return $task->save();

   });
}

For more details, see the PDO documentation on Transactions.

Multiple Connections

ORM now works with multiple connections. Most of the static functions work with an optional connection name as an extra parameter.

$con = new \Impressive\ORM\Connection('mysql:host=localhost;dbname=master');
$con->set('username', 'root');
$con->set('password', '****');

...

\Impressive\ORM\DB::addConnection($con);

$con = new \Impressive\ORM\Connection('mysql:host=localhost;dbname=slave');
$con->set('username', 'root');
$con->set('password', '****');

...

\Impressive\ORM\DB::addConnection($con, 'slave');

$user = \Impressive\ORM\DB::table('users')->first('1');
d($user->name);

$user = \Impressive\ORM\DB::table('users', 'slave')->first('1');

// $user->name .= time();
// $user->save();

d($user->name);

For the \Impressive\ORM\DB::addConnection($con); method, this means that when passing connection strings for a new connection, the second parameter, which is typically omitted, should be null. In all cases, if a connection name is not provided, it defaults to DB::DEFAULT_CONNECTION

Supported Methods

In each of these cases, the $connection_name parameter is optional, and is an arbitrary key identifying the named connection.

DB::table($table_name, $connection_name)
DB::setDb($pdo, $connection_name)
DB::getDb($connection_name)
DB::rawExecute($query, $parameters, $connection_name)
DB::getLast_query($connection_name)
DB::getQueryLog($connection_name)

Of these methods, only DB::getLastQuery($connection_name) does not fallback to the default connection when no connection name is passed. Instead, passing no connection name (or null) returns the most recent query on any connection.

Active Record implementation

Model classes

You should create a model class for each entity in your application. For example, if you are building an application that requires users, you should create a User class. Your model classes should extend the base Model class:

class User extends Model {}

Impressive ORM takes care of creating instances of your model classes, and populating them with data from the database. You can then add behaviour to this class in the form of public methods which implement your application logic. This combination of data and behaviour is the essence of the Active Record pattern.

Lets see example:

 class User extends \Impressive\ORM\Model
 {
    public function tasks()
    {
        return $this->hasMany(Task::class, 'user_id');
    }

    public function projects()
    {
        return $this->hasMany(Project::class, 'user_id');
    }

     public function comments()
     {
         return $this->hasMany(Comments::class, 'user_id');
     }
 }
 

 class Project extends \Impressive\ORM\Model
 {
    protected $table = 'project';

    public function owner()
    {
        return $this->belongsTo(User::class, 'user_id');
    }

    public function team()
    {
        return $this->hasManyThrough(User::class, ProjectTeam::class, 'project_id', 'user_id');
    }
 }


 class ProjectTeam extends \Impressive\ORM\Model
 {
    protected $table = 'project_teams';
 }


 class Comments extends \Impressive\ORM\Model
 {
     protected $table = 'task_comments';
 }


 class Task extends \Impressive\ORM\Model
 {
     public function user()
     {
         return $this->belongsTo(User::class, 'user_id');
     }

     public function manager()
     {
         return $this->belongsTo(User::class, 'manager_id');
     }

     public function project()
     {
         return $this->belongsTo(Project::class, 'project_id');
     }

     public function comments()
     {
         return $this->hasMany(Comments::class, 'task_id');
     }
 }
 

// $project = Project::first(20);
//
// foreach ($project->team as $team) {
//    print_r($team);
// }
//
// d(\Impressive\ORM\DB::getQueryLog());


$tasks = Task::where('status', 'new')->limit(10)->find();
// dd($tasks);
?>
    <ul>
        <?php foreach ($tasks as $task) : ?>
            <li>
                #<?= $task->id ?> <?= $task->name ?>
                <span>Project: <?= $task->project->name ?></span>
                <span>Manager: <?= $task->manager->name ?> <?= $task->manager->surname ?></span>
                <span>User: <?= $task->user->name ?> <?= $task->user->surname ?></span>
                <span>Comments: <?= $task->comments->count() ?></span>
            </li>
        <?php endforeach; ?>
    </ul>

<?php
d(\Impressive\ORM\DB::getQueryLog());

Lazy loading

 $users = User::find();
  foreach($users as $result){
      echo $result->profile->img;
  }

Notice that if there is no result for profile on the above example it will throw a Notice: Trying to get property of non-object... Note: Maybe worth the effort to create a NULL object for this use case and others.

IDE Auto-complete

As Impressive ORM does not require you to specify a method/function per database column it can be difficult to know what properties are available on a particular model. Due to the magic nature of PHP’s __get()_ method it is impossible for an IDE to give you auto complete hints as well.

/**
 * @param int $id
 * @param string $name
 * @param string $surname
 * @param string $email
 * ...
 */
class User extends Model {}

Database tables

Your User class should have a corresponding user table in your database to store its data.

By default, Impressive ORM assumes your class names are in CapWords style, and your table names are in lowercase_with_underscores style. It will convert between the two automatically. For example, if your class is called UsersGroups, Impressive ORM will look for a table named users_groups.

If you are using namespaces then they will be converted to a table name in a similar way. For example \Users\Meta would be converted to users_meta. Note here that backslashes are replaced with underscores in addition to the CapWords replacement discussed in the previous paragraph.

To override the default naming behaviour and directly specify a table name, add a public property to your class called $table:

class User extends Model
{
    public $table = 'custom_users_table';
}

Primary key

Impressive ORM requires that your database tables have a unique primary key column. By default, Impressive ORM will use a column called id. To override this default behaviour, add a public property to your class called $pk:

class User extends Model
{
    public $pk = 'my_primary_key'
}

Associations

Impressive ORM provides a simple API for one-to-one, one-to-many and many-to-many relationships (associations) between models. It takes a different approach to many other ORMs, which use associative arrays to add configuration metadata about relationships to model classes. These arrays can often be deeply nested and complex, and are therefore quite error-prone.

Instead, Impressive ORM treats the act of querying across a relationship as a behaviour, and supplies a family of helper methods to help generate such queries. These helper methods should be called from within methods on your model classes which are named to describe the relationship. These methods return ORM instances (rather than actual Model instances) and so, if necessary, the relationship query can be modified and added to before it is run.

Summary

The following list summarises the associations provided by Impressive ORM, and explains which helper method supports each type of association:

One-to-one

Use hasOne in the base, and belongsTo in the associated model.

One-to-many

Use hasMany in the base, and belongsTo in the associated model.

Many-to-many

Use hasManyThrough in both the base and associated models.

Below, each association helper method is discussed in detail.

Has-one

One-to-one relationships are implemented using the has_one method. For example, say we have a User model. Each user has a single Profile, and so the user table should be associated with the profile table. To be able to find the profile for a particular user, we should add a method called profile to the User class (note that the method name here is arbitrary, but should describe the relationship). This method calls the protected hasOne method provided by Impressive ORM, passing in the class name of the related object. The profile method should return an ORM instance ready for (optional) further filtering.

class Profile extends Model {}
  
class User extends Model
{
    public function profile()
    {
        return $this->hasOne(Profile::class);
    }
}

The API for this method works as follows:

// Select a particular user from the database
$user = DB::factory('User')->findOne($user_id);

// Find the profile associated with the user
$profile = $user->profile()->findOne();

By default, Impressive ORM assumes that the foreign key column on the related table has the same name as the current (base) table, with _id appended. In the example above, Impressive ORM will look for a foreign key column called user_id on the table used by the Profile class. To override this behaviour, add a second argument to your has_one call, passing the name of the column to use.

In addition, Impressive ORM assumes that the foreign key column in the current (base) table is the primary key column of the base table. In the example above, Impressive ORM will use the column called user_id (assuming user_id is the primary key for the user table) in the base table (in this case the user table) as the foreign key column in the base table. To override this behaviour, add a third argument to your hasOne call, passing the name of the column you intend to use as the foreign key column in the base table.

Has many

One-to-many relationships are implemented using the hasMany method. For example, say we have a User model. Each user has several Meta objects. The user table should be associated with the post table. To be able to find the posts for a particular user, we should add a method called posts to the User class (note that the method name here is arbitrary, but should describe the relationship). This method calls the protected hasMany method provided by Impressive ORM, passing in the class name of the related objects. Pass the model class name literally, not a pluralised version. The posts method should return an ORM instance ready for (optional) further filtering.

class UserMeta extends Model {}

class User extends Model
{
    public function posts()
    {
        return $this->hasMany(UserMeta::class);
    }
}

The API for this method works as follows:

// Select a particular user from the database
$user = DB::factory('User')->findOne($user_id);

// Find the posts associated with the user
$posts = $user->posts()->find();

By default, Impressive ORM assumes that the foreign key column on the related table has the same name as the current (base) table, with _id appended. In the example above, Impressive ORM will look for a foreign key column called user_id on the table used by the UserMeta class. To override this behaviour, add a second argument to your hasMany call, passing the name of the column to use.

In addition, Impressive ORM assumes that the foreign key column in the current (base) table is the primary key column of the base table. In the example above, Impressive ORM will use the column called user_id (assuming user_id is the primary key for the user table) in the base table (in this case the user table) as the foreign key column in the base table. To override this behaviour, add a third argument to your has_many call, passing the name of the column you intend to use as the foreign key column in the base table.

Belongs to

The ‘other side’ of hasOne and hasMany is belongsTo. This method call takes identical parameters as these methods, but assumes the foreign key is on the current (base) table, not the related table.

class Profile extends Model
{
    public function user()
    {
        return $this->belongsTo('User');
    }
}

class User extends Model {}

The API for this method works as follows:

// Select a particular profile from the database
$profile = DB::factory('Profile')->findOne($profile_id);

// Find the user associated with the profile
$user = $profile->user()->findOne();

Again, Impressive ORM makes an assumption that the foreign key on the current (base) table has the same name as the related table with _id appended. In the example above, Impressive ORM will look for a column named user_id. To override this behaviour, pass a second argument to the belongsTo method, specifying the name of the column on the current (base) table to use.

Impressive ORM also makes an assumption that the foreign key in the associated (related) table is the primary key column of the related table. In the example above, Impressive ORM will look for a column named user_id in the user table (the related table in this example). To override this behaviour, pass a third argument to the belongs_to method, specifying the name of the column in the related table to use as the foreign key column in the related table.

Has many through

Many-to-many relationships are implemented using the hasManyThrough method. This method has only one required argument: the name of the related model. Supplying further arguments allows us to override default behaviour of the method.

For example, say we have a Book model. Each Book may have several Author objects, and each Author may have written several Books. To be able to find the authors for a particular book, we should first create an intermediary model. The name for this model should be constructed by concatenating the names of the two related classes, in alphabetical order. In this case, our classes are called Author and Book, so the intermediate model should be called AuthorBook.

We should then add a method called authors to the Book class (note that the method name here is arbitrary, but should describe the relationship). This method calls the protected hasManyThrough method provided by Impressive ORM, passing in the class name of the related objects. Pass the model class name literally, not a pluralised version. The authors method should return an ORM instance ready for (optional) further filtering.

class Author extends Model
{
    public function books()
    {
        return $this->hasManyThrough('Book');
    }
}

class Book extends Model
{
    public function authors()
    { 
        return $this->hasManyThrough('Author');
    }
}

class AuthorBook extends Model {}

The API for this method works as follows:

// Select a particular book from the database
$book = DB::factory('Book')->findOne($book_id);

// Find the authors associated with the book
$authors = $book->authors()->find();

// Get the first author
$first_author = $authors[0];

// Find all the books written by this author
$first_author_books = $first_author->books()->find();

Overriding defaults

The hasManyThrough method takes up to six arguments, which allow us to progressively override default assumptions made by the method.

First argument: associated model name - this is mandatory and should be the name of the model we wish to select across the association.

Second argument: intermediate model name - this is optional and defaults to the names of the two associated models, sorted alphabetically and concatenated.

Third argument: custom key to base table on intermediate table - this is optional, and defaults to the name of the base table with _id appended.

Fourth argument: custom key to associated table on intermediate table - this is optional, and defaults to the name of the associated table with _id appended.

Fifth argument: foreign key column in the base table - this is optional, and defaults to the name of the primary key column in the base table.

Sixth argument: foreign key column in the associated table - this is optional, and defaults to the name of the primary key column in the associated table.

Filters

It is often desirable to create reusable queries that can be used to extract particular subsets of data without repeating large sections of code. Impressive ORM allows this by providing a method called filter which can be chained in queries alongside the existing DB query API. The filter method takes the name of a public static method on the current Model subclass as an argument. The supplied method will be called at the point in the chain where filter is called, and will be passed the ORM object as the first parameter. It should return the ORM object after calling one or more query methods on it. The method chain can then be continued if necessary.

It is easiest to illustrate this with an example. Imagine an application in which users can be assigned a role, which controls their access to certain pieces of functionality. In this situation, you may often wish to retrieve a list of users with the role ‘admin’. To do this, add a static method called (for example) admins to your Model class:

class User extends Model
{
    public static function admins($builder)
    {
        return $builder->where('role', 'admin');
    }
}

You can then use this filter in your queries:

$admin_users = DB::factory('User')->filter('admins')->find();

You can also chain it with other methods as normal:

$admins = 
    DB::factory('User')
    ->filter('admins')
    ->whereLt('age', 18)
    ->find();

Filters with arguments

You can also pass arguments to custom filters. Any additional arguments passed to the filter method (after the name of the filter to apply) will be passed through to your custom filter as additional arguments (after the DB instance).

For example, let’s say you wish to generalise your role filter (see above) to allow you to retrieve users with any role. You can pass the role name to the filter as an argument:

class User extends Model
{
    public static function hasRole($builder, $role)
    {
        return $builder->where('role', $role);
    }
}

$admins = DB::factory('User')->filter('has_role', 'admin')->find();
$guests = DB::factory('User')->filter('has_role', 'guest')->find();

Multiple Connections

Impressive ORM works with multiple database connections (and necessarily relies on an updated version of DB that also supports multiple connections). Database connections are identified by a string name, and default to Wrapper::DEFAULT_CONNECTION (which is really ORM::DEFAULT_CONNECTION).

class MyClass extends Model
{
    public $connection_name = 'custom';
}

impressive / orm

Maintainers

Details

README

Philosophy

Installation

Setup and configuration

Collections

PDO Driver Options

PDO Error Mode

PDO object access

Identifier quote character

Column ID

Limit clause style

Query logging

Query logger

Query caching

Query builder

Counting results

Filtering results

Caveats

Shortcut: whereIdIs

Shortcut: whereIdIn

String comImpressive ORMion: whereLike and whereNotLike

Multiple OR’ed conditions

Set membership: whereIn and whereNotIn

Working with NULL values: whereNull and whereNotNull

Raw WHERE clauses

Limits and offsets

Ordering

Grouping

Having

Result columns

DISTINCT

Joins

Raw JOIN clauses

Aggregate functions

Raw queries

Raw SQL execution using PDO

Dropping tables

Getting the PDO instance

Objects and CRUD

Getting data from objects

Updating records

Properties containing expressions

Creating new records

Properties containing expressions

Checking whether a property has been modified

Deleting records

Transactions

Multiple Connections

Supported Methods

Active Record implementation

Model classes

Lazy loading

IDE Auto-complete

Database tables

Primary key

Associations

Summary

One-to-one

One-to-many

Many-to-many

Has-one

Has many

Belongs to

Has many through

Overriding defaults

Filters

Filters with arguments

Multiple Connections

Shortcut: `whereIdIs`

Shortcut: `whereIdIn`

String comImpressive ORMion: `whereLike` and `whereNotLike`

Set membership: `whereIn` and `whereNotIn`

Working with NULL values: `whereNull` and `whereNotNull`