Download

Introduction Last updated: 04/10/2020

DML is short name of Data Manipulation Language
which deals with data manipulation and includes most common SQL statements such :
SELECT, INSERT, UPDATE, DELETE, etc.,
and it is used to store, modify, retrieve, delete and update data in a database.

SELECT

Select methods uses to retrieve data from a database

Methods :


Method Description
select Retrieve all data from a database table.
first Retrieve single record from database table.
find Retrieve single record from database table with condition by id. (WHERE id=1)
findBy Retrieve records from database table with condition by custom column.
paginate Paginate retrieved data from a database table.
query Generate your owen Query, Type SQL Command.



select

Definition :

public function select($fields = ['*'], $last = false)


Parameters :

$fields optional Columns to select , by default get all columns.
$last optional Order retrieved data from last to start using id, by default false


Usage :

$model = new MyModel();

$allData = $model->select();


Get Specified Columns :

$model = new MyModel();

$allData = $model->select(['columns_1', 'columns_2']);


Order Data From Last Record to First :

$model = new MyModel();

$allData = $model->select(['*'], true);

if you send boolean true value to first argument
the method will know that you want all columns but ordering from last to start 

$model = new MyModel();

$allData = $model->select(true);
// its mean
//$allData = $model->select(['*'] ,true);



Adding Additional Constraints :

$model = new MyModel();

$allData = $model->where('column_table', 'operation', 'value')
                 ->limit($recordsNumber)
                 ->select(['columns_1', 'columns_2']);



first

Definition :

public function first()


Parameters :

$fields optional Columns to select , by default get all columns.
$last optional Order retrieved data from last to start using id, by default false


Usage :

$model = new MyModel();

$singleRecord = $model->first();


Get Specified Columns :

$model = new MyModel();

$singleRecord = $model->first(['columns_1', 'columns_2']);


Order Data From Last Record to First :

$model = new MyModel();

$singleRecord = $model->first(['*'], true);

if you send boolean true value to first argument
the method will know that you want all columns but ordering from last to start 

$model = new MyModel();

$allData = $model->first(true);
// its mean
//$allData = $model->first(['*'] ,true);



Adding Additional Constraints :

$model = new MyModel();

$singleRecord = $model->where('column_table', 'operation', 'value')
                 ->first(['columns_1', 'columns_2']);



find

Definition :

public function find($id)


Parameters :

$id required id value.


Usage :

$model = new MyModel();

$find = $model->find($id);

find method return single record



findBy

Definition :

public function findBy($column, $value)


Parameters :

$column required Column Name
$value required Column Value.


Usage :

$model = new MyModel();

$findBy = $model->findBy('username', $usernameValue);
$findBy->select();
$findBy->first();

findBy method return Model Class



paginate

Definition :

public function paginate($recordsCount = 0, $last = false)


Parameters :

$recordsCount optional records count in page. by default value is coming from configuration
$last optional Order retrieved data from last to start using id, by default false


Usage :

$model = new MyModel();

$data = $model->pagenate();


Order Data From Last Record to First :

$model = new MyModel();

$allData = $model->pagenate($recordsCount, true);

if you send boolean true value to first argument
the method will know that you want all columns but ordering from last to start 

$model = new MyModel();

$allData = $model->paginate(true);



Adding Additional Constraints :

$model = new MyModel();

$allData = $model->where('column_table', 'operation', 'value')
                 ->orWhere('column_table', 'operation', 'value')
                 ->limit($recordsNumber)
                 ->paginate();

Check out this page to learn how you can create pagination with pages numbers links with 3 lines of code




query

Definition :

protected function query($sql, $params = [])

query method is protected scope by default
its for advanced usage, you can change scope to public if you want.
you must know SQL Commands.

query method not for retrieve data only!
you can use it for any SQL Command (DDL, DML, DCL, TCL)
that mean you can use it to achieve unsupported things in PHPtricks-ORM Library Like (Create Database Users)


Parameters :

$sql required SQL Command.
$params optional Parameters to bind in Command


Usage :

$model = new MyModel();

$data = $model->query($sqlStatement);

$model = new MyModel();

$data = $model->query("SELECT * FROM users WHERE active=:active", ['active' => 0]);
var_dump($data->select());

$model = new MyModel();

