SELECT、SELECT COUNT 和交叉引用表可以仅由一个查询处理吗?

2022-01-23 00:00:00 subquery php mysqli inner-join nested-query

我有一个显示项目列表的页面.每个项目都会显示从 mysqli 数据库中检索到的以下数据:

I have a page that displays a list of projects. With each project is displayed the following data retrieved from a mysqli database:

  1. 标题
  2. 副标题
  3. 说明
  4. 部件号(1 个)
  5. 与该项目相关的照片总数
  6. 从项目中随机选择的一张照片
  7. 标签列表

使用分页系统每页显示 6 个项目

Projects are displayed 6 per page using a pagination system

由于这是基于我的一个旧项目，它最初是用草率的代码(我只是在学习，不知道更好)使用许多查询完成的.第三，实际上，仅针对第 5-7 项，它们包含在与分页系统一起使用的 while 循环中.我现在很清楚，这甚至不是正确的经商方式.

As this is based on an old project of mine, it was originally done with sloppy code (I was just learning and did not know any better) using many queries. Three, in fact, just for items 5-7, and those were contained within a while loop that worked with the pagination system. I'm now quite aware that this is not even close to being the right way to do business.

我熟悉 INNER JOIN 和子查询的使用，但我担心仅使用一个选择查询可能无法获取所有这些数据，原因如下:

I am familiar with INNER JOIN and the use of subqueries, but I'm concerned that I may not be able to get all of this data using just one select query for the following reasons:

  • 使用基本的 SELECT 查询，第 1-4 项就足够简单了，但是...

  • Items 1-4 are easy enough with a basic SELECT query, BUT...

第 5 项需要 SELECT COUNT AND...

Item 5 needs a SELECT COUNT AND...

第 6 项需要一个基本的 SELECT 查询和一个 ORDER by RAND LIMIT 1 来从与每个项目相关的所有照片中随机选择一张照片(使用 FilesystemIterator 是不可能的，因为照片table 有一个列，如果照片是非活动的，则表示 0，如果是，则表示 1活跃)

Item 6 needs a basic SELECT query with an ORDER by RAND LIMIT 1 to select one random photo out of all those associated with each project (using FilesystemIterator is out of the question, because the photos table has a column indicating 0 if a photo is inactive and 1 if it is active)

第 7 项是从标签的交叉引用表中选择的，并且项目和包含标签 ID 和名称的表

Item 7 is selected from a cross reference table for the tags and projects and a table containing the tag ID and names

鉴于此，我不确定这一切是否可以(甚至应该就此而言)仅通过一个查询完成，或者是否需要多个查询.我反复阅读过，在一个while循环中嵌套一个或多个查询是多么值得用报纸拍打鼻子.我什至读过多个查询通常是个坏主意.

Given that, I'm not certain if all this can (r even should for that matter) be done with just one query or if it will need more than one query. I have read repeatedly how it is worth a swat on the nose with a newspaper to nest one or more queries inside a while loop. I've even read that multiple queries is, in general, a bad idea.

所以我被困住了.我意识到这可能听起来太笼统了，但我没有任何有效的代码，只有使用 4 个查询来完成这项工作的旧代码，其中 3 个嵌套在 while 循环中.

So I'm stuck. I realize this is likely to sound too general, but I don't have any code that works, just the old code that uses 4 queries to do the job, 3 of which are nested in a while loop.

数据库结构如下.

项目表:

+-------------+---------+----------+---------------+------+
| project_id  | title   | subtitle | description   | part |
|---------------------------------------------------------|
|       1     |   Chevy | Engine   | Modify        |  1   |
|       2     |   Ford  | Trans    | Rebuild       |  1   |
|       3     |   Mopar | Diff     | Swap          |  1   |
+-------------+---------+----------+---------------+------+

照片表:

+----------+------------+--------+
| photo_id | project_id | active |
|--------------------------------|
|     1    |     1      |    1   |
|     2    |     1      |    1   | 
|     3    |     1      |    1   |
|     4    |     2      |    1   |
|     5    |     2      |    1   |
|     6    |     2      |    1   |
|     7    |     3      |    1   |
|     8    |     3      |    1   |
|     9    |     3      |    1   |
+----------+------------+--------+

标签表:

+--------+------------------+
| tag_id |        tag       |
|---------------------------|
|    1   | classic          |
|    2   | new car          |
|    3   | truck            |
|    4   | performance      |
|    5   | easy             |
|    6   | difficult        |
|    7   | hard             |
|    8   | oem              |
|    9   | aftermarket      |
+--------+------------------+

标签/项目交叉引用表:

Tag/Project cross-reference table:

+------------+-----------+
| project_id | tag_id    |
|------------------------|
|      1     |     1     |
|      1     |     3     |
|      1     |     4     |
|      2     |     2     |
|      2     |     5     |
|      3     |     6     |
|      3     |     9     |
+------------+-----------+

我不是要求为我编写代码，但如果我的要求是有道理的，我真诚地感谢你朝着正确的方向前进.很多时候我都在纠结于在线的 PHP 和 MySQLi 手册，所以如果有任何方法可以解决这个问题，那就太棒了.

I'm not asking for the code to be written for me, but if what I'm asking makes sense, I'd sincerely appreciate a shove in the right direction. Often times I struggle with both the PHP and MySQLi manuals online, so if there's any way to break this down, then fantastic.

非常感谢大家.

推荐答案

您可以在 SELECT 子句中执行子查询，如下所示:

You're able to do subqueries inside your SELECT clause, like this: 

SELECT 
    p.title, p.subtitle, p.description, p.part,
    (SELECT COUNT(photo_id) FROM Photos where project_id = p.project_id) as total_photos, 
    (SELECT photo_id FROM Photos where project_id = p.project_id ORDER BY RAND LIMIT 1) as random_photo
FROM projects as p

现在，对于标签列表，由于它返回多行，您不能执行子查询，您应该为每个项目执行一次查询.好吧，实际上，如果您以某种连接形式返回所有标签，例如逗号分隔的列表:tag1,tag2,tag3....仅当您有许多项目并且检索每个单独项目的标签列表的性能相当低时才这样做.如果你真的想要，你可以:

Now, for the list of tags, as it returns more than one row, you can't do a subquery and you should do one query for every project. Well, in fact you can if you return all the tags in some kind of concatenation, like a comma separated list: tag1,tag2,tag3... but I don't recommend this one time that you will need to explode the column value. Do it only if you have many many projects and the performance to retrieve the list of tags for each individual project is fairly low. If you really want, you can:

SELECT 
    p.title, p.subtitle, p.description, p.part,
    (SELECT COUNT(photo_id) FROM Photos where project_id = p.project_id) as total_photos, 
    (SELECT photo_id FROM Photos where project_id = p.project_id ORDER BY RAND LIMIT 1) as random_photo,
    (SELECT GROUP_CONCAT(tag SEPARATOR ', ') FROM tags WHERE tag_id in (SELECT tag_id FROM tagproject WHERE project_id = p.project_id)) as tags
FROM projects as p

相关文章