README

Laravel使用查询构建器（Fluent）而不是Eloquent的存储库。它返回StdClass的集合或简单的StdClass对象。它可以接收数组以在数据库中创建或更新记录，也可以删除单个记录或多个记录。

安装

composer require ceddyg/query-builder-repository

用法

创建存储库

首先，您必须创建一个存储库并定义表、主键和可填充字段。

默认情况下，表将使用存储库名称的复数蛇形形式（不带"Repository"），主键默认为"id"。

namespace App\Repositories;

use CeddyG\QueryBuilderRepository\QueryBuilderRepository;

class ProductRepository extends QueryBuilderRepository
{
    //By default $sTable = 'products'
    protected $sTable = 'product';

    //By default $sPrimaryKey = 'id'
    protected $sPrimaryKey = 'id_products';

    //The attributes that are mass assignable.
    protected $fillable = ['name','category'];
}

可用方法

• all(array $aColumns)
• find(int $id, array $aColumns)
• findByField(string $sField, mixed $mValue, array $aColumns)
• findWhere(array $aWhere, array $aColumns)
• findWhereIn(string $sField, array $aWhere, array $aColumns)
• findWhereNotIn(string $sField, array $aWhere, array $aColumns)
• first(array $aColumns)
• last(array $aColumns)
• paginate(int $iLimit, array $aColumns, string $sPageName, int $iPage)
• create(array $aAttributes)
• update(int $id, array $aAttributes)
• updateOrCreate(array $aAttributes)
• delete(array|int $id)

附加方法

• getTable
• getPrimaryKey
• getFillFromView
• orderBy
• limit
• datatable

all

获取数据库中的所有记录。

$oRepository = new ProductRepository();

$oProducts = $oRepository->all(); //Collection
//or
$oProducts = $oRepository->all(['name']); //Collection

foreach ($oProducts as $oProduct)
{
    //$oProduct is a StdClass
    echo oProduct->name;
}

find

通过ID查找记录。

$oRepository = new ProductRepository();

$oProduct = $oRepository->find(1); //StdClass with all columns
//or
$oProduct = $oRepository->find(1, ['name']); //StdClass with specific columns

echo oProduct->name; 

findByField

通过给定字段查找记录。

$oRepository = new ProductRepository();

$oProducts = $oRepository->findByField('name', 'Matrix'); //Collection
//or
$oProducts = $oRepository->findByField('name', 'Matrix', ['name', 'category']); //Collection

foreach ($oProducts as $oProduct)
{
    //$oProduct is a StdClass
    echo oProduct->name;
    echo oProduct->category;
}

findWhere

通过给定WHERE子句查找记录。

$oRepository = new ProductRepository();

$oProducts = $oRepository->findWhere(['price' => 20]); //Will find all products where the price = 20 and return a Collection
//or
$oProducts = $oRepository->findWhere(['price', '<', 20]); //Will find all products where the price < 20 and return a Collection
//or
$oProducts = $oRepository->findWhere([['price', '<', 20]], ['name']); //Will find all products where the price < 20 and return a Collection 
//or
$oProducts = $oRepository->findWhere([['price', '<', 20], ['name', 'LIKE', 'Mat%']], ['name']);

foreach ($oProducts as $oProduct)
{
    //$oProduct is a StdClass
    echo oProduct->name;
}

findWhereIn

通过给定WHERE IN子句查找记录。

$oRepository = new ProductRepository();

$oProducts = $oRepository->findWhereIn('name', ['Matrix', 'Matrix 2'])); //Collection
//or
$oProducts = $oRepository->findWhereIn('name', ['Matrix', 'Matrix 2'], ['name', 'category'])); //Collection

foreach ($oProducts as $oProduct)
{
    //$oProduct is a StdClass
    echo oProduct->name;
    echo oProduct->category;
}

findWhereNotIn

通过给定WHERE NOT IN子句查找记录。

$oRepository = new ProductRepository();

$oProducts = $oRepository->findWhereNotIn('name', ['Matrix', 'Matrix 2'])); //Collection
//or
$oProducts = $oRepository->findWhereNotIn('name', ['Matrix', 'Matrix 2'], ['name', 'category'])); //Collection

foreach ($oProducts as $oProduct)
{
    //$oProduct is a StdClass
    echo oProduct->name;
    echo oProduct->category;
}

