README

通常，当有过滤需求时，会提供一系列资源。但是，处理过滤条件和相应构建查询并不总是显而易见的：不同表上的多个过滤条件、过滤器的多个值、空值、日期等。

此库提供了一个简单的实现，可以从过滤器的数组构建查询条件。

要求和安装

• php >=5.4

composer require nmarniesse/pomm-filter

使用方法

该库帮助创建一个实例 PommProject\Foundation\Where，您可以在每个pomm查询中使用它（有关进一步说明，请参阅此处）。

为了解释我们可以用此库做什么，我们可以用一个实际案例：我们想要过滤活跃产品，颜色为'蓝色'或'黄色'，类别为'配件'，价格在50到100之间，并且有一个标签。

过滤器数组的表示形式是

use NMarniesse\PommFilter\FilterInterface;

$array_filters = [
    'is_active'  => '1',
    'color'      => ['blue', 'yellow'],
    'category'   => ['accessory'],
    'price_from' => 50,
    'price_to'   => 100,
    'tag'        => FilterInterface::_not_null_,
];

使用类似HTTP查询的代码 ?filter[is_active]=1&filter[color][]=blue&filter[color][]=yellow&filter[category]=accessory&filter[price_from]=50&filter[price_from]=50&filter[price_to]=100&filter[tag]=_not_null_，您可以在PHP中使用 $array_filters = $_GET['filter']; 获取相同的数组。

现在您有了数组过滤器，让我们构建查询

use NMarniesse\PommFilter\FilterCondition;
use NMarniesse\PommFilter\FilterType\BasicFilter;
use NMarniesse\PommFilter\FilterType\BooleanFilter;

# The sql query with a placeholder for the where condition
$sql = <<<SQL
SELECT
  p.id,
  p.color,
  c.category_id,
  pr.unit_price
FROM product p
 INNER JOIN category c     ON ...
 INNER JOIN price pr       ON ...
 LEFT JOIN  product_tag pt ON ...
WHERE {conditions}
SQL;

# Define the available filters and create the Where instance
$filter_condition = new FilterCondition('p');

$filter_condition->addFilter(new BasicFilter('color', 'p')); // optional
$filter_condition->addFilter(new BooleanFilter('is_active'));
$filter_condition->addFilter(new BasicFilter('category_id', 'c'));
$filter_condition->addFilter(new BasicFilter('unit_price', 'pr', '>='));
$filter_condition->addFilter(new BasicFilter('unit_price', 'pr', '<='), 'price_from');
$filter_condition->addFilter(new BasicFilter('tag', 'pt'), 'price_to');
// ...

$where = $filter_condition>getWhere($array_filters);

# Execute the query with Pomm with our instance of Where
$sql = str_replace('{conditions}', (string) $where, $sql);
$pomm_session->getQueryManager()->query($sql, $where->getValues());

重要提示

即使生成的 Where 条件可以防止SQL注入，请注意您必须根据您的业务规则清理和验证用户提供的数据。

文档

FilterCollection

默认情况下，FilterCollection 不包含任何过滤器。

方法 getWhere($filters) 将任何关联数组转换为 Where 实例。当您执行 getWhere(['key1' => 'val1']) 时，它假定key1字段存在于您的查询中，并构建一个简单的条件查询 key1 = $*，参数为 'val1'。

如果您想指定条件查询中的表别名，或不想使用 = 操作符，您必须使用 addFilter 方法手动添加过滤器。

示例

use NMarniesse\PommFilter\FilterCondition;
use NMarniesse\PommFilter\FilterType\BasicFilter;

# Create a filter condition.
# When you pass a filter {"key1": "value1"}, it assumes that the field *key1* exists in your query
$filter_condition = new FilterCondition();

# When you have multiple tables in your query, you may specify the table/alias name
# Then when you pass a filter {"key1": "value1"}, it will automatically construct "user.key1 = $*"
$filter_condition = new FilterCondition('user');

# To use a filter on a field which is not on main table, you have to add it manually
# For example to add a filter on the field category on table p
$filter_condition->addFilter(new BasicFilter('category', 'p'));

# If you want personnalize your filter name, use second parameter to specify it
$filter_condition->addFilter(new BasicFilter('category', 'p'), 'my_custom_category_filter_name');

过滤器类型

此库提供几种过滤器类型，以帮助您创建自己的过滤器集合。

BasicFilter

