对遍布多个表的数据进行复杂的MySQL查询（PHP）

I am currently trying to solve a complex problem with MySQL and PHP.

我目前正在尝试解决MySQL和PHP的复杂问题。

Here is an example of the tables I have:

这是我的表格示例:

List of clients:

客户名单:

table_clients
Client_ID | Client_Name | Address         | Zip Code |
----------|-------------|-----------------|----------|
1         | Mark        | 127 Park Ave    | 12235    |
2         | John        | 6 Freeman Drive | 12899    |
3         | Allan       | 450 Clever Rd   | 12235    |

List of services:

服务清单:

table_services
Service_ID | Service_Name | Service_Price |
-----------|--------------|---------------|
1          | Fertilizer   | 100.00        |
2          | Bug Spray    | 50.00         |
3          | Seeds        | 20.00         |

Next table stores which client has which services (one or more), the status of the service and the date it was done, if applicable:

下表存储哪个客户端具有哪些服务(一个或多个),服务状态及其完成日期(如果适用):

table_jobs
Job_ID | Client_ID | Service_ID | Status | Date_Done  |
-------|-----------|------------|--------|------------|
1      | 1         | 1          | done   | 2013-05-01 |
2      | 1         | 3          | active | NULL       |
3      | 2         | 1          | active | NULL       |
4      | 2         | 2          | active | NULL       |
5      | 3         | 1          | active | NULL       |
6      | 3         | 3          | active | NULL       |

Now comes the tricky part. Some services need to have a certain time difference with others. For example, one client can't receive seeds if he received fertilizer in the last 30 days. To keep track of this, I have a third table with the information:

现在是棘手的部分。有些服务需要与其他服务有一定的时差。例如,如果一个客户在过去30天内收到肥料,则无法收到种子。为了跟踪这一点,我有第三个表格,其中包含以下信息:

table_time_difference
Service_ID_1 | Service_ID_2 | Time_Diff |
-------------|--------------|-----------|
1            | 3            | 30d       |
1            | 4            | 7d        |
2            | 4            | 14d       |
4            | 5            | 14d       |

Now that everything is stored in database (keep in mind there can be dozens of services and thousands of clients), I am trying to get the rows of clients that have certain services or not, while always respecting the time difference.

现在一切都存储在数据库中(请记住,可以有数十种服务和数千个客户端),我试图获得具有某些服务或不具有某些服务的客户端,同时始终尊重时差。

For example:

I want all the client that are due to receive Fertilizer, should return:

我希望所有应该收到Fertilizer的客户都应该返回:

Client_ID | Client_Name | Zip Code | Job_ID | Service_ID | Service_Name |
----------|-------------|----------|--------|------------|--------------|
2         | John        | 12235    | 3      | 1          | Fertilizer   |
3         | Allan       | 12145    | 5      | 1          | Fertilizer   |

Now if I want to do all clients that are due to receive Fertilizer AND Bug Spray:

现在,如果我想要做所有接受肥料和虫子喷雾的客户:

Client_ID | Client_Name | Zip Code | Job_ID | Service_ID | Service_Name |
----------|-------------|----------|--------|------------|--------------|
2         | John        | 12235    | 3      | 1          | Fertilizer   |
2         | John        | 12235    | 4      | 2          | Bug Spray    |

And if I want to do all clients that are due to receive seeds in ZIP code 12235:

如果我想要在邮政编码12235中接收所有要接收种子的客户:

Client_ID | Client_Name | Zip Code | Job_ID | Service_ID | Service_Name |
----------|-------------|----------|--------|------------|--------------|
3         | Allan       | 12235    | 6      | 3          | Fertilizer   |

Notice how Mark isn't included as he doesn't meet the 30 days requirements since the last fertilizer service.

请注意Mark不包括在内,因为他不满足自上次肥料服务以来的30天要求。

I have tried many different options with all sorts of JOINS, but never found a solution that would work like described. The closest I have gotten is by generating sub-queries with PHP and them joining them in a big query.

我已经尝试了各种各样的JOINS的不同选项,但从来没有找到一个像所描述的那样工作的解决方案。我得到的最接近的是通过PHP生成子查询,并在一个大查询中加入它们。

For example, one of my attempts looked like this (for the last expected result above):

例如,我的一次尝试看起来像这样(对于上面的最后一个预期结果):

SELECT c.Client_ID,
       c.Client_Name,
       c.Zip_Code,
       j.Job_ID,
       s.Service_ID,
       s.Service_Name
FROM clients c
LEFT JOIN jobs j
    ON j.Client_ID = c.Client_ID
LEFT JOIN services s
    ON s.Service_ID = j.Service_ID
WHERE s.Service_ID = "1"
   && c.Zip_Code = "12235"
   && c.Client_ID NOT IN (
                          SELECT Client_ID
                          FROM jobs
                          WHERE Status = "done"
                             && Date_Done < (UNIX_TIMESTAMP() - 2592000)
                         )

• Note that the subquery has been generated by a PHP script that does a lookup for restrictions corresponding to the service requested and the minimum time difference for that service since there can be multiple restriction for the same service and I don't know if I can do that in pure SQL.

请注意,子查询是由PHP脚本生成的,该脚本执行查找与所请求的服务相对应的限制以及该服务的最小时间差,因为对同一服务可能有多个限制,我不知道我是否可以执行在纯SQL中。