first

返回第一条记录。

$oRepository = new ProductRepository();

$oRepository->first(); //StdClass
//or
$oRepository->first(['name']); //StdClass

#### last

Return the last record.

```php
$oRepository = new ProductRepository();

$oRepository->last(); //StdClass
//or
$oRepository->last(['name']); //StdClass

paginate

与fluent的使用相同。

create

创建记录。

$oRepository = new ProductRepository();

$aAttributes = [
    'name'      => 'Matrix 2',
    'category'   => 'DVD'
];
//or
$aAttributes = [
    [
        'name'      => 'Matrix 2',
        'category'   => 'DVD'
    ],
    [
        'name'      => 'Matrix 3',
        'category'   => 'DVD'
    ]
];

$oRepository->create($aAttributes);//Return insert id if 1 create or bool if multiple

update

更新数据库中的记录。

$oRepository = new ProductRepository();

$aAttributes = [
    'name'      => 'Matrix 1',
    'category'   => 'DVD'
];

$oRepository->update(1, $aAttributes);

updateOrCreate

插入或更新与属性匹配的记录，并用值填充它。

$oRepository = new ProductRepository();

$aAttributes = [
    'ref' => 'PROD-01'
];

$aValues = [
    'name'      => 'Matrix 2',
    'category'   => 'DVD'
];

$oProduct = $oRepository->updateOrCreate($aAttributes, $aValues)

delete

从数据库中删除一个或多个记录。

$oRepository = new ProductRepository();

$oRepository->delete(1); //Delete the record with id 1
//or
$oRepository->delete([1, 2, 3]); //Delete the record with id 1, 2 and 3

getTable

返回表。

$oRepository = new ProductRepository();

$sTable = $oRepository->getTable();

getPrimaryKey

返回主键。

$oRepository = new ProductRepository();

$sPrimaryKey = $oRepository->getPrimaryKey();

getFillFromView

从给定视图中获取列。它将检查可填充字段是否在视图中，并将它们添加到查询的列列表中。

$oRepository = new ProductRepository();

$oProducts = $oRepository->getFillFromView('product/index')->all();
//or
$oProducts = $oRepository->getFillFromView('product/index')->all(['name']);//Will merge fill in the view and parameters in all()

//Other
$oProduct = $oRepository->getFillFromView('product/index')->find(1);
$oProducts = $oRepository
    ->getFillFromView('product/index')
    ->findWhere([['price', '<', 20], ['name', 'LIKE', 'Mat%']], ['name']);

datatable

当您想使用Datatable的服务器端时使用。indexAjax()是您在Ajax中调用的控制器方法。

PHP

public function indexAjax(Request $oRequest)
{
    $oRepository = new ProductRepository();

    return $this->oRepository->datatable($oRequest->all());
}

HTML

<table id="tab-admin" class="table no-margin table-bordered table-hover">
	<thead>
		<tr>
			<th>ID</th>
			<th>Name</th>
			<th>Price</th>
			<th>Category</th>
			<th>Tag</th>
			<th>Tag Category</th>
			<th></th>
			<th></th>
		</tr>
	</thead>
</table>

JavaScript

$(document).ready(function() {
    $('#tab-admin').DataTable({
        serverSide: true,
        ajax: {
            url: '../ajax-url'
        },
        columns: [
            { data: "id" },
            { data: "name" },
            { data: "price" },
            { 
                data: "category_name",
                name: "category.name"
            },
            {
                data: "tag_name",
                name: "tag.name",
                //If you have many tag and want to replace ' / '
                render: function ( data, type, row, meta ) {
                    return data.replace(" / ", "</br>"); ;
                }
            },
            {
                data: "category_tag_name",
                name: "tag.category_tag.name"
            },
            //Add a button to edit
            { 
                data: "id",  
                render: function ( data, type, row, meta ) {
                    
                    var render = "{!! Button::warning('Edit')->asLinkTo(route('admin.admin.edit', 'dummyId'))->extraSmall()->block()->render() !!}";
                    render = render.replace("dummyId", data); 
                    
                    return render;
                }
            },
            //Add a button to delete
            { 
                data: "id",  
                render: function ( data, type, row, meta ) {
                    
                    var render = '{!! BootForm::open()->action( route("admin.admin.destroy", "dummyId") )->attribute("onsubmit", "return confirm(\'Are you sure to delete ?\')")->delete() !!}'
                        +'{!! BootForm::submit("Delete", "btn-danger")->addClass("btn-block btn-xs") !!}'
                        +'{!! BootForm::close() !!}';
                    render = render.replace("dummyId", data); 
                    
                    return render;
                } 
            }
        ],
        //Don't sort edit and delete column
        aoColumnDefs: [
            {
                bSortable: false,
                aTargets: [ -1, -2 ]
            }
        ]
    });
} );

orderBy

按给定字段和方向排序。默认方向为'asc'。

$oRepository = new ProductRepository();

$oProducts = $oRepository->orderBy('name')->all();
//or
$oProducts = $oRepository->orderBy('name')->findWhere(['categorie_id', 1]);
//or
$oProduct = $oRepository->orderBy('id', 'desc')->find(1, ['name']); //Useless

limit

限制查询。

$oRepository = new ProductRepository();

$oProducts = $oRepository->limit(0, 10)->all(); //Will take the first 10 records
//or
$oProducts = $oRepository->limit(5, 5)->all(); //Will take the 5 records after the 5th record.
//or
$oProduct = $oRepository->limit(0, 10)->find(1, ['name']); //Useless

时间戳

您可以为记录自动设置时间戳。

namespace App\Repositories;

use CeddyG\QueryBuilderRepository\QueryBuilderRepository;

class ProductRepository extends QueryBuilderRepository
{
    /**
     * Indicates if the query should be timestamped.
     *
     * @var bool
     */
    protected $bTimestamp = true;

    /**
     * The name of the "created at" column.
     *
     * @var string
     */
    const CREATED_AT = 'created_at';

    /**
     * The name of the "updated at" column.
     *
     * @var string
     */
    const UPDATED_AT = 'updated_at';
}

日期

您可以指定数据库中的默认日期格式和存储在数据库中的默认日期格式。

namespace App\Repositories;

use CeddyG\QueryBuilderRepository\QueryBuilderRepository;

class ProductRepository extends QueryBuilderRepository
{

    protected $aFillable = ['name', 'category', 'price', 'date_limit'];
    
    protected $aDates = ['date_limit'];
    
    //By default $sDateFormatToGet = 'Y-m-d'
    protected $sDateFormatToGet = 'd/m/Y';

    //By default $sDateFormatToStore = 'Y-m-d'
    protected $sDateFormatToStore = 'Y-m-d';
}

然后，如果您数据库中有2017-05-24，您将得到

$oRepository = new ProductRepository();

$oProduct = $oRepository->first(['date_limit']);
echo $oProduct->date_limit; // 24/05/2017

$oRepository->update(1, ['date_limit' => '25/05/2017']) // Will store 2017-05-25 in the database

自定义属性

您可以获取特定属性。

namespace App\Repositories;

use CeddyG\QueryBuilderRepository\QueryBuilderRepository;

class ProductRepository extends QueryBuilderRepository
{

    protected $aFillable = ['name', 'category', 'price', 'date_limit'];
    
    /**
     * Will change a fill that came from the database
     *
     * @param Collection|StdClass $oItem
     */
    public function getPriceAttribute($oItem)
    {
        return oItem->price * 1.2;
    }
    
    /**
     * Will create a new attribute that not in database
     *
     * @param Collection|StdClass $oItem
     */
    public function getReferenceAttribute($oItem)
    {
        return oItem->name.' '.oItem->category;
    }
}

然后您可以使用它。

$oRepository = new ProductRepository();

$oProduct = $oRepository->first(['name', 'category', 'price', 'reference']);

您可以在类中指定您需要的自定义属性所在的列。

namespace App\Repositories;

use CeddyG\QueryBuilderRepository\QueryBuilderRepository;

class ProductRepository extends QueryBuilderRepository
{
    protected $aFillable = ['name', 'category', 'price', 'date_limit'];
    
    /**
     * List of the customs attributes.
     * 
     * @var array
     */
    protected $aCustomAttribute = [
        'reference' => [
            'name',
            'category'
        ],
        'tag_name' => [
            'tag.name'
        ]
    ];
    
    /**
     * Will create a new attribute that not in database
     *
     * @param Collection|StdClass $oItem
     */
    public function getReferenceAttribute($oItem)
    {
        return oItem->name.' '.oItem->category;
    }
    
    /**
     * Will create a new attribute that not in database
     *
     * @param Collection|StdClass $oItem
     */
    public function getTagNameAttribute($oItem)
    {
        return oItem->tag[0]->name;
    }

    public function tag()
    {
        $sForeignKey = 'fk_product';
        $sOtherForeignKey = 'fk_tag';

        //If $sForeignKey is null, the method will set 'product_id' (<table name>.'_id')
        //If $sOtherForeignKey is null, the method will set tag_id (<table name of TagRepository>.'_id')
        $this->belongsToMany('App\Repositories\TagRepository', 'product_tag', $sForeignKey, $sOtherForeignKey);
    }
}

然后。

$oRepository = new ProductRepository();

$oProduct = $oRepository->first(['price', 'reference', 'tag_name']);

关系

要配置关系，就像Eloquent一样，您必须使用其他存储库定义belongsTo、belongsToMany或hasMany。

belongsTo($sRepository, $sForeignKey = null)
belongsToMany($sRepository, $sPivotTable, $sForeignKey = null, $sOtherForeignKey = null)
hasMany($sRepository, $sForeignKey = null)

namespace App\Repositories;

use Ceddyg\QueryBuilderRepository\QueryBuilderRepository;

class ProductRepository extends QueryBuilderRepository
{
    //By default $sTable = 'product'
    protected $sTable = 'products';

    //By default $sPrimaryKey = 'id'
    protected $sPrimaryKey = 'id_products';

    //The attributes that are mass assignable.
    protected $fillable = ['name','category'];

    public function tag()
    {
        $sForeignKey = 'fk_tag';

        //If $sForeignKey is null, the method will set tag_id (<table name of TagRepository>.'_id')
        $this->belongsTo('App\Repositories\TagRepository', $sForeignKey);
    }
    
    //or
    public function tag()
    {
        $sForeignKey = 'fk_product';
        $sOtherForeignKey = 'fk_tag';

        //If $sForeignKey is null, the method will set 'product_id' (<table name>.'_id')
        //If $sOtherForeignKey is null, the method will set tag_id (<table name of TagRepository>.'_id')
        $this->belongsToMany('App\Repositories\TagRepository', 'product_tag', $sForeignKey, $sOtherForeignKey);
    }
    
    //or
    public function tag()
    {
        $sForeignKey = 'fk_product';

        //If $sForeignKey is null, the method will set 'product_id' (<table name>.'_id')
        $this->hasMany('App\Repositories\TagRepository', 'product_id');
    }
}

关系被视为列，因此要添加它

$oRepository = new ProductRepository();

//It will take the name attribut and add the relation tag to an attribut "tag"
$oProduct = $oRepository->find(1, ['name', 'tag']);

echo $oProduct->name;
echo $oProduct->tag->name;

//If belongsToMany or hasMany relation, $oProduct->tag is a Collection
foreach ($oProduct->tag as $oTag)
{
    //$oTag is a StdClass
    echo $oTag->name;
}

要使用getFillFromView，你必须定义允许的关系

/**
* List of relations we allow in getFillFromView.
* 
* @var array 
*/
protected $aRelations = ['tag'];

你可以指定关系是作为数组还是集合返回。

$oRepository = new ProductRepository();

//True : collection | false : array (good way to work with a lot of data)
$oRepository->setReturnCollection(false); //True by default

//It will take the name attribut and add the relation tag to an attribut "tag"
$oProduct = $oRepository->find(1, ['name', 'tag']);

foreach ($oProduct->tag as $oTag)
{
    //$oTag is a StdClass
    echo $oTag->name;
}

连接

你可以指定一个数据库（在config/database.php中设置）。

namespace App\Repositories;

use Ceddyg\QueryBuilderRepository\QueryBuilderRepository;

class ProductRepository extends QueryBuilderRepository
{
    protected $sConnection = 'mysql';
}

或者

$oRepository = new ProductRepository();
$oRepository->setConnection('mysql');

$oProduct = $oRepository->find(1);

待办事项列表

• 添加特定的setter
• 在getFillFromView方法中仅选择可填充的关系（如果视图中存在$oItem->tag->tag_name，系统必须仅选择tag_name）
• 通过关系添加
• 混合分页和可用方法
• 添加一个生成存储库的命令