windwalker/datamapper

Windwalker DataMapper package

Windwalker DataMapper

Installation via Composer

Add this to the require block in your composer.json., _(*1)

``` json { "require": { "windwalker/datamapper": "~3.0" } }, <sub>(*2)</sub>

## Getting Started

### Prepare Windwalker Database object

``` php
use Windwalker\Database\DatabaseFactory;

// Make the database driver.
$db = DatabaseFactory::getDbo(
    'mysql',
    array(
        'driver'   => 'mysql',
        'host'     => 'localhost',
        'user'     => 'root',
        'password' => 'xxxx',
        'database' => 'mydb',
        'prefix'   => 'prefix_'
    )
);

The DatabaseDriver will be cached in Factory, now DataMapper will auto load database driver., <sub>(*3)</sub>

See Database, <sub>(*4)</sub>

Create DataMapper

``` php use Windwalker\DataMapper\DataMapper;, <sub>(*5)</sub>

$fooMapper = new DataMapper('#__foo');, <sub>(*6)</sub>

$fooSet = $fooMapper->find(array('id' => 1));, <sub>(*7)</sub>

Inject DB to DataMapper

``` php
// $db is Windwalker DatabaseDriver
$mapper = new DataMapper('table', null, $db);

Custom primary keys:, <sub>(*8)</sub>

``` php // If keep keys NULL, the default id will auto set. $mapper = new DataMapper('table'); // Keys: array('id'), <sub>(*9)</sub>

// Set custom key $mapper = new DataMapper('table', 'table_id'); // Keys: array('table_id'), <sub>(*10)</sub>

// Set multiple keys $mapper = new DataMapper('table', array('table_id', 'uuid')); // Keys: array('table_id', 'uuid'), <sub>(*11)</sub>

### Extend It

You can also create a class to operate specific table:

``` php
class FooMapper extends DataMapper
{
    protected static $table = '#__foo';

    protected static $keys = 'id';
}

$data = (new FooMapper)->findAll();

Or using facade:, <sub>(*12)</sub>

``` php use Windwalker\DataMapper\AbstractDatabaseMapperProxy;, <sub>(*13)</sub>

abstract class FooMapper extends AbstractDatabaseMapperProxy { protected $table = '#__foo';, <sub>(*14)</sub>

protected $keys = 'id'; // Keep NULL will use default `id`

}, <sub>(*15)</sub>

$data = FooMapper::findOne(array('id' => 5, 'alias' => 'bar'));, <sub>(*16)</sub>

## Find Records

Find method will fetch rows from table, and return `DataSet` class.

### find()

Get id = 1 record

``` php
$fooSet = $fooMapper->find(array('id' => 1));

Fetch published = 1, and sort by date, <sub>(*17)</sub>

``` php $fooSet = $fooMapper->find(array('published' => 1), 'date');, <sub>(*18)</sub>

Fetch published = 1, language = en-US, sort by `date` DESC and start with `30`, limit `10`.

``` php
$fooSet = $fooMapper->find(array('published' => 1, 'language' => 'en-US'), 'date DESC', 30, 10);

Using array, will be IN condition:, <sub>(*19)</sub>

``` php $fooSet = $fooMapper->find(array('id' => array(1,2,3))); // WHERE id IN (1,2,3), <sub>(*20)</sub>

### findOne()

Just return one row.

``` php
$foo = $dooMapper->findOne(array('published' => 1), 'date');

findAll()

Equal to find(array(), $order, $start, $limit)., <sub>(*21)</sub>

Find With Custom Query

