使用Laravel和Eloquent查询创建一个可过滤的列表 [英] Creating a filterable list using Laravel and Eloquent queries
问题描述
我正在开发一个应用程序的API，并且需要对结果进行过滤，例如：
I'm developing an API for an app and the results of offers need to be filterable, examples of this would be:
Filters
Price Min - Price Max
Category Ids
Min Review (1-5 stars) if 3 is sent then only 3 and above
Min Distance
Max Distance
Location (for the above 2)
Sort By
Distance,
Price,
Review
我以最有效的方式感到困惑，我会发布用户过滤器选项，但是根据他们执行查询选项是让我失去的部分。
I'm confused on the most efficient way to do this, I will be posting the users filter options but executing a query based on their options is the part that has me lost.
任何帮助都将被大量赞赏！感谢所有：）
Any help would be massively appreciated! Thanks all :)
推荐答案
当您开始对查询应用条件时，您需要注意以下功能： newQuery（） 。此功能将允许您启动一个新的查询构建器并将过滤器/顺序链接到其上。
When you start applying conditions on your queries, you'll need to take note of the following function: newQuery(). This function will allow you to start a new query builder and chain your filters/order bys onto it.
示例模式：
产品表中的列：
id |名称|价格| date_received
product_tag 表中的列：
id | tag_id | product_id
标签中的列：
id |名字
关系：
- 许多产品有很多标签
我不会设置雄辩的型号，不过请注意，产品型号有 tag（）功能与 hasManyThrough（）关系
I won't be setting up the eloquent models, though note that Product model has a tag() function with a hasManyThrough() relationship
已经设置了一个过滤器类，其主要目的是应用您的过滤器和订单。注意：该类可以进一步抽象。
A filter class has been setup with the main purpose of applying your filters & order bys. Note: The class can be abstracted even further.
您的过滤器类：
Your Filter Class:
class ProductFilter {
/**
* Fluent query builder
* @var mixed $queryBuilder
*/
private $queryBuilder;
/**
* Http Request
* @var \Illuminate\Http\Request $request
*/
protected $request;
/**
* Filters collection
* @var array $filters
*/
private $filters = [];
/**
* Order Bys Collection
* @var array $orderBys
*/
private $orderBys = [];
/**
* Class constructor
*
* @param array $input
*/
public function __construct(\Illuminate\Http\Request $request, &$queryBuilder)
{
//Initialize Query Builder
$this->queryBuilder = $queryBuilder;
//Get input
$this->request = $request;
//Register Filters
$this->registerFilters();
//Register Order Bys
$this->registerOrderBys();
}
/**
* Register Filters in the function below
* Each filter is in the form of an array
*/
private function registerFilters()
{
$this->filters['product_name'] = ['name'=>'Name',
'value' => $this->request->get('filter_product_name'),
'enabled' => $this->request->has('filter_product_name'),
'function' => 'filterProductName'
];
$this->filters['tag'] = ['name'=>'End Date',
'value' => $this->request->get('filter_tag'),
'enabled' => $this->request->has('filter_tag'),
'function' => 'filterTag'
];
}
/**
* Check if any filters are active
* Useful to show/hide filter bar
*
* @return bool
*/
public function isFiltersActive()
{
return (boolean)count(
array_filter($this->filters,function($v){
return $v['enabled'] === true;
})
);
}
/**
* Register Order Bys in the function below
* Each order by is in the form of an array
*
*/
private function registerOrderBys()
{
$this->orderBys['name'] = [
'name' => 'Order By Name',
'value' => $this->request->get('order_by_product_name','ASC'),
'enabled' => $this->request->has('order_by_product_name'),
'function' => 'orderByProductName'
];
}
/**
* Check if any order bys are active
* Useful to show/hide order by bar
*
* @return bool
*/
public function isOrderBysActive()
{
return (boolean)count(
array_filter($this->orderBys,function($v){
return $v['enabled'] === true;
})
);
}
/**
* Apply Filters
* Loop through each filter, check if they are enabled. If they are, apply filter to query builder
*/
public function applyFilters()
{
foreach($this->filters as $filter_name => $filter_array)
{
if($filter_array['enabled'] &&
array_key_exists('function',$filter_array) &&
method_exists($this,$filter_array['function']))
{
$this->{$filter_array['function']}($filter_array);
}
}
return $this;
}
/**
* Apply Order Bys
* Loop through each order by, check if they are enabled. If they are, apply order by to query builder
*/
public function applyFilters()
{
foreach($this->orderBys as $order_by_name => $order_by_array)
{
if($order_by_array['enabled'] &&
array_key_exists('function',$order_by_array) &&
method_exists($this,$order_by_array['function']))
{
$this->{$order_by_array['function']}($order_by_array);
}
}
return $this;
}
/*
* Filter Functions: START
*/
/**
* Filter by Product Name
*
* @param array $filterArray
*/
private function filterProductName($filterArray)
{
$this->queryBuilder
->where('name','=',$filterArray['value']);
}
/**
* Filter by Product Tag
*
* @param array $filterArray
*/
private function filterTag($filterArray)
{
$this->queryBuilder
->whereHas('tag',function($query) use ($filterArray){
return $query->where('name','=',$filterArray['value']);
});
}
/*
* Filter Functions: END
*/
/*
* Order By Functions: START
*/
/**
* Order By Name
* @param array $orderByArray
*/
private function orderByName($orderByArray)
{
$this->queryBuilder
->orderBy('name', $orderByArray['value']);
}
/*
* Order By Functions: END
*/
}
如何使用：
//In my controller function
public function getListOfProducts(\Illuminate\Http\Request $request)
{
//Init Product Query
$productQuery = \App\Models\Product()::newQuery();
//Apply all filters and order bys
$productFilter = app('ProductFilter',[$request,$productQuery])->applyFilters()->applyOrderBys();
//Fetch Product Result
$productResult = $productQuery->get();
}
这篇关于使用Laravel和Eloquent查询创建一个可过滤的列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
