Dimitri Dimitri - 4 months ago 12
PHP Question

Building data access layer in PHP application and ORM

I'm having some trouble figuring out how to access data from my database tables. I know there are many frameworks out there that facilitate with this, such as Yii for example, but I'm trying to avoid that route due to its learning curve with others in my team and time restriction.

Suppose I have a table called

File
which houses a list of files with their names, upload dates, upload user etc. I also have another table called
Versions
which essentially holds each file's version: when user uploads the same file, it considers it as a revision.

Files

fid | name | uploadDate
1 | test | 2017-01-01
2 | test2 | 2017-01-01


Versions

vid | fid | user
1 | 1 | a
2 | 1 | b


My initial thought was to create a class for each table where I would throw in a
fid
in the
Files
constructor and retrieve that single row and do the same for the
Versions
, where an
vid
would be put in the constructor and retrieve a specific version.

However, this means that I would have two separate queries, one for each class. So I was thinking of perhaps combining them into one. This would mean that I would retrieve the file information AND the versions information under one query (with the use of LEFT JOIN) within the
Files
class then use
Versions
class to handle the data rather than querying the db and also handling the data.

For some reason I feel like this comes with a better performance since I'm using JOINS to help me out here, but on the other hand, if I only need information for a specific Version of a file, I need to create an instance of
File
, which means using a
fid
THEN retrieving the Version I want from within. (<-- poorly worded).

Perhaps someone can give me a little insight on this manner and what to do when no access to a framework.

Answer

ORM will not solve all your problems

In many cases it is convenient to use Models to access DB but not always. Probably it will be better for you to keep your ORM models free of tasks where you need complex queries and high performance.

Use ORM pattern properly

ORM is just one of many coding patterns which you can use in your code.

Read this topic to learn more about it What is an ORM and where can I learn more about it?

You should not consider ORM as application layer which isolates access to database. It is just a technique to manipulate data in object oriented manner.

In every popular framework you'll see that ORM is an option, not a mandatory layer.

Consider the case when you want to get all versions for specific file. Create something like FileVersionManager singleton with method getAllVersions which will perform JOIN query by fid. Here can be also something like checkoutToVersion or getPreviousVersion. This methods can return one or set of your Version ORM models. And what is important -- this way will be more semantically meaningful and therefore more simple to read and to understand by other programmers.

Or if you need to produce version on each file change you can delegate this logic to something like Versionable behavior or something.

I prefer to think about ORM as about some kind of application domain definition layer.

So, answering your question

If you want your own ORM implementation, then do not make it too complex. Create separate ORM models for each table with CRUD methods and schema definition. And use other patterns to perform complex queries.

Example (Query.selectWhere should be implemented to make example working):

<?php


/**
 * @property $name
 **/
class File extends ORMModel
{
    public function tableName()
    {
        return 'files';
    }

    public function properties()
    {
        return array_merge(parent::properties(), ['name']);
    }
}

/**
 * @property $number
 * @property $file_id
 **/
class Version extends ORMModel
{
    public function tableName()
    {
        return 'versions';
    }

    public function properties()
    {
        return array_merge(parent::properties(), ['file_id', 'number']);
    }

    public function getFile()
    {
        return new File($this->file_id);
    }
}

class FileVersionManager
{
    public static function getLatestVersion($file)
    {
        $query = new Query();
        $rows = $query->selectWhere((new Version())->tableName(), ['file_id' => $file->id]);

        $max = 0;
        $maxId = null;
        foreach ($rows as $row) {
            if ($row['number'] > $max) {
                $maxId = $row['id'];
                $max = $row['number'];
            }
        }

        return new Version($maxId);
    }

    public static function createNewVersion($file)
    {
        $latestVersion = static::getLatestVersion($file);
        $newVersion = new Version();
        $newVersion->file_id = $file->id;
        $newVersion->number = $latestVersion->number + 1;
        $newVersion->insert();

        return $newVersion;
    }
}

class Query
{

    private static $datasource = [
        'files' => [
            1 => [
                'name' => 'file1',
            ],
            2 => [
                'name' => 'file1',
            ]
        ],
        'versions' => [
            1 => [
                'file_id' => 1,
                'number' => 1
            ],
            2 => [
                'file_id' => 1,
                'number' => 2
            ],
            3 => [
                'file_id' => 2,
                'number' => 1
            ],
            4 => [
                'file_id' => 2,
                'number' => 2
            ],
        ]
    ];

    public function select($tablename) {
        return static::$datasource[$tablename];
    }

    public function selectOne($tablename, $id) {
        return @static::$datasource[$tablename][$id];
    }

    public function selectWhere($tableName, $condition) {
        //@todo: implement selection assuming that condition is ['propertyName1' => 'propertyValue1', 'propertyName2' => 'propertyValue2',  ]
        //should retun array of properties including id for above example
        return [];
    }

    public function update($tableName, $id, $values) {
        if (empty(static::$datasource[$tableName][$id])) return false;
        static::$datasource[$tableName][$id] = $values;
        return true;
    }

    public function insert($tableName,  $values) {
        $id = max(array_keys(static::$datasource[$tableName])) + 1;
        static::$datasource[$tableName][$id] = $values;
        return $id;
    }

    public function delete($tableName, $id)
    {
        unset(static::$datasource[$tableName][$id]);
        return true;
    }

}

/**
 * @property $id
 **/
abstract class ORMModel
{
    private $properties;

    public abstract function tableName();
    public function properties() {
        return ['id'];
    }

    public function __get($name) {
        $propertyNames = $this->properties();

        if (in_array($name, $propertyNames)) {
            return @$this->properties[$name];
        }
    }

    public function __set($name, $value) {
        $propertyNames = $this->properties();

        if (in_array($name, $propertyNames)) {
            $this->properties[$name] = $value;
        }
    }

    public function __construct($id = null)
    {
        if (!empty($id)) {
            $query = new Query();
            if ($this->properties = $query->selectOne($this->tableName(), $id)) {
                $this->properties['id'] = $id;
            }
        }
    }

    public function insert()
    {
        $query = new Query();
        $id = $query->insert($this->tableName(), $this->properties);
        $this->properties['id'] = $id;
        return $this;
    }

    public function update()
    {
        if (empty($this->properties['id'])) return false;

        $query = new Query();
        return $query->update($this->tableName(), $this->id, array_diff($this->properties, ['id']));
    }

    public function delete()
    {
        if (empty($this->properties['id'])) return false;

        $query = new Query();
        return $query->delete($this->tableName(), $this->id);
    }
}


$version = new Version(1);
$file = $version->getFile();
var_dump($file->name);

$file = new File(2);
$version = FileVersionManager::getLatestVersion($file);
var_dump($version->number);

FileVersionManager::createNewVersion($file);
$version = FileVersionManager::getLatestVersion($file);
var_dump($version->number);

P.S. Here you can define own Query implementation and this should work fine with your (any) data source

P.P.S. And I still would recommend you to learn some popular framework (Yii2, Laravel, Symfony or any other) just because it is a good way to learn best practices in building application architecture

Comments