``` php $fooMapper = new DataMapper('#__foo');, <sub>(*22)</sub>

$fooMapper->where('a = "b"') // Simple where ->where('%n = $q', 'foo', 'bar') // Where format ->where('flower = :sakura')->bind('sakura', 'Sakura') // Bind params ->orWhere(array('c = d', 'e = f')) // AND (c=d OR e=f) ->having('...') ->limit(10, 20) // Limit, offset ->order('created DESC') // Can be array or string ->select(array('id', 'title', 'alias')) // Can be array or string ->find();, <sub>(*23)</sub>

The available query methods.

- `join($type = 'LEFT', $alias, $table, $condition = null, $prefix = null)`
- `leftJoin($alias, $table, $condition = null, $prefix = null)`
- `rightJoin($alias, $table, $condition = null, $prefix = null)`
- `nnerJoin($alias, $table, $condition = null, $prefix = null)`
- `outerJoin($alias, $table, $condition = null, $prefix = null)`
- `call($columns)`
- `group($columns)`
- `order($columns)`
- `limit($limit = null, $offset = null)`
- `select($columns)`
- `where($conditions, ...$args)`
- `orWhere($conditions)`
- `having($conditions, ...$args)`
- `orHaving($conditions)`
- `clear($clause = null)`
- `bind($key = null, $value = null, $dataType = \PDO::PARAM_STR, $length = 0, $driverOptions = array())`

See [Query Format](https://github.com/ventoviro/windwalker-query#format)

## Create Records

Using DataSet to wrap every data, then send this object to create() method, these data will insert to table.

### create()

``` php
use Windwalker\Data\Data;
use Windwalker\Data\DataSet;

$data1 = new Data;
$data1->title = 'Foo';
$data1->auhor = 'Magneto';

$data2 = new Data(
    array(
        'title' => 'Bar',
        'author' => 'Wolverine'
    )
);

$dataset = new DataSet(array($data1, $data2));

$return = $fooMapper->create($dataset);

The return value will be whole dataset and add inserted ids., <sub>(*24)</sub>

Windwalker\Data\DataSet Object
(
    [storage:ArrayObject:private] => Array
        (
            [0] => Windwalker\Data\Data Object
                (
                    [title] => Foo
                    [auhor] => Magneto
                    [id] => 39
                )

            [1] => Windwalker\Data\Data Object
                (
                    [title] => Bar
                    [auhor] => Wolverine
                    [id] => 40
                )
        )
)

createOne()

Only insert one row, do not need DataSet., <sub>(*25)</sub>

``` php $data = new Data; $data->title = 'Foo'; $data->auhor = 'Magneto';, <sub>(*26)</sub>

$fooMapper->createOne($data);, <sub>(*27)</sub>

## Update Records

Update methods help us update rows in table.

### update()

``` php
use Windwalker\Data\Data;
use Windwalker\Data\DataSet;

$data1 = new Data;
$data1->id = 1;
$data1->title = 'Foo';

$data2 = new Data(
    array(
        'id' => 2,
        'title' => 'Bar'
    )
);

$dataset = new DataSet(array($data1, $data2));

$fooMapper->update($dataset);

updateOne()

Just update one row., <sub>(*28)</sub>

``` php $data = new Data; $data->id = 1; $data->title = 'Foo';, <sub>(*29)</sub>

$fooMapper->updateOne($data);, <sub>(*30)</sub>

### updateAll()

UpdateAll is different from update method, we just send one data object, but using conditions as where
to update every row match these conditions. We don't need primary key for updateAll().

``` php
$data = new Data;
$data->published = 0;

$fooMapper->updateAll($data, array('author' => 'Mystique'));

Delete

Delete rows by conditions., <sub>(*31)</sub>

delete()

``` php $boolean = $fooMapper->delete(array('author' => 'Jean Grey'));, <sub>(*32)</sub>

## Join Tables

Use `newRelation()` to create a DataMapper and join other tables.

``` php
use Windwalker\DataMapper\DataMapper;

$items = DataMapper::newRelation('flower', '#__flower')
    ->leftJoin('author', '#__users', 'flower.user_id = author.id')
    ->innerJoin('category', '#__categories', array('category.lft >= flower.lft', 'category.rgt <= flower.rgt'))
    ->where('flower.id = 1')
    ->order('created DESC')
    ->group('category.id')
    ->find();

The Join query will be:, <sub>(*33)</sub>

