psx / sql
Build complex data structures from relational databases
Installs: 42 535
Dependents: 2
Suggesters: 0
Security: 0
Stars: 3
Watchers: 1
Forks: 1
Open Issues: 1
Requires
- php: >=7.2
- doctrine/dbal: ^2.12
- nikic/php-parser: ^4.0
- psx/record: ^1.0
- symfony/console: ^4.0|^5.0
Requires (Dev)
- phpunit/phpunit: ^8.0
- psx/cache: ^1.0
- vimeo/psalm: ^4.0
This package is auto-updated.
Last update: 2020-12-30 23:38:53 UTC
README
About
The SQL library helps to manage data from relational databases. It is designed to build complex API responses from SQL queries. It has a simple table concept where a table class represents a table on your database. The table knows about the available columns of the table thus it can provide basic CRUD operations. The strength of this library is the ability to build complex JSON responses based on raw SQL queries.
Basic usage
The following are basic examples how you can work with a table class
<?php use PSX\Sql\Condition; use PSX\Sql\Fields; use PSX\Sql\Sql; use PSX\Sql\TableManager; $connection = null; // a doctrine DBAL connection $tableManager = new TableManager($connection); $table = $tableManager->getTable(Acme\Table\News::class); // returns by default 16 entries from the table ordered by the primary column // descending. The default settings can be overriden in the table class $table->getAll(); // returns 12 entries starting at index 0 $table->getAll(0, 12); // orders the entries after the column "id" descending $table->getAll(0, 12, 'id', Sql::SORT_DESC); // adds a condition to select only the rows where the title contains "foo" $condition = new Condition(); $condition->like('title', '%foo%'); $table->getAll(0, 12, 'id', Sql::SORT_DESC, $condition); // adds a blacklist so that the column "password" gets not returned $table->getAll(0, 12, 'id', Sql::SORT_DESC, null, Fields::blacklist(['password'])); // returns all columns which match the provided condition $table->getBy(new Condition(['userId', '=', 1])); // it is also possible to use a magic method which adds a ['userId', '=', 1] // condition $table->getByUserId(1); // returns a single row matching the provided condition $table->getOneBy(new Condition(['userId', '=', 1])); // it is also possible to use a magic method $table->getOneByUserId(1); // returns a complete row by the primary key $table->get(1); // returns the count of entries in the table. It is also possible to provide a // condition $table->getCount(); // creates a new row on the table $table->create([ 'title' => 'foo' ]); // updates a row. The array must contains the primary key column $table->update([ 'id' => 1, 'title' => 'bar', ]); // deletes a row. The array must contains the primary key column $table->delete([ 'id' => 1, ]);
Table
The following is an example of a table class.
<?php namespace Acme\Table; use PSX\Sql\TableAbstract; use PSX\Sql\TableInterface; class AcmeTable extends TableAbstract { public function getName() { return 'acme_table'; } public function getColumns() { return array( 'id' => TableInterface::TYPE_INT | 10 | TableInterface::PRIMARY_KEY | TableInterface::AUTO_INCREMENT, 'userId' => TableInterface::TYPE_INT | 10, 'title' => TableInterface::TYPE_VARCHAR | 32, 'date' => TableInterface::TYPE_DATETIME, ); } }
Views
It is also possible to build view classes which is not based on a specific table. Instead you can build complex aggregated result sets.
<?php namespace Acme\View; use PSX\Sql\Reference; use PSX\Sql\ViewAbstract; class AcmeView extends ViewAbstract { /** * Example howto build a nested result based on different tables. It * contains also some field tranformations */ public function getNestedResult() { $definition = [ 'totalEntries' => $this->getCount(), 'entries' => $this->doCollection('SELECT id, authorId, title, createDate FROM news ORDER BY createDate DESC', [], [ 'entryId' => 'id', 'title' => $this->fieldCallback('title', function($title){ return ucfirst($title); }), 'isNew' => $this->fieldValue(true), 'author' => $this->doEntity('SELECT name, uri FROM author WHERE id = :id', ['id' => new Reference('authorId')], [ 'displayName' => 'name', 'uri' => 'uri', ]), 'date' => $this->fieldDateTime('createDate'), 'links' => [ 'self' => $this->fieldReplace('http://foobar.com/news/{id}'), ] ]) ]; return $this->build($definition); } }
The getNestedResult
method could produce the following json response
{ "totalEntries": 2, "entries": [ { "entryId": 1, "title": "Foo", "isNew": true, "author": { "displayName": "Foo Bar", "uri": "http:\/\/phpsx.org" }, "date": "2016-03-01T00:00:00+01:00", "links": { "self": "http:\/\/foobar.com\/news\/1" } }, { "entryId": 2, "title": "Bar", "isNew": true, "author": { "displayName": "Foo Bar", "uri": "http:\/\/phpsx.org" }, "date": "2016-03-01T00:00:00+01:00", "links": { "self": "http:\/\/foobar.com\/news\/2" } } ] }
Generation
The library contains also a Symfony-Command to generate a table class from an actual table.
sql:generate acme_news > AcmeTable.php