php - MySQL JOIN 许多表将条件列表获取到一个表中

Question

我想弄清楚如何使用 ONE table JOIN 来获取车辆 MAKE、MODEL、YEAR 和 TRIMS 标准的列表，供客户搜索。

已经有了主 key 表，管理员可以从中选择一系列车辆选项，并将这些与该产品相关的车辆详细信息输入到PRODUCT表中。

我现在想为购物者生成一个列表，该列表仅反射(reflect)可用的车辆详细信息选择 - 基于管理员输入到 PRODUCTS 表中的内容。

我一直在使用 PHP 循环/迭代 MAKE MODEL TRIMS 表，并在 PRODUCTS 表中搜索 PRODUCTS 表中是否存在 MAKE MODEL YEAR TRIM 类型。但它对 PRODUCTS 表进行了大约 800 次单独调用。

据了解，这不是最佳做法，可能会导致各种问题 - 成为对数据库的多次调用的方式，而且效率不高。

我在另一个问题中被告知

https://stackoverflow.com/questions/13960571/sanity-check-mysql-whats-reasonable-800-calls-to-the-database-in-one-second

这可以通过使用 JOIN 和 WHERE 语句的一次调用来完成。

我以前使用过表 JOINS，但看不出如何通过一次调用这么多 MAKES、MODELS、YEARS、TRIMS 来生成一份可用的 MAKES、MODELS、YEARS、TRIMS 标准列表，供购物者使用从中选择。

如果我能从你的例子中学到任何关于这方面的知识，我将不胜感激:)

以下是用于将车辆相关详细信息添加到产品条目记录的管理员主 key 选择表的示例:

---

表:制作

| Id | MAKE      | // Admin table for selecting products related vehicle make 
------------------
| 1  | FORD      |
| 2  | CHEV      |
| 3  | GMC       |
| 4  | HONDA     |
etc.

表:福特

| Id | MODEL     | // Admin table for selecting products related vehicle model 
------------------
| 1  | F150      |
| 2  | ESCAPE    |
| 2  | EXPLORER  |
etc.

表:FORD_F150_YEARS_TRIMS

| Id | YEARS|                  TRIMS                      |  // Admin table for selecting products related vehicle year and trim(s)
--------------------------------------------
| 1  | 1999 | 1999_SPORT+1999_SPORTRAC+1999_XLT+1999_XLS  |
| 2  | 2000 | 2000_XLT+2000_XLS+2000_LTD+2000_EDDIE_BAUER |
| 3  | 2001 | 2001_SPORTRAC+2001_XLT+2001_LTD             |
etc.

---

这是管理员输入产品/车辆详细信息的产品表:

表:产品

| PRODUCT_ID  | MAKE          |          MODELS         |      YEARS       |                                     TRIMS                                               |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 123456      | FORD FORD GMC |   F150 ESCAPE CANYON    |  2000 2001 1999  | FORD_F150_1999_SPORT+FORD_F150_1999_SPORTRAC+GMC_CANYON_1999_LTD+GMC_CANYON_1999_LTD    |
| 123457      | FORD GMC CHEV | F150 EXPLORER SILVERADO |  2000 2010 2010  | FORD_F150_2001_XLT+FORD_F150_2001_LTD+GMC_CANYON_2010_XLT+CHEV_SILVERADO_1500_2010_LTD  |
etc.

我想做的是 - 在 PRODUCTS 表上进行查询，我可以在其中生成一个表或列表，其中仅包含有产品的车辆类型。

因此，如果 PRODUCTS 表中没有适合 2001 FORD F150 和 SPORTRAC 修剪 - 那么我不想让购物者选择 SPORTRAC 和 2001 FORD F150 但我确实想让他们选择 2001 FORD F150 带 XLT + LTD

真的 - 我只是想消除购物者对不存在的产品车辆详细信息的选择。

有人告诉我，这可以在对数据库的一次 MySQL 调用中完成。有人告诉我，与其循环遍历所有品牌、型号 和装饰 并单独调用 PRODUCTS 表，我可以以某种方式使用表连接和 while 语句来获得仅根据 PRODUCTS 表中的内容为客户提供的所有潜在MAKE MODEL TRIMS选项列表

我知道如何通过调用 PRODUCTS 表然后循环遍历并使用 PHP 清除结果中的重复项来实现这一点。但是有数以千计的产品，而且这些产品可能会增长 - 所以我正在寻找实现这一目标的最佳实践方法。

Answer 1

好吧，您的首要问题似乎是您拥有这些不同的表，但您没有以关系方式使用它们。您真的应该花一些时间学习如何正确规范化您的表格。作为一般准则，您应该真正考虑现实世界中的项目/属性如何相互关联，并通过正确使用主键和外键来表达这种关系。

您的产品应通过各种主键 ID 与品牌、型号、装饰等相关联，而不是通过复制产品表中的数据。例如，您也不应该有一个“Ford”表，而应该只有一个包含“models”的表。

作为示例，我可能有这样的架构

models
---------
model_id
make_id
model

makes (Make is really just a property of the model of car, and could possibly be de-normalized into models table. Here I am showing it as separate table to show a fully normalized example.)
---------
make_id
make

trims ('SPORT+XLS' unless those represent a specific trim combination.  Each different trim package should have its own row)
--------
trim_id
trim

products (I am assuming that a model and year define a product, by looking at your example data)
--------
product_id
year
model_id


product_trims (many-to-many table expressing relation of products to trims - you could have multiple rows with same product_id and different trim id)
-------------
product_id
trim_id

如果您真的想将产品定义为年份、型号和装饰的组合，您可以删除 product_trims 表，只需要像这样修改后的产品表

product
-------------
product_id
year
model_id
trim_id

然后您可以跨联接查询以获取所需的数据。例如，假设用户指定了型号和年份。查询可能如下所示(假设同时使用 products 和 products_trim 表)

SELECT p.product_id, p.year, ma.make, mo.model, t.trim
FROM
products AS p
INNER JOIN models AS mo ON p.model_id = mo.model_id
INNER JOIN makes AS ma ON mo.make_id = ma.make_id
INNER JOIN product_trims AS pt ON p.product_id = pt.product_id
INNER JOIN trims AS t ON pt.trim_id = t.trim_id
WHERE p.year = '?' AND p.model_id = ?

当然，您需要正确索引所有用于连接和任何 WHERE 或 ORDER BY 条件的文件。