$model->query("CREATE TABLE MyTable (
               id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
               firstname VARCHAR(30) NOT NULL,
               lastname VARCHAR(30) NOT NULL,
               email VARCHAR(50),
               reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP");

$model = new MyModel();

$model->query("CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password'");
$model->query("GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost'");

take care with GRANT PRIVILEGES to users, be safe.

Where Clauses

You may use the where method on a query builder to add where clauses to the query.
The most basic call to where requires three arguments.

Definition :

public function where($field, $operator, $value = false)

Parameters :

$field required the name of the column.
$operator required where Clause operator.
$value optional value to evaluate against the column. 
class PostModel extends Models
{
    protected $_table = 'posts';
    
    public function getDraftPosts()
    {
        return $this->where('status', '=', 'draft')
                    ->select();
    }   
}

$users = new UserModel();
$inActiveUsers = $users->where('active', '=', 0)->select();


For convenience, if you want to verify that a column is equal to a given value, you may pass the value directly as the second argument to the where method: 

class PostModel extends Models
{
    protected $_table = 'posts';
    
    public function getDraftPosts()
    {
        return $this->where('status', 'draft')
                    ->select();
    }   
}

$users = new UserModel();
$inActiveUsers = $users->where('active', 0)->select();


where types :

Method Description
where Simple Where Clause
orWhere Or Statements
whereBetween verifies that a column's value is between two values
likeWhere verifies that a column's value is like value
in verifies that a given column's value is contained within the given array
notIn verifies that the given column's value is not contained in the given array
ParseWhere advanced where clauses

orWhere

You may chain where constraints together as well as add or clauses to the query.
The orWhere method accepts the same arguments as the where method:

Definition :

public function orWhere($field, $operator, $value = false)


Parameters :

$field required the name of the column.
$operator required where Clause operator.
$value optional value to evaluate against the column. 
$model->where('active', 1)
      ->orWhere('username', 'not-active')
      ->select();



whereBetween

The whereBetween method verifies that a column's value is between two values:

Definition :

public function whereBetween($field, array $values = [])


Parameters :

$field required the name of the column.
$values required values [from , to] 
$model->whereBetween('created_at', ['10-12-2019', '1-10-2020'])
      ->select();



likeWhere

The likeWhere method verifies that a column's value is like a value:

Definition :

public function likeWhere($field, string $value)


Parameters :

$field required the name of the column.
$value required column value 
$model->likeWhere('username', 'mohammad')
      ->select();

// SELECT * FROM table WHERE username LIKE '%mohammad%'



in / notIn

The in method verifies that a given column's value is contained within the given array,

The notIn method verifies that a given column's value is NOT contained within the given array.

Definition :

public function in($field, array $values = [])
public function notIn($field, array $values = [])


Parameters :

$field required the name of the column.
$values required column values 
$contains = $model->in('id', [1, 2, 3, 4]);

$notContains = $model->notIn('id', [1, 2, 3, 4]);



ParseWhere

Sometimes you may need to create more advanced where clauses such as nested parameter groupings.

This method will be overwritten in the next release (v5.1.0)

Definition :

public function parseWhere(array $cons, $type = "AND")


Parameters :

$cons required conditions you want to nested
$type optional where clause type [AND, OR] by default AND 
$con = [
    [
        'age', '<', '30'
    ],
    'OR' => [
        'sex', '=', 'female'
    ],
    'AND' => [
        'position', '=', 'manager'
    ]
];

// ---

$model->where('username', 'ALI')
    ->parseWhere($con)->select();
    
// SELECT * FROM table_name where username='ALI' AND (age<30 OR sex='female AND position='manager')




$model->where('username', 'ALI')
          ->parseWhere($con, 'OR')->select();
          
// SELECT * FROM table_name where username='ALI' OR (age<30 OR sex='female' AND position='manager')

Ordering, Finding By, Limit & Offset

orderBy

The orderBy method allows you to sort the result of the query by a given column.

Definition :

public function orderBy($colName, string $type = 'ASC')


Parameters :

$colName required column you wish to sort by,.
$type required direction of the sort may be either ASC or DESC. default ASC

$type argument can be UPPERCASE or lowercase.


Usage :

$users = $userModel->orderBy('username', 'desc')->select();



groupBy

The groupBy method allows you to group the result of the query by a given column.

Definition :

public function groupBy(string $colName)


Parameters :

$colName required column you wish to group by,.


Usage :

$users = $userModel->groupBy('username')
                   ->orderBy('username', 'desc')->select(['COUNT(id)']);



limit

The limit method to limit the number of results returned from the query.

Definition :

public function limit($limit)


Parameters :

$limit required returned records count limit


Usage :

$users = $userModel->limit(15)->select();

$users = $userModel->likeWhere('username', 'mohammad')
                   ->limit(10)
                   ->select();



offset

The offset method to skip a given number of results in the query.

Definition :

public function offset($limit)


Parameters :

$offset required skipped records number


Usage :

$users = $userModel->offset(15)->select();

$users = $userModel->likeWhere('username', 'mohammad')
                   ->offset(15)
                   ->limit(10)
                   ->select();

Inserting, Updating & Delete

Inserts :

insert

PHPtricks-ORM provides an insert method for inserting records into the database table. The insert method accepts an array of column names and values:

Definition :

public function insert(array $values = [])


Parameters :

$values required array of column names and values


Usage :

$userModel->insert([
              'username' => 'mohammad',
              'email'    => 'mohammad@email.com',
              'password' => 'secret',
          ]);



lastInsertedId

PHPtricks-ORM allows you to get last inserted id.

Definition :

public function lastInsertedId()


Usage :

$userModel->insert($dataArrayToInsert);

echo $userModel->lastInsertedId();



createOrUpdate

createOrUpdate allows you to update if record exists, or insert if not.

Definition :

public function createOrUpdate($values, $conditionColumn = [])


Parameters :

$values required array of column names and values
$conditionColumn optional condition for update,

$conditionColumn is not required, so if you keep it empty the method will search on table by id.


Usage :

$userModel->createOrUpdate([
              'username' => 'AL-Anzawi',
              'email'    => 'mohammad@email.com',
              'password' => 'secret',
          ], ['username', 'mohammad']);



Updates :

Of course, in addition to inserting records into the database, PHPtricks-ORM can also update existing records using the update method.
The update method, like the insert method, accepts an array of column and value pairs containing the columns to be updated.
You may constrain the update query using where clauses:


update

Definition :

public function update($values = [])


Parameters :

$values required array of column names and new values


Usage :

$userModel->where('id', 5)
          ->update([
              'name' => 'ali'
            ]);

$postModel->likeWhere('title', 'test')
              ->update([
                  'title' => 'this is a test post'
              ]);



save

save method allows you to update values for selected record directly.


Definition :

public function save()


Usage :

$user = $userModel->find(1);

$user->username = 'Mohammad Waleed';
$user->email = 'new-email@email.test';

$user->save();



Delete

delete

delete records from the table via the delete method.
You may constrain delete statements by adding where clauses before calling the delete method.


Definition :

public function delete()


Usage :

$userModel->find(105)->delete();


delete method allows you to delete multiple records at once. 


$postModel->where('vote', "<", 2)
              ->where('visitors', '<', 200)
              ->orWhere('status', 'draft')
              ->delete();

Collections

PHPtricks\Orm\Collection\Collection class provides a fluent, convenient wrapper for working with retrieved data.

select first paginate find methods return instance of PHPtricks\Orm\Collection\Collection

that's mean you can work with data in easy!

Available Methods :

all first last
each filter map
keys toJson toJsonFormatted
merge toArray

all methods works with returned data, not with arrays.

all

convert data to [array, object] that how you configure fetch


Definition

public function all()


Usage

$model = new MyModel();
$data = $model->select();

var_dump($data->all());



first

get first record as [array, object] that how you configure fetch


Definition

public function first()


Usage

$model = new MyModel();
$data = $model->select();

var_dump($data->first());



last

get last record as [array, object] that how you configure fetch


Definition

public function last()


Usage

$model = new MyModel();
$data = $model->select();

var_dump($data->last());



each

foreach data and do something with callback


Definition

public function each(callable $callback)


Parameters

$callback required callback function to applied on data.


Usage

$users = new UserModel();
$data = $users->where('active', 0)->select();

$processedData = $data->each(function ($row) {
    return $row->nots . ' - not active';
});



filter

Filters elements of a data using a callback function


Definition

public function filter(callable $callback = null)


Parameters

$callback optional callback function to applied on data.


Usage

$users = new UserModel();
$data = $users->where('active', 0)->select();

$filteredData = $data->filter(function($row) {
    return $row->id > 15;
});

$users = new UserModel();
$data = $users->where('active', 0)->select();

$filteredData = $data->filter(function($row, $key) {
    // echo $key;
    return $row->active != 0;
});


if you want to filter data to remove [empty or null] values, just call filter without a callback

$users = new UserModel();
$data = $users->where('active', 0)->select();

// exclude null and empty values !
$filteredData = $data->filter();



map

Applies the callback to the elements of the given data


Definition

public function map(callable $callback)


Parameters

$callback required callback function to applied on data.


Usage

$users = new UserModel();
$data = $users->where('active', 0)->select();

$processedData = $data->map(function ($row) {
    // callback
});



keys

get columns name.


Definition

public function keys()


Usage

$users = new UserModel();
$data = $users->select();

var_dump($data->keys());



toJson

convert data to [string] json format.


Definition

public function toJson()


Usage

$users = new UserModel();
$data = $users->select();

echo $data->toJson();



toJsonFormatted

convert data to readable [string] json format.


Definition

public function toJsonFormatted()


Usage

$users = new UserModel();
$data = $users->select();

echo $data->toJsonFormatted();



merge

merge 2 collections together


Definition

public function merge($items)


Parameters

$items required the data you want to marge with.


Usage

$users = new UserModel();
$data = $users->where('active', 0)->select();
$anotherData = $users->where('active', 1)->select();

$mergedData = $data->marge($anotherData);



toArray

convert data to array.


Definition

public function toArray()


Usage

$users = new UserModel();
$data = $users->where('active', 0)->select();

var_dump($data->toArray());