SELECT，SELECT COUNT和交叉引用表可以仅通过一个查询来处理吗? [英] Can SELECT, SELECT COUNT and cross reference tables be handled by just one 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. 零件编号(x的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是不可能的，因为照片 表格中有一列，如果照片是非活动的，则指示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

这篇关于SELECT，SELECT COUNT和交叉引用表可以仅通过一个查询来处理吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！