``` sql SELECT flower.id, flower.catid, flower.title, flower.user_id, flower.meaning, flower.ordering, flower.state, flower.params, author.id AS author_id, author.name AS author_name, author.pass AS author_pass, category.id AS category_id, category.title AS category_title, category.ordering AS category_ordering, category.params AS category_params FROM #__foo AS foo LEFT JOIN #__users AS author ON foo.user_id = author.id INNER JOIN #__categories AS category ON category.lft >= foo.lft AND category.rgt <= foo.rgt WHERE flower.id = 1 ORDER BY flower.created DESC GROUP BY category.id, <sub>(*34)</sub>

Where condition will auto add alias if not provided.

``` php
$fooMapper->find(array(
    'foo.id' => 3 // This is correct condition
    'state' => 1 // This field may cause column conflict, DataMapper will auto covert it to `foo.state` => 1
));

Reset all tables and query:, <sub>(*35)</sub>

``` php $fooMapper->reset();, <sub>(*36)</sub>

### Using OR Condition

``` php
$fooMapper->addTable(
    'category',
    '#__categories',
    'category.lft >= foo.lft OR category.rgt <= foo.rgt',
    'LEFT'
);

Group

``` php $fooMapper->group('category.id');, <sub>(*37)</sub>

## Compare objects

Using Compare objects help us set some where conditions which hard to use array to defind.

``` php
$fooSet = $fooMapper->find(
    array(
        new GteCompare('id', 5),
        new NeqCompare('name', 'bar')
        new LtCompare('published', 1),
        new NinCompare('catid', array(1,2,3,4,5))
    )
);

This will generate where conditions like below:, <sub>(*38)</sub>

``` sql WHERE id >= '5' AND name !== 'bar' AND published < '1' AND catid NOT IN (1,2,3,4,5), <sub>(*39)</sub>

### Available compares:

| Name       | Description      | Operator |
| ---------- | -----------------| -------- |
| EqCompare  | Equal                 | `=`  |
| NeqCompare | Not Equal             | `!=` |
| GtCompare  | Greater than          | `>`  |
| GteCompare | Greater than or Equal | `>=` |
| LtCompare  | Less than             | `<`  |
| LteCompare | Less than or Equal    | `<=` |
| InCompare  | In                    | `IN` |
| NinCompare | Not In                | `IN` |

### Custom Compare

``` php
echo (string) new Compare('title', '%flower%', 'LIKE');

Will be, <sub>(*40)</sub>

``` sql title LIKE %flower%, <sub>(*41)</sub>

See: https://github.com/ventoviro/windwalker-compare

## Using Data and DataSet

See: https://github.com/ventoviro/windwalker-data

## Hooks

Add `"windwalker/event": "~3.0"` to `composer.json`.

Then we are able to use hooks after every operations.

``` php
class FooListener
{
    public function onAfterCreate(Event $event)
    {
        $result = $event['result'];

        // Do something
    }
}

$mapper = new DataMapper('table');

// Add object as listener
$mapper->getDispatcher()->addListener(new FooListener);

// Use listen() to add a callback as listener
$mapper->getDispatcher()->listen('onAfterUpdate', function () { ... });

$mapper->create($dataset);

Extends DataMapper:, <sub>(*42)</sub>

``` php class SakuraMapper extends DataMapper { protected $table = 'saluras';, <sub>(*43)</sub>

public function onAfterFind(Event $event)
{
    $result = $event['result'];

    // Find some relations
}

}, <sub>(*44)</sub>

$mapper = new DataMapper('table'); $mapper->find(array('id' => 5)); ```, <sub>(*45)</sub>

Available events:, <sub>(*46)</sub>

• onBeforeFind
• onAfterFind
• onBeforeFindAll
• onAfterFindAll
• onBeforeFindOne
• onAfterFindOne
• onBeforeFindColumn
• onAfterFindColumn
• onBeforeCreate
• onAfterCreate
• onBeforeCreateOne
• onAfterCreateOne
• onBeforeUpdate
• onAfterUpdate
• onBeforeUpdateOne
• onAfterUpdateOne
• onBeforeUpdateBatch
• onAfterUpdateBatch
• onBeforeSave
• onAfterSave
• onBeforeSaveOne
• onAfterSaveOne
• onBeforeFlush
• onAfterFlush
• onBeforeDelete
• onAfterDelete

More about Event: Windwalker Event, <sub>(*47)</sub>