metarush / data-mapper
A generic data access layer for basic CRUD operations. Can act as a layer between database and repositories/services.
Requires
- php: >=7.1
- atlas/query: ^1.2
- phpstan/phpstan: ^1.0
Requires (Dev)
- phpunit/phpunit: ^7.5
README
A generic data access layer for basic CRUD operations. Can act as a layer between database and repositories/services.
Install
Install via composer as metarush/data-mapper
Usage
Init library
<?php $builder = (new \MetaRush\DataMapper\Builder) ->setDsn('mysql:host=localhost;dbname=example') // PDO DSN ->setDbUser('foo') ->setDbPass('bar'); $dM = $builder->build();
Create new row
// insert 'foo' in column 'col1' and 'bar' in column 'col2' $data = [ 'col1' => 'foo', 'col2' => 'bar' ]; $lastInsertId = $dM->create('table', $data);
Find column
// find value of column 'col2' where 'col1' == 'foo' $column = $dM->findColumn('table', ['col1' => 'foo'], 'col2'); \print_r($column); // bar
Find row
// find row where column 'col1' == 'foo' $row = $dM->findOne('table', ['col1' => 'foo']); \print_r($row);
Find rows
// find all rows $rows = $dM->findAll('table'); \print_r($rows); // find rows where column 'col1' = 'foo' $rows = $dM->findAll('table', ['col1' => 'foo']); \print_r($rows); // find rows where column 'col1' = 'foo', order by col1 DESC $rows = $dM->findAll('table', ['col1' => 'foo'], 'col1 DESC'); \print_r($rows); // find rows where column 'col1' = 'foo', order by col2 DESC, limit 2, offset 3 $rows = $dM->findAll('table', ['col1' => 'foo'], 'col2 DESC', 2, 3); \print_r($rows); // find rows grouped by column 'col1' $dM->groupBy('col1'); $rows = $dM->findAll('table'); \print_r($rows);
Update rows
$data = ['col1' => 'bar']; $where = ['col2' => 'foo']; $dM->update('table', $data, $where);
Delete rows
$where = ['col1' => 'foo']; $dM->delete('table', $where);
Using $where
clause
As per Atlas.Query
documentation, if the value of the column given is an array, the condition will be IN (). Given a null value, the condition will be IS NULL. For all other values, the condition will be =. If you pass a key without a value, that key will be used as a raw unescaped condition.
$where = [ 'foo' => ['a', 'b', 'c'], 'bar' => null, 'baz' => 'dib', 'zim = NOW()' ];
The above sample is equivalent to
WHERE foo IN (:__1__, :__2__, :__3__) AND bar IS NULL AND baz = :__4__ AND zim = NOW()
Other examples using other WHERE
operators:
$where = [ 'foo > 20', 'bar <= 30', 'baz BETWEEN 5 AND 10', "firstName LIKE 'test%'" ];
Remember, if you pass a key without a value (like these other WHERE
operators), they will be unescaped.
Transaction methods
$dM->beginTransaction(); $dM->commit(); $dM->rollBack();
Custom SQL query
You can use prepared statements with placeholders or named parameters
SELECT
$preparedStatement = 'SELECT * FROM table WHERE x = ? AND y = ?'; $bindParams = ['foo', 'bar']; $fetchStyle = \PDO::FETCH_BOTH; // See https://www.php.net/manual/en/pdostatement.fetch.php for options. Default: \PDO::FETCH_BOTH $rows = $dM->query($preparedStatement, $bindParams, $fetchStyle); \print_r($rows);
Single INSERT
$preparedStatement = "INSERT INTO table (firstName, lastName, age) VALUES (?, ?, ?)"; $bindParams = ['Mark', 'Calaway', '18']; $numberOfAffectedRows = $dM->exec($preparedStatement, $bindParams); // returns 1 $lastInsertID = $dM->getLastInsertId();
Multiple INSERT in one statement
$preparedStatement = "INSERT INTO table (firstName, lastName, age) VALUES (?, ?, ?), (?, ?, ?)"; $bindParams = ['Mark', 'Calaway', '18', 'Dwayne', 'Johnson', '17']; $numberOfAffectedRows = $dM->exec($preparedStatement, $bindParams); // returns 2 $lastInsertID = $dM->getLastInsertId();
UPDATE
$preparedStatement = "UPDATE table SET age = ? WHERE lastName = 'Doe'"; $bindParams = ['18']; $numberOfAffectedRows = $dM->exec($preparedStatement, $bindParams);
DELETE
$preparedStatement = "DELETE FROM table WHERE lastName = ?"; $bindParams = ['Doe']; $numberOfAffectedRows = $dM->exec($preparedStatement, $bindParams);
Optional config/builder methods
->setStripMissingColumns(true);
If set totrue
,create()
and update()
methods will strip missing columns in their $data
parameter.
->setTablesDefinition(array $tablesDefinition);
Required when using setStripMissingColumns(true)
Example parameter for $tablesDefinition
:
$tablesDefinition = [ 'UsersTable' => [ // table name 'id', 'firstName', 'lastName' // column names ], 'PostsTable' => [ // table name 'id', 'subject', 'message' // columns names ] ];
Current adapters
- PDO (via Atlas.Query)