With object relational mapping, you don’t have to know structured query language (SQL) in order to write database queries. Liten and the ORM helps facilitate simple and rapid development and also allows both fluent sql queries as well as CRUD operations.

ORM

Instantiate

To create a new database connection, we need to instantiate the ORM class. Throughout the rest of the docs, we will use the variable $orm for the database connection.

$pdo = new \PDO("mysql:host=localhost;dbname=$dbname", $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$orm = new \Liten\Orm($pdo);

$pdo = new \PDO("mysql:host=localhost;dbname=$dbname", $username, $password);

$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$orm = new \Liten\Orm($pdo);

Table

Connect to a database table by calling the table() method.

$posts = $orm->table('posts');

1	$posts = $orm->table('posts');

An alternative to above is calling the database table as a method.

$posts = $orm->posts();

1	$posts = $orm->posts();

Insert

When calling the insert(array $data) method, $data can be passed as one dimensional array to insert one new record or multiple arrays to insert multiple records.

Single entry:

$post = $posts->insert([
                "title" => "Liden Framework",
                "content" => "The Liden framework is small, simple, restful.", 
                "authorID" => 1,
                "timestamp" => $orm->NOW()
            ]);

$post = $posts->insert([

"title" => "Liden Framework",

"content" => "The Liden framework is small, simple, restful.",

"authorID" => 1,

"timestamp" => $orm->NOW()

]);

Multiple entries:

$massPosts = $posts->insert([
                    [
                         "title" => "Micro Frameworks",
                         "content" => "Micro frameworks started...",
                         "authorID" => 1,
                         "timestamp" => $orm->NOW()
                    ],
                    [
                         "title" => "PHP 5.6",
                         "content" => "The new features in 5.6 are...",
                         "authorID" => 1,
                         "timestamp" => $orm->NOW()
                    ],
                    [
                         "title" => "RESTful API's",
                         "content" => "Building restful api's...",
                         "authorID" => 1,
                         "timestamp" => $orm->NOW()
                    ],
                ]);

$massPosts = $posts->insert([

[

"title" => "Micro Frameworks",

"content" => "Micro frameworks started...",

"authorID" => 1,

"timestamp" => $orm->NOW()

[

"title" => "PHP 5.6",

"content" => "The new features in 5.6 are...",

"authorID" => 1,

"timestamp" => $orm->NOW()

[

"title" => "RESTful API's",

"content" => "Building restful api's...",

"authorID" => 1,

"timestamp" => $orm->NOW()

]);

Update

There are two ways to update a record, by using the active record pattern or by or by using the where clause.

Single entry:

$post->update([
                    "title" => "PHP 6.0"
                ]);

$post->update([

"title" => "PHP 6.0"

]);

The above can also be written as:

$post->title = "PHP 6.0";
$post->update();

1 2	$post->title = "PHP 6.0"; $post->update();

You can use the alternative save() instead of update().

Or you can use the set(array $data) or set($key, $value)

$post->set('title','PHP 6.0')->update();

1	$post->set('title','PHP 6.0')->update();

For multiple entries using set(array $data) and where($key, $value).

$post->set([
             "content" => "PHP 6.0 is the greatest because..."
        ])
        ->where("title", "PHP 6.0")
        ->update();

$post->set([

"content" => "PHP 6.0 is the greatest because..."

])

->where("title", "PHP 6.0")

->update();

Save

Save() is a shortcut to insert() or update().

Insert:

$post = $orm->posts();
$post->title = "PHP 6.0";
$post->content = "PHP 6.0 is the greatest because...";
$post->save();

$post = $orm->posts();

$post->title = "PHP 6.0";

$post->content = "PHP 6.0 is the greatest because...";

$post->save();

Update:

$post = $posts->findOne(3847);
$post->title = "PHP 6.0 Update";
$post->save();

$post = $posts->findOne(3847);

$post->title = "PHP 6.0 Update";

$post->save();

Delete

Single entry:

$post = $posts->reset()->findOne(3847);
$post->delete();

1 2	$post = $posts->reset()->findOne(3847); $post->delete();

Multiple entries:

$posts->where("title", "PHP 6.0")->delete();

1	$posts->where("title", "PHP 6.0")->delete();

Count

Count all the entries based on where() clause.

$allPosts = $posts->count();

$count = $posts->where($x, $y)->count();

$allPosts = $posts->count();

$count = $posts->where($x, $y)->count();

Use count for a specific column name.

$count = $posts->where($x, $y)->count($columnName);

1	$count = $posts->where($x, $y)->count($columnName);

Max

Max based on where() clause.

$max = $posts->where($x, $y)->max($columnName);

1	$max = $posts->where($x, $y)->max($columnName);

Min

Min based on where() clause.

$min = $posts->where($x, $y)->min($columnName);

1	$min = $posts->where($x, $y)->min($columnName);

Sum

Sum based on where() clause.

$sum = $posts->where($x, $y)->sum($columnName);

1	$sum = $posts->where($x, $y)->sum($columnName);

Avg

Average based on where clause.

$avg = $posts->where($x, $y)->avg($columnName);

1	$avg = $posts->where($x, $y)->avg($columnName);

Aggregate

$agg = $posts->where($x, $y)->aggregate('GROUP_CONCAT $columnName');

1	$agg = $posts->where($x, $y)->aggregate('GROUP_CONCAT $columnName');

Querying

The fluent query feature of the ORM allows you to write simple queries without having to write SQL.

FindOne

Returns a single record is found otherwise it will return false.

$post = $posts->where('postID', 364)
              ->findOne();

1 2	$post = $posts->where('postID', 364) ->findOne();

You can achieve the same above by using only the primary key and dropping the where clause.

$post = $posts->findOne(364);

1	$post = $posts->findOne(364);

Retrieving the entry

if ($post) {
    echo " $post->title";

// On a retrieved entry you can perform update and delete
    $post->last_viewed = $posts->NOW();
    $post->save();
}

if ($post) {

echo " $post->title";

// On a retrieved entry you can perform update and delete

$post->last_viewed = $posts->NOW();

$post->save();

}

Find

Find returns and ArrayIterator of rows found, otherwise it will return false.

$allPosts = $posts->where('title', 'PHP 6.0')
                  ->find();

foreach ($allPosts as $post) {
    echo "{$post->content}";

// On a retrieved entry you can perform update and delete
    $post->last_viewed = $posts->NOW();
    $post->save();
}

$allPosts = $posts->where('title', 'PHP 6.0')

->find();

foreach ($allPosts as $post) {

echo "{$post->content}";

// On a retrieved entry you can perform update and delete

$post->last_viewed = $posts->NOW();

$post->save();

}

Find also accepts a closure ( find(Closure $callback) ) to perform data manipulation.

    $posts->where('title', 'PHP 6.0');

    $results = $posts->find(function($data){
        $newResults = array();

        foreach ($data as $d) {
            $d["new_title"] = "{$data["title"]}";
            $newResults[] = $d;
        }

        return $newResults;
    });

$posts->where('title', 'PHP 6.0');

$results = $posts->find(function($data){

$newResults = array();

foreach ($data as $d) {

$d["new_title"] = "{$data["title"]}";

$newResults[] = $d;

}

return $newResults;

});

Fluent Query Builder

Select

Select all:

$posts->select()

1	$posts->select()

Select columns:

$posts->select("title, content")
      ->select("last_viewed");

1 2	$posts->select("title, content") ->select("last_viewed");

Where

Where can be used to setup the where clauses and they work with find(), findOne(), update(), and delete(). This is the same for the where aliases as well.Repetitive call to where and it’s aliases will append to each other using the AND operator. Use _or() to mimic the OR operator.

Examples:

$posts->where("title", "PHP 6.0");

$posts->where("authorID > ?", 25);

$posts->where("name in (?, ?, ?)", "PHP 6.0", "HTML 5", "ROR");

$posts->where("(field1, field2)", array(array(1, 2), array(3, 4)))

$posts->where("title", "PHP 6.0");

$posts->where("authorID > ?", 25);

$posts->where("name in (?, ?, ?)", "PHP 6.0", "HTML 5", "ROR");

$posts->where("(field1, field2)", array(array(1, 2), array(3, 4)))

There where aliases can help shorten the where examples above.

Primary key:

$posts->wherePK(456)

1	$posts->wherePK(456)

Not equal to:

$posts->whereNot('postID', 24);

1	$posts->whereNot('postID', 24);

Like:

$posts->whereLike('title', 'PH%');

1	$posts->whereLike('title', 'PH%');

Not like:

$posts->whereNotLike('title', 'PH%');

1	$posts->whereNotLike('title', 'PH%');

Greater than:

$posts->whereGt('timestamp', 2014-09-14);

1	$posts->whereGt('timestamp', 2014-09-14);

Greater than equal to:

$posts->whereGte('timestamp', 2014-09-14);

1	$posts->whereGte('timestamp', 2014-09-14);

Less than:

$posts->whereLt('timestamp', 2014-09-14);

1	$posts->whereLt('timestamp', 2014-09-14);

Less than equal to:

$posts->whereLte('timestamp', 2014-09-14);

1	$posts->whereLte('timestamp', 2014-09-14);

Where in:

$posts->whereIn('authorID', array('2', '24'));

1	$posts->whereIn('authorID', array('2', '24'));

Where not in:

$posts->whereNotIn('authorID', array('2', '24'));

1	$posts->whereNotIn('authorID', array('2', '24'));

Where null:

$posts->whereNull('content');

1	$posts->whereNull('content');

Where not null:

$posts->whereNotNull('timestamp');

1	$posts->whereNotNull('timestamp');

Where with OR and AND

Use _and_() / _or_() chained to any where clauses.

_and_():

$posts->where("authorID", 24)->_and_()->whereGte("timestamp", 2014-09-14);

1	$posts->where("authorID", 24)->_and_()->whereGte("timestamp", 2014-09-14);

_or_:

$posts->where("authorID", 24)->_or_()->whereGte("authorID", 24)->_or_()->where("category", "Programming");

1	$posts->where("authorID", 24)->_or_()->whereGte("authorID", 24)->_or_()->where("category", "Programming");

Order, Group, Limit, Offset

$posts->orderBy('postID', 'DESC');

$posts->groupBy('category');

$posts->limit(10);

$posts->offset(10);

$posts->orderBy('postID', 'DESC');

$posts->groupBy('category');

$posts->limit(10);

$posts->offset(10);

Joins

/**
 * Defaults to LEFT JOIN, for others, use INNER, RIGHT, etc as the
 * $join_operator
 *
 * join( $tablename, $constraint, $table_alias , $join_operator )
 */

$posts->_join('category', 'c.catID = posts.catID', 'c')

/**

* Defaults to LEFT JOIN, for others, use INNER, RIGHT, etc as the

* $join_operator

* join( $tablename, $constraint, $table_alias , $join_operator )

$posts->_join('category', 'c.catID = posts.catID', 'c')

Last Modified: Apr 23, 2015 @ 11:33 PM

Liten Framework

Small, Simple, RESTful

Object Relational Mapping

ORM

Instantiate

Table

Insert

Update

Save

Delete

Count

Max

Min

Sum

Avg

Aggregate

Querying

FindOne

Find

Fluent Query Builder

Select

Where

Where with OR and AND

Order, Group, Limit, Offset

Joins