symfony - FOS 弹性范围价格与折扣

Question

我想以折扣价获得所有产品。

这是它在 sql 中的样子：

  WHERE (
CASE WHEN p.discount IS NOT NULL THEN ROUND(
  p.unit_price * (100 - p.discount) / 100, 1) 
ELSE p0_.unit_price END ) >= :min 
  AND (
    CASE WHEN p.discount IS NOT NULL THEN ROUND(
      p.unit_price * (100 - p.discount) / 100, 1) 
    ELSE p0_.unit_price END ) <= :max 

有没有办法对范围条件做同样的事情？

$fieldRange = new \Elastica\Query\Range('unitPrice', array('gte' => 300, 'lte' => 1500));

这是我的配置：

fos_elastica:
    clients:
        default: { url: '%env(ELASTICSEARCH_URL)%' }
    indexes:
        product:
               properties:
                   unitPrice:
                       type: integer
                   discount:
                       type: keyword
                   attributeValues:
                       type: "nested"
                       properties:
                           value:
                               type: keyword
                           product:
                               type: keyword
           persistence:
               driver: orm
               model: App\Entity\Product
               provider: ~
               listener: ~
               finder: ~

这是完整的查询：

    $query = new \Elastica\Query();
    $query->setSize(0);

    $boolQuery = new \Elastica\Query\BoolQuery();

  /* filter checked */
    $fieldQuery = new \Elastica\Query\Match();
    $fieldQuery->setFieldQuery('attributeValues.value', 'Brand');
    $domainQuery = new \Elastica\Query\Nested();
    $domainQuery->setPath('attributeValues');
    $domainQuery->setQuery($fieldQuery);

    $fieldQuery2 = new \Elastica\Query\Match();
    $fieldQuery2->setFieldQuery('attributeValues.value', 'Another Brand');
    $domainQuery2 = new \Elastica\Query\Nested();
    $domainQuery2->setPath('attributeValues');
    $domainQuery2->setQuery($fieldQuery2);

    $fieldRange = new \Elastica\Query\Range('unitPrice', array('gte' => 300, 'lte' => 1500));


    $boolQuery->addMust($domainQuery);
    $boolQuery->addMust($domainQuery2);
    $boolQuery->addMust($fieldRange);

    $query->setQuery($boolQuery);

    $agg = new \Elastica\Aggregation\Nested('attributeValues', 'attributeValues');
    $names = new \Elastica\Aggregation\Terms('value');
    $cardinality = new \Elastica\Aggregation\Cardinality('unique_products');

    $cardinality->setField('attributeValues.product');
    $names->setField('attributeValues.value');
    $names->setSize(100);

    $names->addAggregation($cardinality);
    $agg->addAggregation($names);
    $query->addAggregation($agg);



    $companies = $this->finder->findPaginated($query);
    $asd = $companies->getAdapter()->getAggregations();

结果如下：

        array(
    "attributeValues" => array:2(
        "doc_count" => 406,
        "value" => array:3(
            "doc_count_error_upper_bound" => 0,
            "sum_other_doc_count" => 0,
            "buckets" => array:42(
                2 => array:3(
                    "key" => "Another Brand",
                    "doc_count" => 15,
                    "unique_products" => array:1(
                        "value" => 9
                    )
                )
            )
        )
    )
);

这是本机请求（以防万一）：

{
"size": 0,
"query": {
    "bool": {
        "must": [
            {
                "nested": {
                    "path": "attributeValues",
                    "query": {
                        "match": {
                            "attributeValues.value": {
                                "query": "Brand"
                            }
                        }
                    }
                }
            },
            {
                "nested": {
                    "path": "attributeValues",
                    "query": {
                        "match": {
                            "attributeValues.value": {
                                "query": "Another Brand"
                            }
                        }
                    }
                }
            },
            {
                "range": {
                    "unitPrice": {
                        "gte": 300,
                        "lte": 1500
                    }
                }
            }
        ]
    }
},
"aggs": {
    "attributeValues": {
        "nested": {
            "path": "attributeValues"
        },
        "aggs": {
            "value": {
                "terms": {
                    "field": "attributeValues.value",
                    "size": 100
                },
                "aggs": {
                    "unique_products": {
                        "cardinality": {
                            "field": "attributeValues.product"
                        }
                    }
                }
            }
        }
    }
}
}

一点解释 - 我正在制作一个智能过滤器，当其中没有产品时禁用选项，我用这个查询计算。但我不知道如何计算弹性折扣（％）的价格范围。我展示了我是如何在 sql 中做到这一点的。

标签： symfonyelasticsearchfoselasticabundle