当未提供某些搜索属性时，使用KVP返回结果进行搜索查询

Question

我需要关于成功运行的复杂SQL查询的帮助，但需要验证额外的步骤才能提供可靠的结果。

以下是此查询中涉及的3个主要表：

RULE
rule_id   rule_set_name   status   position   date_from   date_to
1         B2C             1        1          null        null
2         England All     1        2          null        null

RULE_ATTRIBUTE
attribute_id   attribute_code
1              customer_group
2              customer
3              country
4              subdivision_one
5              subdivision_two
RULE_ATTRIBUTE_VALUE
value_id   attribute_id   store_id   rule_id   value   parent_group_id   parent_country_id   parent_subdivision_1_id
9          1              0          2         0       null              null                null
10         1              0          2         1       null              null                null
11         1              0          2         2       null              null                null
12         1              0          2         3       null              null                null
13         1              0          2         4       null              null                null
14         1              0          2         8       null              null                null
15         1              0          2         9       null              null                null
16         3              0          2         34      null              null                null
17         4              0          2         540     null              34                  null
18         1              0          1         0       null              null                null
19         1              0          1         1       null              null                null
20         3              0          1         15      null              null                null

考虑到不同的客户数据，例如下面的数据，我需要提取符合条件的最高优先级规则。我将在下面给你举一个例子。

让我们考虑以下客户数据阵列：

Array
(
[customer] => 44
[customer_group] => 1
[store_id] => 0
[country] => 15
[subdivision_one] => 421
[subdivision_two] => 
)

应该考虑的最高优先级规则是#1。原因如下：

• 在分配给该规则的所有客户组中，customer_group=1是匹配的
• 国家正在匹配
• 即使我传递customer和subdivision_one，这个规则也是匹配的，因为对于这两个属性，我根本没有值。如果我有一个customer/subdivision_one的值，它不应该匹配

我有一个部分有效的查询，只有当查询中涉及的所有属性都匹配时才会给出结果。

但是，

我需要调整这个查询，以便在有可用记录(如查询中(或没有可用于该属性的值的情况下，它会将属性视为匹配以下是查询：

SELECT `r`.*
FROM `rule_attribute_value` `rav`
INNER JOIN `rule` `r`
ON `rav`.`rule_id` = `r`.`rule_id`
INNER JOIN `rule_attribute` `ra`
ON `rav`.`attribute_id` = `ra`.`attribute_id`
WHERE
(`rav`.`store_id` = 0 AND `ra`.`attribute_code` = 'customer' AND `rav`.`value` = 44) OR
(`rav`.`store_id` = 0 AND `ra`.`attribute_code` = 'customer_group'  AND `rav`.`value` = 1) OR
(`rav`.`store_id` = 0 AND `ra`.`attribute_code` = 'country' AND `rav`.`value` = 15) OR
(`rav`.`store_id` = 0 AND `ra`.`attribute_code` = 'subdivision_one' AND `rav`.`value` = 421)
GROUP BY `r`.`rule_id`
HAVING COUNT(DISTINCT `rav`.`attribute_id`) = 4 /* NUMBER OF ATTRIBUTES QUERIED */
ORDER BY `r`.`position` ASC
LIMIT 1;

每次都会发送不同数量的客户属性。有时所有的6都有非空值，有时是5，有时是4。我只需要找到符合条件的最高优先级规则。

期待您的回复。

Answer 1

我仍在为这个查询寻找最优化的解决方案，但我认为我已经找到了更好的方法来解释我的问题。

让我们考虑一下，我只想找到与一个属性(客户(匹配的规则。有三种情况：

• 如果一个规则具有我要查找的值的此属性，则返回该规则
• 如果一个规则具有此属性，但其值与我要查找的值不同，则不返回任何内容
• 如果规则根本没有此属性，则返回该规则

这是我尝试执行的查询，但它没有按预期工作。但是，OR语法之后的子查询可能有助于更好地理解我要查找的内容。

SELECT `r`.*
FROM `rule_attribute_value` `rav`
INNER JOIN `rule` `r`
ON `rav`.`rule_id` = `r`.`rule_id`
INNER JOIN `rule_attribute` `ra`
ON `rav`.`attribute_id` = `ra`.`attribute_id`
WHERE
(`rav`.`store_id` = 0 AND `ra`.`attribute_code` = 'customer'        AND `rav`.`value` = 2) OR (NOT EXISTS(SELECT * FROM `coriolan_b2bvariables_rule_attribute_value` `ravs` WHERE `ravs`.`rule_id` = `rav`.`rule_id` AND `rav`.`store_id` = 0 AND `ra`.`attribute_code` = 'customer'))
GROUP BY `r`.`rule_id`
HAVING COUNT(DISTINCT `rav`.`attribute_id`) = 1 /* 1 IS THE NUMBER OF ATTRIBUTES BEING QUERIED */
ORDER BY `r`.`position` ASC
LIMIT 1;

这是一个只有一个属性的示例，但我总共有五个属性。然而，我觉得如果我能找到一个属性的解决方案，我可以做剩下的。