smorken/ext-database

Database extension for Laravel 9

v9.0.1 2022-06-06 19:23 UTC

Requires

• php: ^8.0
• illuminate/database: ^9.0
• illuminate/support: ^9.0

Requires (Dev)

• mockery/mockery: ^1.0
• phpunit/phpunit: ^9.0
• smorken/docker: *
• yajra/laravel-oci8: ^9.0

Suggests

• yajra/laravel-oci8: ^9.0

MIT

• Scott Morken

README

License

This software is open-sourced software licensed under the MIT license

The Laravel framework is open-sourced software licensed under the MIT license

Requirements

• PHP 7.0+
• Composer

Installation

• add to your Laravel app composer.json

"require": {
    "smorken/ext-database": "~5.5"
}

• composer update

• add service provider to config/app.php

'providers' => [
...
    \Smorken\Ext\Database\DatabaseServiceProvider::class,

Builder additions

• ::createOrUpdate($keys, array $values) - this will attempt to do a merge or a insert ... on duplicate key update

• ::concatenate($values, $separator = null, $as_expression = false) - this will return a concatenated string or expression

• ::complexJoin($toTable, $fromTable, $keys, $joinType = 'join') - this method is a helper to make building joins with multiple keys simpler

Model additions

• ::createOrUpdate($keys, array $data) - see builder

• ::compositeBelongsTo($related, array $foreignKey, array $otherKey, $relation = null) - creates a belongs to relation with composite keys

• ::compositeHasMany($related, array $foreignKey, array $localKey) - creates a has many relation with composite keys

• ::compositeHasOne($related, array $foreignKey, array $localKey) - creates a has one relation with composite keys

• ::hasManyThroughArray($related, $through, array $nearToMid, array $midToFar) - creates a has many through relation with keys that are different on all tables

• ::belongsToManyArray($related, array $parentToMid, array $midToFar, $table = null, $relation = null) - creates a belong to many relation with keys that are different on all tables

• ::belongsToModel($related, array $attributes = [], $relation = null) creates a belong to relation using attributes found on the loaded model - default is all attributes, attributes can be 'local_key' => 'model_key' or 0 => 'key' (if shared)

Relationship examples

Composite Belongs To

class MyModel extends \Smorken\Ext\Database\Eloquent\Model
{
    public function anotherModel()
        {
            return $this->compositeBelongsTo(
                AnotherModel::class,
                ['t2_join_1', 't2_join_2', 't2_join_3', 't2_join_4'], //$this columns
                ['t1_join_1', 't1_join_2', 't1_join_3', 't1_join_4']  //AnotherModel columns
            );
        }
    
        public function complicatedModel()
        {
            $func = function($start, $count) {
                return function($ev, $model) use ($start, $count) {
                    return substr($model->t2_join_1, $start, $count);
                };
            };
            $exp1 = new Expression('SUBSTR(t2_join_1, 1, 3)');
            $exp2 = new Expression('SUBSTR(t2_join_1, 4, 6)');
            $col1 = new ExpressionValue('t2_join_1', $exp1, $func(0, 3));
            $col2 = new ExpressionValue('t2_join_1', $exp2, $func(3, 3));
            return $this->compositeBelongsTo(
                ComplicatedModel::class,
                [$col1, $col2,],
                ['t1_join_1', 't1_join_2',]
            );
        }
}

Composite Has Many

class AnotherModel extends \Smorken\Ext\Database\Eloquent\Model
{
    public function myModels()
        {
            return $this->compositeHasMany(
                MyModel::class,
                ['t2_join_1', 't2_join_2', 't2_join_3', 't2_join_4'], //$this
                ['t1_join_1', 't1_join_2', 't1_join_3', 't1_join_4']  //MyModel
            );
        }
}

class ComplicatedModel extends \Smorken\Ext\Database\Eloquent\Model
{
    public function myModels()
        {
            $func = function($start, $count)
            {
                return function($ev, $model) use ($start, $count) {
                    return substr($model->t2_join_1, $start, $count);
                };
            };
            $exp1 = new Expression('SUBSTR(t2_join_1, 1, 3)');
            $exp2 = new Expression('SUBSTR(t2_join_1, 4, 6)');
            $col1 = new ExpressionValue('t2_join_1', $exp1, $func(0, 3));
            $col2 = new ExpressionValue('t2_join_1', $exp2, $func(3, 3));
            return $this->compositeHasMany(
                MyModel::class,
                [$col1, $col2],
                ['t1_join_1', 't1_join_2']
            );
        }
}

Composite Has One

class MyModel extends \Smorken\Ext\Database\Eloquent\Model
{
    public function anotherModel()
        {
            return $this->compositeHasOne(
                AnotherModel::class,
                ['t2_join_1', 't2_join_2', 't2_join_3', 't2_join_4'], //$this columns
                ['t1_join_1', 't1_join_2', 't1_join_3', 't1_join_4']  //AnotherModel columns
            );
        }
}