Creating complex IF, OR, NOT & AND SQL queries (MySQL)

(Apologies if this a duplicate - I have tried searching, but I may not know the right word for what I'm trying to achieve - feel free to correct me!)

（如果这是重复的道歉 - 我已经尝试过搜索，但我可能不知道我想要实现的正确的词 - 随意纠正我！）

The Background

So I have a PHP based app (Codeigniter, but I'm using normal SQL language for this part), that has a MySQL database, with 2 tables - 'contact' and 'order'.

所以我有一个基于PHP的应用程序（Codeigniter，但我正在使用这部分的普通SQL语言），它有一个MySQL数据库，有2个表 - “联系人”和“订单”。

For simplicity, let's assume that:

为简单起见，我们假设：

'contact' has 3 cols : Id, FirstName, LastName
'contact'有3个cols：Id，FirstName，LastName
'order' has 4 cols : Id, ContactId, ItemBought, ItemValidDate
'order'有4个cols：Id，ContactId，ItemBought，ItemValidDate
Example of a row in 'order' table: 22, 11, Adult Membership, 2012/13
'order'表中行的示例：22,11，Adult Membership，2012/13
Id is primary key of both tables, ContactId is foreign key for 'contact' and ItemBought and ItemValidDate are both simple varchar (we're storing 'seasons' rather than dates -I know, its not ideal but its what the client wants)
Id是两个表的主键，ContactId是'contact'的外键，而ItemBought和ItemValidDate都是简单的varchar（我们存储'季节'而不是日期 - 我知道，它不理想，但它是客户想要的）

At some point, I know, I am going to have to extend this for 3 tables and use an OrderItem table, to allow an order to have multiple items, so I'd like to find a solution that can be built on. But at present, I don't even understand the basics so I've kept it to 2 tables

在某些时候，我知道，我将不得不为3个表扩展这个并使用OrderItem表，以允许订单有多个项目，所以我想找到一个可以构建的解决方案。但目前，我甚至不了解基础知识，所以我把它保存到2桌

The Problem

I want to create a search page that allows the user to find subsets of records based on lots of different criteria.

我想创建一个搜索页面，允许用户根据许多不同的标准查找记录子集。

See screenshot of search page

查看搜索页面的屏幕截图

This form submits as an array of criteria like this:

此表单提交为一系列标准，如下所示：

[order_type_operator] => Array
    (
        [0] => equal
        [1] => equalor
        [2] => notequal
    )

[order_type] => Array
    (
        [0] => Adult Membership
        [1] => Adult Membership
        [2] => Adult Membership
    )

[order_expire] => Array
    (
        [0] => 2005/06
        [1] => 2006/07
        [2] => 2010/11
    )

[submit] => Start Search

I then cycle through this array, testing to see if values have been submitted, and build up my SQL query.

然后我遍历这个数组，测试是否已提交值，并构建我的SQL查询。

So, I hope I've explained it properly, so that its clear a user may use this form to search for records that match lots of different conditions - in theory, unlimited numbers of conditions - to end up with a list of contacts that match this criteria.

因此，我希望我已经正确地解释了它，以便明确用户可以使用此表单来搜索匹配许多不同条件的记录 - 理论上，无限数量的条件 - 最终得到匹配的联系人列表这个标准。

What I have Tried

Example 1 - simple WHERE

"find contact records that have an order record for 'Adult Membership' in '2009/10'"
“查找在'2009/10'中有'成人会员'订单记录的联系人记录”
i.e. SELECT * FROM contact JOIN order ON contact.Id = order.ContactId WHERE (order.ItemBought = 'Adult Membership' AND order.ItemValidDate = '2009/10')
即SELECT * FROM联系JOIN订单ON contact.Id = order.ContactId WHERE（order.ItemBought ='Adult Membership'AND order.ItemValidDate ='2009/10'）

This works fine.

这很好用。

Example 2 - WHERE OR WHERE

"find contact records that have an order record for 'Adult Membership' in '2009/10'" OR have a an order record for 'Adult Membership' in '2010/10'
“查找在'2009/10''中有'成人会员'订单记录的联系人记录”或在'2010/10'中找到'成人会员'的订单记录
i.e. SELECT * FROM contact JOIN order ON contact.Id = order.ContactId WHERE (order.ItemBought = 'Adult Membership' AND order.ItemValidDate = '2009/10') OR (order.ItemBought = 'Adult Membership' AND order.ItemValidDate = '2010/11')
ie SELECT * FROM contact JOIN order ON contact.Id = order.ContactId WHERE（order.ItemBought ='Adult Membership'AND order.ItemValidDate ='2009/10'）OR（order.ItemBought ='Adult Membership'AND order.ItemValidDate ='2010/11'）

This works fine as long as EVERY condition the user is asking for is an OR query. I assume that I can build this query up using brackets and OR for as big as I like? E.g. find Adult membership in 2005/06, OR 2006/07, OR 2007/08, OR 2008/09 etc etc will be just like the above SQL with lots more brackets joined by 'OR'?

只要用户要求的每个条件都是OR查询，这样就可以正常工作。我假设我可以使用括号和OR构建此查询，因为我喜欢这么大？例如。在2005/06年度，或2006/07年度，或2007/08年度，或2008/09年度等中找到成人会员资格，就像上面的SQL一样，有更多括号加'OR'？

Example 3 - WHERE AND WHERE - I'm stuck!

"find contact records that have an order record for 'Adult Membership' in '2009/10' OR 2010/11 AND have a an order record for 'Adult Membership' in '2012/13'
“查找”2009/10“或2010/11年度”成人会员“订单记录的联系人记录，并在”2012/13“中有”成人会员“的订单记录

At the moment, I've been trying UNION, however if there are more queries to follow this (e.g Adult membership in 2008 OR 2009 AND 2010) this means doing more than one SELECT. (Perhaps this is the answer?)

目前，我一直在尝试UNION，但是如果有更多的查询要遵循这一点（例如2008年或2009年和2010年的成人会员资格），这意味着要做多个SELECT。（也许这就是答案？）

e.g. `SELECT * FROM contact JOIN order ON contact.Id = order.ContactId WHERE (order.ItemBought = 'Adult Membership' AND order.ItemValidDate = '2009/10') OR (order.ItemBought = 'Adult Membership' AND order.ItemValidDate = '2010/11')

例如`SELECT * FROM contact JOIN order ON contact.Id = order.ContactId WHERE（order.ItemBought ='Adult Membership'AND order.ItemValidDate ='2009/10'）OR（order.ItemBought ='Adult Membership'AND order.ItemValidDate ='2010/11'）

UNION

SELECT * FROM contact
JOIN order ON contact.Id = order.ContactId WHERE (order.ItemBought = 'Adult Membership' AND order.ItemValidDate = '2012/13)`

Example 4 - But does NOT have a record.... Blows my mind

"find contact records that have an order record for 'Adult Membership' in '2009/10' AND have an order record for 'Adult Membership' in '2010/10' BUT DO NOT have an order of 'Sponsorship' in 2007/08
“查找在'2009/10'中有'成人会员'订单记录的联系人记录，并在'2010/10'中有'成人会员'的订单记录但是在2007/08年度没有订购'赞助'

I wondered about running these queries, storing the results in a PHP array and then doing a IN (*array of ids already selected*), but this just seems like I'm not using SQL properly.

我想知道运行这些查询，将结果存储在PHP数组中，然后执行IN（*已经选择*的数组*），但这似乎我没有正确使用SQL。

So clever people - what am I doing wrong?

这么聪明的人 - 我做错了什么？

Thank you so much in advance for you help.

非常感谢你提前帮助。

PS. Not asking you write the code for me!

PS。不要求你为我写代码！

PPS. If you know of any good tutorials then I'll happily follow them!

PPS。如果你知道任何好的教程，那么我很乐意跟着他们！

PPPS. If this is a duplicate, then please accept my apologies!

购买力平价。如果这是重复，那么请接受我的道歉！

3 个解决方案

#1

As ZorleQ says it can rapidly get to be a mess

正如ZorleQ所说，它很快就会变得一团糟

For your 3rd question a possible solution using joins of subselects would be as follows

对于第3个问题，使用子选择连接的可能解决方案如下

SELECT contact.*, order.*
FROM contact
INNER JOIN order
ON contact.Id = order.ContactId
INNER JOIN (SELECT DISTINCT ContactId 
            FROM order 
            WHERE (ItemBought = 'Adult Membership' AND ItemValidDate = '2009/10') 
            OR (ItemBought = 'Adult Membership' AND ItemValidDate = '2010/11')) Sub1
ON contact.Id = Sub1.ContactId
INNER JOIN (SELECT DISTINCT ContactId
            FROM order 
            WHERE (ItemBought = 'Adult Membership' AND ItemValidDate = '2012/13')) Sub2
ON contact.Id = Sub2.ContactId

You could probably do this without using the subselects and just a plain join as follows

您可以在不使用子选择和仅使用普通连接的情况下执行此操作，如下所示

SELECT contact.*, order.*
FROM contact
INNER JOIN order
ON contact.Id = order.ContactId
LEFT OUTER JOIN order Sub1
ON contact.Id = Sub1.ContactId AND Sub1.ItemBought = 'Adult Membership' AND Sub1.ItemValidDate = '2009/10'
LEFT OUTER JOIN order Sub2
ON contact.Id = Sub2.ContactId AND Sub2.ItemBought = 'Adult Membership' AND Sub2.ItemValidDate = '2010/11'
INNER JOIN order  Sub3
ON contact.Id = Sub3.ContactId AND Sub3.ItemBought = 'Adult Membership' AND Sub3.ItemValidDate = '2012/13'
WHERE Sub1.ContactId IS NOT NULL OR Sub2 IS NOT NULL

Your 4th question can be done using a LEFT OUTER JOIN to find a record with Sponsorship bought for 2007/08, and only returning rows where a match isn't found (ie, check the ContactId on the LEFT OUTER JOINed table is NULL).

您的第4个问题可以使用LEFT OUTER JOIN来查找2007/08购买赞助商的记录，并且仅返回未找到匹配项的行（即，检查LEFT OUTER JOINed表上的ContactId是否为NULL）。

SELECT contact.*, order.*
FROM contact
INNER JOIN order
ON contact.Id = order.ContactId
INNER JOIN order Sub1
ON contact.Id = Sub1.ContactId AND Sub1.ItemBought = 'Adult Membership' AND Sub1.ItemValidDate = '2009/10'
INNER JOIN order Sub2
ON contact.Id = Sub2.ContactId AND Sub2.ItemBought = 'Adult Membership' AND Sub2.ItemValidDate = '2010/10'
LEFT OUTER JOIN order Sub3
ON contact.Id = Sub3.ContactId AND Sub3.ItemBought = 'Sponsorship' AND Sub3.ItemValidDate = '2007/08'
WHERE Sub3.ContactId IS NULL

#2

I think you have to take a step back and try to visualize your question on paper first. Examples 1 and 2 are pretty easy, but let's look at example 3.

我认为你必须退后一步，首先尝试在纸上形象化你的问题。示例1和2非常简单，但让我们看一下示例3。

For conditions where all your criteria are 'AND' or 'OR' - things are very simple. Just do a long WHERE, just liek before. However, when you start mixing them you have to answer yourself a serious question:

对于所有标准都是“AND”或“OR”的情况 - 事情非常简单。做一个很长的时间，只是先说谎。但是，当你开始混合它们时，你必须回答一个严肃的问题：

How do you split the conditions?

你如何分割条件？

Lets say someone picked up those criteria:

让我们说有人选择了这些标准：

and A
和A.
or B
或B.
and C
和C.

This gives you so many permutations of your query! eg:

这为您提供了如此多的查询排列！例如：

(A or B) and C
（A或B）和C.
A or (B and C)
A或（B和C）
(A and C) or B
（A和C）或B.

If you add one more 'OR' to it, you will end it with tens of combinations more! Leaving you in a place where you have to guess what to do. Don't even want to think what would happen if there is a NOT involved...

如果你再添加一个“OR”，你将会结束数十种组合！让你离开你必须猜测该做什么的地方。甚至不想想如果没有涉及会发生什么......

This is not a direct answer to your question, but more of a pointer towards a possible solution. The last time we had to do something similar, we've ended up grouping the conditions together into blocks.

这不是您问题的直接答案，而是更多指向可能解决方案的指针。上次我们不得不做类似的事情时，我们最终将这些条件组合成块。

You could either add a condition within a block or add a new search block. Think of the blocks as brackets in the example above. Everything in a block is an 'AND' or 'NOT AND', and between blocks you can specify 'and' or 'or'. This way you know straight away how to structure your query. This worked like a charm in a standalone application. Might be a bit tricky to implement it nicely on a page, but you catch the idea.

您可以在块中添加条件或添加新的搜索块。在上面的示例中将块视为括号。块中的所有内容都是“AND”或“NOT AND”，块之间可以指定“和”或“或”。通过这种方式，您可以立即知道如何构建查询。这在独立应用程序中就像一个魅力。在页面上很好地实现它可能有点棘手，但你抓住了这个想法。

#3

My solution to all issues like this where multiple criteria may or may not be provided by the user is the following... (this example is for oracle, but should be able to be done in MySQL as well)...

对于像这样的所有问题的解决方案，用户可能会或可能不会提供多个标准，如下所示...（此示例适用于oracle，但也应该能够在MySQL中完成）...

You pass in all the filter variables, regardless of whether they are null or filled with a value. In this example, I'll say I have 3 values the user may or may not fill that act as filters on the SELECT.

传入所有过滤器变量，无论它们是否为空或填充了值。在这个例子中，我会说我有3个值，用户可能会或可能不会填充SELECT作为过滤器。

SELECT
   *
FROM
    table
WHERE
    NVL2(InputVariable1, InputVariable1, Column1) = Column1
OR  NVL2(InputVariable2, InputVariable2, Column2) = Column2 
OR  NVL2(InputVariable3, InputVariable3, Column3) = Column3

NVL2 - This is an oracle function. If the first value is not null, it returns the second value, otherwise it returns the third value. If you aren't using oracle, and there is no equivalent function for NVL2, simply write the function yourself.

NVL2 - 这是一个oracle函数。如果第一个值不为null，则返回第二个值，否则返回第三个值。如果您没有使用oracle，并且没有NVL2的等效功能，那么只需自己编写该功能即可。

So, using the above example, the code ALWAYS passes all three InputVariables into the select statement, even if they are NULL. By using NVL2 or an equivalent function, the comparison is between the InputVariable and the Column ONLY if the InputVariable is not null; otherwise it is between the Column and the Column, which will of course always be true, thereby effectively ignoring that filter variable, which is what you want (i.e. a null filter value matches all rows - i.e. if user does not specify LastName, then include all LastNames).

因此，使用上面的示例，代码ALWAYS将所有三个InputVariables传递给select语句，即使它们是NULL。通过使用NVL2或等效函数，如果InputVariable不为null，则比较在InputVariable和Column ONLY之间;否则它在Column和Column之间，当然总是如此，从而有效地忽略了你想要的过滤器变量（即空过滤器值匹配所有行 - 即如果用户没有指定LastName，那么包括所有LastNames）。

This solution allows you to use many filter variables without having to do a lot of processing up front - just pass them all down into the SELECT every time, whether they are null or not.

这个解决方案允许您使用许多过滤器变量，而无需事先进行大量处理 - 每次都将它们全部传递到SELECT中，无论它们是否为空。

If you have sets of filter variables (i.e. the user enables a set of input values via a checkbox or some similar mechanism), you can do the above inside of a CASE statement. Each case should check the enable value for a given set, and return the result of evaluating the entire set of filter variables (exactly like the above). You then compare the result of the entire CASE structure to 1, as in...

如果您有多组过滤器变量（即用户通过复选框或某种类似的机制启用一组输入值），您可以在CASE语句中执行上述操作。每种情况都应检查给定集的启用值，并返回评估整个过滤器变量集的结果（与上面完全相同）。然后，将整个CASE结构的结果与1进行比较，如...

WHERE

CASE [ expression ]

    WHEN enableSet1 
    THEN     NVL2(InputVariable1, InputVariable1, Column1) = Column1
         OR  NVL2(InputVariable2, InputVariable2, Column2) = Column2 
         OR  NVL2(InputVariable3, InputVariable3, Column3) = Column3

   WHEN condition_2 THEN result_2
   ...
   WHEN condition_n THEN result_n

END = 1

This works because the value of a CASE structure is the result of the THEN block which was evaluated.

这是有效的，因为CASE结构的值是被评估的THEN块的结果。

This will allow you to do ALL or MOST of your desired filtering within the confines of a single SELECT statement - again, without having to do a lot of pre-processing to build the SELECT.

这将允许您在单个SELECT语句的范围内完成所需或大部分所需的过滤 - 同样，无需进行大量预处理来构建SELECT。

#1