Greg ORM

A lightweight but powerful ORM(Object-Relational Mapping) library for PHP.

Gest Started with establishing a Database Connection, create an Active Record Model of a database table and write your first queries using the Query Builder.

Why use Greg ORM?

You can read about it in the next article: pending

Get Started

• Requirements
• Installation
• Supported Drivers
• Database Connection - Quick Start
• Query Builder - Quick Start
• Active Record Model - Quick Start

Requirements

• PHP Version ^7.1

Installation

You can add this library as a local, per-project dependency to your project using Composer:

composer require greg-md/php-orm

Supported Drivers

• MySQL
• SQLite

In progress:

• MS SQL
• PostgreSQL
• Oracle

Database Connection - Quick Start

There are two ways of creating a database connection:

1. Instantiate a database connection for a specific driver;
2. Instantiate a connection manager to store multiple database connections.

The connection manager implements the same connection strategy. This means that you can define a connection to act like it.

In the next example we will use a connection manager to store multiple connections of different drivers.

// Instantiate a Connection Manager$manager = new \Greg\Orm\Connection\ConnectionManager();
// Register a MySQL connection$manager->register('mysql_connection', function() {
returnnew \Greg\Orm\Connection\MysqlConnection(
new \Greg\Orm\Connection\Pdo('mysql:dbname=example_db;host=127.0.0.1', 'john', 'doe')
);
});
// Register a SQLite connection$manager->register('sqlite_connection', function() {
returnnew \Greg\Orm\Connection\SqliteConnection(
new \Greg\Orm\Connection\Pdo('sqlite:/var/db/example_db.sqlite')
);
});
// Make the manager to act as "mysql_connection"$manager->actAs('mysql_connection');

Now you can work with this manager:

// Fetch a statement from "sqlite_connection"$manager->connection('sqlite_connection')
->select()
->from('Table')
->fetchAll();
// Fetch a statement from mysql_connection, which is used by default$manager
->select()
->from('Table')
->fetchAll();

Full documentation can be found here.

Active Record Model - Quick Start

The Active Record Model represents a table schema, an entity or a collection of entities of that table, integrated with the Query Builder to speed up your coding process.

Let's say you have Users table:

CREATETABLE `Users` (
`Id`INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`Email`VARCHAR(255) NOT NULL,
`Password`VARCHAR(32) NOT NULL,
`SSN`VARCHAR(32) NULL,
`FirstName`VARCHAR(50) NULL,
`LastName`VARCHAR(50) NULL,
`Active`TINYINT(1) UNSIGNED NOT NULL DEFAULT '1',
PRIMARY KEY (`Id`),
UNIQUE (`Email`),
UNIQUE (`SSN`),
KEY (`Password`),
KEY (`FirstName`),
KEY (`LastName`),
KEY (`Active`)
);

Let's create the model for that table and configure it:

class UsersModel extends \Greg\Orm\Model
{
// Define table alias. (optional)protected$alias = 'u';
// Cast columns. (optional)protected$casts = [
'Active' => 'boolean',
];
// Table name (required)publicfunctionname(): string
{
return'Users';
}
// Create abstract attribute "FullName". (optional)publicfunctiongetFullNameAttribute(): string
{
returnimplode('', array_filter([$this['FirstName'], $this['LastName']]));
}
// Change "SSN" attribute. (optional)publicfunctiongetSSNAttribute(): string
{
// Display only last 3 digits of the SSN.returnstr_repeat('*', 6) . substr($this['SSN'], -3, 3);
}
// Extend SQL Builder. (optional)publicfunctionwhereIsNoFullName()
{
$this->whereIsNull('FirstName')->whereIsNull('LastName');
return$this;
}
}

Now, let's instantiate that model. The only thing you need is a Database Connection:

// Initialize the model.$usersModel = newUsersModel($connection);

Working with table schema

// Display table name.print_r($usersModel->name()); // result: Users// Display auto-increment column.print_r($usersModel->autoIncrement()); // result: Id// Display primary keys.print_r($usersModel->primary()); // result: ['Id']// Display all unique keys.print_r($usersModel->unique()); // result: [['Email'], ['SSN']]

Working with a single row

// Create a user.$user = $usersModel->create([
'Email' => 'john@doe.com',
'Password' => password_hash('secret'),
'SSN' => '123456789',
'FirstName' => 'John',
'LastName' => 'Doe',
]);
// Display user email.print_r($user['Email']); // result: john@doe.com// Display user full name.print_r($user['FullName']); // result: John Doeprint_r($user['SSN']); // result: ******789// Display if user is active.print_r($user['Active']); // result: true// Display user's primary keys.print_r($user->getPrimary()); // result: ['Id' => 1]

Working with a row set

// Create some users.$usersModel->create([
'Email' => 'john@doe.com',
'Password' => password_hash('secret'),
'Active' => true,
]);
$usersModel->create([
'Email' => 'matt@damon.com',
'Password' => password_hash('secret'),
'Active' => false,
]);
$usersModel->create([
'Email' => 'josh@barro.com',
'Password' => password_hash('secret'),
'Active' => false,
]);
// Fetch all inactive users from database.$inactiveUsers = $usersModel->whereIsNot('Active')->fetchAll();
// Display users count.print_r($inactiveUsers->count()); // result: 2// Display users emails.print_r($inactiveUsers->get('Email')); // result: ['matt@damon.com', 'josh@barro.com']// Activate all users in the row set.$inactiveUsers->set('Active', true)->save();
print_r($inactiveUsers[0]['Active']); // result: trueprint_r($inactiveUsers[1]['Active']); // result: true

Working with Query Builder

Select users that doesn't have first and last names.

$users = $usersModel
->whereIsNoFullName()
->orderAsc('Id')
->fetchAll();

Update an user:

$usersModel
->where('Id', 10)
->update(['Email' => 'foo@bar.com']);

Full documentation can be found here.

Query Builder - Quick Start

The Query Builder provides an elegant way of creating SQL statements and clauses on different levels of complexity.

You can easily instantiate a Query Builder with a Database Connection.

Let's say you have Students table.

Find students names that lives in Chisinau and were born in 1990:

$students = $connection->select()
->columns('Id', 'Name')
->from('Students')
->where('City', 'Chisinau')
->whereYear('Birthday', 1990)
->fetchAll();

Update the grade of a student:

$connection->update()
->table('Students')
->set('Grade', 1400)
->where('Id', 10)
->execute();

Delete students that were not admitted in the current year:

$connection->delete()
->from('Students')
->whereIsNot('Admitted')
->execute();

Add a new student:

$query = $connection->insert()
->into('Students')
->data(['Name' => 'John Doe', 'Year' => 2017])
->execute();

Full documentation can be found here.

Documentation

• Database Connection
• Active Record Model
• Query Builder
• Migrations are under construction, but you can use Phinx in the meantime.

License

MIT © Grigorii Duca

Huuuge Quote