Now, the query shown above does work for that exact scenario (Although it is very slow), it breaks and I haven't been able to adapt it to fit my other needs (Multiple services that are included or excluded).

现在,上面显示的查询确实适用于那个确切的场景(虽然它非常慢),它会中断并且我无法使其适应我的其他需求(包含或排除的多个服务)。

Tell me if you need any other information or if you are open to discussing it further.

如果您需要任何其他信息或者您愿意进一步讨论,请告诉我。

Thank you very much to everyone who has read through the whole question (very long) and I hope that some of you understand my needs and can help me!

非常感谢所有阅读了整个问题的人(很长一段时间),我希望你们中的一些人了解我的需求并帮助我!

2 个解决方案

#1

The following may be of help:

以下可能有所帮助:

This will pull the outstanding orders (without restrictions)

这将拉动未完成的订单(没有限制)

SELECT *
FROM table_jobs AS T_job, table_services AS T_ser, table_clients AS T_cli
WHERE T_job.Client_ID=T_cli.Client_ID
AND T_job.Service_ID=T_ser.Service_ID
AND T_job.Status='active'

This should pull order that have previously done orders with restrictions BUT Time_Diff should be in Days (ie remove the d)

这应该拉出先前已经完成订单的订单但是Time_Diff应该是Days(即删除d)

SELECT *
FROM (table_jobs AS T_job, table_services AS T_ser, table_clients AS T_cli)
LEFT JOIN (table_time_difference AS T_dif, table_jobs AS T_ojobs)
ON (
    AND T_job.Service_ID=T_dif.Service_ID_1
    AND T_dif.Service_ID_2=T_ojob.Service_ID
    AND T_ojobs.Date_Done > DATE_SUB(CURDATE(), INTERVAL T_dif.Time_Diff DAY)
    AND T_ojobs.Status='done'
)
WHERE T_job.Client_ID=T_cli.Client_ID
AND T_job.Service_ID=T_ser.Service_ID
AND T_job.Status='active'
AND T_ojobs.Job_ID IS NULL

You can then add your additional parameters for bug spray or zip code at the end, using T_job, T_ser or T_cli as the table names. (ie not T_ojobs or T_dif)

然后,您可以使用T_job,T_ser或T_cli作为表名,在最后添加bug喷涂或邮政编码的附加参数。 (即不是T_ojobs或T_dif)

#2

I have found the answer to my question, thanks to the help of Waygood who helped me understand the different uses of the ON clause of the LEFT JOIN.

我找到了问题的答案,感谢Waygood的帮助,他帮助我理解了LEFT JOIN的ON子句的不同用法。

Here is the query that fulfils all the needs I enumerated:

这是满足我枚举的所有需求的查询:

# Select desired columns

SELECT j.`Job_ID`,
       j.`Client_ID`,
       c.`Name`,
       j.`Service_ID`,
       s.`Service_Name`,
FROM (
      jobs j,
      clients c,
      Services s
      )

# Use LEFT JOIN to filter out services that meet the constraints

LEFT JOIN (
           time_difference diff,
           jobs j2
          )
ON (
    j.`Service_ID` = diff.`Service_ID_1`
    && j.`Client_ID` = j2.`Client_ID`
    && diff.`Service_ID_2` = j2.`Service_ID`
    && j2.`Date_Done` > (UNIX_TIMESTAMP() - diff.`Time_Diff`)
    && j2.`Status` = 'done'
)

# Use LEFT JOIN to filter out jobs that have specific restrictions

LEFT JOIN jobs_constraints jconst
ON (
    j.`id` = jconst.`Job_ID`
    && jconst.`value` > UNIX_TIMESTAMP()
)

#Add one LEFT JOIN for every service that a client must or must not have

LEFT JOIN jobs j3
ON (
    j.`Client_ID` = j3.`Client_ID`
    && j3.`Status` = "active"
    && j3.`Service_ID` = "1"
)
LEFT JOIN jobs j4
ON (
    j.`Client_ID` = j4.`Client_ID`
    && j4.`Status` = "active"
    && j4.`Service_ID` = "2"
)
LEFT JOIN jobs j5
ON (
    j.`Client_ID` = j5.`Client_ID`
    && j5.`Status` = "active"
    && j5.`Service_ID` = "3"
)

# END automatically generated blocks

WHERE j.`Status` = "active"
   && j2.`Job_ID` IS NULL
   && j.`Client_ID` = c.`Client_ID`
   && j.`Service_ID` = s.`Service_ID`
   && jconst.`id` IS NULL

   # Add one AND clause for every service that a client must
   # or must not have use IS NULL if client must not have service
   # or IS NOT NULL if client must have

   && j3.`Job_ID` IS NULL
   && j4.`Job_ID` IS NOT NULL
   && j5.`Job_ID` IS NOT NULL

This query still needs a bit of PHP to generate but it is pretty simple and there are only 2 areas that need to be adjusted on the fly.

这个查询仍然需要一些PHP来生成,但它非常简单,只有2个区域需要动态调整。

I've added a few other parameters since my question to allow more control, but it still revolves around the same idea of using multiple parameters in the ON clause of the LEFT JOIN I am doing.

自从我的问题以来我添加了一些其他参数以允许更多控制,但它仍然围绕在我正在进行的LEFT JOIN的ON子句中使用多个参数的相同想法。

Thanks again Waygood for your help.

再次感谢Waygood的帮助。