如其名所示，此类用于创建简单过滤器。
但是，您可以指定要使用的操作符（默认为 =），以自定义过滤器的行为。

use NMarniesse\PommFilter\FilterCondition;
use NMarniesse\PommFilter\FilterType\BasicFilter;

# Create a BasicFilter
$filter1 = new BasicFilter('color');

# Create a BasicFilter and specify the table name/alias used in the query
$filter2 = new BasicFilter('category_id', 'c');

# If you want to filter on prices greater than specific value
$filter3 = new BasicFilter('unit_price', 'p', '>=');

# If you want to filter on prices greater than specific value
$filter4 = new BasicFilter('unit_price', 'p', '<=');

$filter_condition = new FilterCondition();
$filter_condition->addFilter($filter1);
$filter_condition->addFilter($filter2);
$filter_condition->addFilter($filter3, 'price_from');
$filter_condition->addFilter($filter4, 'price_to');

# Filter on color 'blue' or 'yellow', with category 'accessory', and price between 50 and 100
$filter_condition>getWhere([
    'color'      => ['blue', 'yellow'],
    'category'   => ['accessory'],
    'price_from' => 50,
    'price_to'   => 100,
]);

DateTimeFilter

此过滤器允许您使用日期值。

use NMarniesse\PommFilter\FilterCondition;
use NMarniesse\PommFilter\FilterType\DateTimeFilter;

# Create DateTimeFilter
$filter1 = new DateTimeFilter('created_at', '', '>=');
$filter2 = new DateTimeFilter('created_at', '', '<=');

$filter_condition->addFilter($filter1, 'created_date_from');
$filter_condition->addFilter($filter2, 'created_date_to');

# Filter on color 'blue' or 'yellow', with category 'accessory', and price between 50 and 100
$filter_condition>getWhere([
    'created_date_from' => '2010-01-01T00:00:00+00',
    'created_date_to'   => '2010-12-31T23:59:59+00',
]);

BooleanFilter

此过滤器用于处理布尔字段。

use NMarniesse\PommFilter\FilterCondition;
use NMarniesse\PommFilter\FilterType\DateTimeFilter;

# Create BooleanFilter
$filter1 = new BooleanFilter('is_new');
$filter_condition->addFilter($filter1);

# Filter on true value
$filter_condition>getWhere([
    'is_new' => true, // Any value different from false, 'inactive', 'false', '0', 0
]);

# Filter on true value
$filter_condition>getWhere([
    'is_new' => false, // Any value among the values false, 'inactive', 'false', '0', 0
]);

HstoreFilter

此过滤器用于处理hstore字段。

假设我们有一个包含诸如街道、城市、邮政编码、国家等键的hstore字段full_address...

use NMarniesse\PommFilter\FilterCondition;
use NMarniesse\PommFilter\FilterType\HstoreFilter;

# Create HstoreFilter
$filter1 = new HstoreFilter('city', 'full_address');
$filter2 = new HstoreFilter('country_code', 'full_address');
$filter_condition->addFilter($filter1);
$filter_condition->addFilter($filter2);

# Filter on city value
$filter_condition>getWhere([
    'city' => 'Paris',
]);

# Filter on country_code value
$filter_condition>getWhere([
    'country_code' => 'FR',
]);

LtreeFilter

此过滤器用于处理ltree字段。由于ltree通常用于处理树形视图，您可以使用此过滤器对值及其所有后代进行过滤。如果您不想过滤后代，则BasicFilter足以。

RangeFilter

此过滤器用于处理范围字段。值可以是单个值或由 NMarniesse\PommFilter\ValueType\RangeValue 对象标识的范围值。在两种情况下，过滤器都会测试值是否包含在范围字段中。

use NMarniesse\PommFilter\FilterCondition;
use NMarniesse\PommFilter\FilterType\RangeFilter;
use NMarniesse\PommFilter\ValueType\RangeValue;

# Create a RangeFilter
$filter1 = new RangeFilter('score_range');
$filter2 = new RangeFilter('lifetime');
$filter_condition->addFilter($filter1);
$filter_condition->addFilter($filter2);

# Filter entities which have a score 10 and be active in 2010 january
$filter_condition>getWhere([
    'score_range' => 10,
    'lifetime'    => new RangeValue(
        new \DateTime('2010-01-01 00:00:00+00:00'),
        new \DateTime('2010-12-31 23:59:59+00:00')
    ),
]);

开发

运行单元测试

make unit-tests