guide.md

Guide

Installation

Install LessQL via Composer, the package name is morris/lessql:

{
    "require": {
        "morris/lessql": "~0.3"
    }
}

You can also download an archive from the GitHub repository.

LessQL requires PHP >= 5.3.0 and PDO.

Database

LessQL works on existing MySQL, PostgreSQL or SQLite3 databases. There's no schema generation or migration in LessQL, use a dedicated tool like Phinx for that.

The following tables are used throughout this guide:

user:           id, name
post:           id, title, body, date_published, is_published, user_id
categorization: category_id, post_id
category:       id, title

Setup

Create a PDO instance and a LessQL\Database using it. We will also need a few hints about our database so we define them at setup.

$pdo = new \PDO('sqlite:blog.sqlite3');
$db = new \LessQL\Database($pdo);

$db->setAlias('author', 'user');
$db->setPrimary('categorization', array('category_id', 'post_id'));

We define author to be a table alias for user and a compound primary key for the categorization table. See the Conventions section for more information about schema hints.

Finding and Traversal

The most interesting feature of LessQL is easy and performant traversal of associated tables. Here, we're iterating over four tables in an intuitive way, and the data is retrieved efficiently under the hood.

foreach ($db->post()
    ->orderBy('date_published', 'DESC')
    ->where('is_published', 1)
    ->paged(10, 1) as $post) {
    // Get author of post
    // Uses the pre-defined alias, gets from user where id is post.author_id
    $author = $post->author()->fetch();

    // Get category titles of post
    $categories = array();

    foreach ($post->categorizationList()->category() as $category) {
        $categories[] = $category['title'];
    }

    // render post
    $app->renderPost($post, $author, $categories);
}

LessQL creates only four queries to execute this example:

SELECT * FROM `post` WHERE `is_published` = 1 ORDER BY `published` DESC LIMIT 10 OFFSET 0
SELECT * FROM `user` WHERE `id` IN (...)
SELECT * FROM `categorization` WHERE `post_id` IN (...)
SELECT * FROM `category` WHERE `id` IN (...)

When traversing associations, LessQL always eagerly loads all references in one query. This way, the number of queries is always constant, no matter how "deep" you are traversing your database.

Let's step through the example in some detail. The first part iterates over a subset of posts:

foreach ($db->post()
    ->orderBy('date_published', 'DESC')
    ->where('is_published', 1)
    ->paged(10, 1) as $post) { /* ... */ }

The orderBy and where calls are basic SQL, paged(10, 1) limits to page 1 where pages have a size of 10 posts.

Note that Result objects are immutable. All filter methods like where or orderBy return a new Result instance with the new SELECT information.

Inside the loop, we have access to $post. It is a Row instance which can be worked with like an associative array or object. It can be modified, saved, deleted, and you can retrieve associated rows.

Many-To-One

// Get author of post
$author = $post->author()->fetch();

A post has one author, a Many-To-One-Association. LessQL will look for author_id in the post table and find the corresponding author, if any.

Note the explicit fetch() to get the row. This is required because you might want to get the author using other methods (via).

One-To-Many (and Many-To-Many, too)

// Get category titles of post
$categories = array();

foreach ($post->categorizationList()->category() as $category) {
    $categories[] = $category['title'];
}

A post has many categorizations, a One-To-Many-Association. The List suffix in $post->categorizationList() tells LessQL to look for post_id in the categorization table and find all rows that point to our post.

In turn, the categorization table points to the category table. This way we model a Many-To-Many-Association between posts and categories.

Note how we directly call ->category() without intermediate rows.

Saving

LessQL is capable of saving deeply nested structures with a single method call.

$row = $db->createRow('post', [
    'title' => 'Fantasy Movie Review',
    'author' => [
        'name' => 'Fantasy Guy'
    ],
    'categorizationList' => [
        [
            'category' => ['title' => 'Movies']
        ],
        [
            'category' => $existingFantasyCategory
        ]
    ]
]);

// wrapping this in a transaction is a good practice and more performant
$db->begin();
$row->save();
$db->commit();

LessQL generates all queries needed to save the structure, including references:

INSERT INTO `post` (`title`, `author_id`) VALUES ('Fantasy Movie Review', NULL)
INSERT INTO `user` (`name`) VALUES ('Fantasy Guy')
UPDATE `post` SET `user_id` = ... WHERE `id` = ...
INSERT INTO `category` (`title`) VALUES ('Movies')
INSERT INTO `categorization` (`post_id`, `category_id`) VALUES (...)
INSERT INTO `categorization` (`post_id`, `category_id`) VALUES (...)

For this operation to work, two things are crucial: First, author_id must be nullable. Second, LessQL must know about the compound primary key of the categorization table.

Always define required columns and compound primary keys at setup. See Conventions for more details.