php - 如何编写一个 mySQL 查询以便在前端组成一个表？
问题描述
第一次问好！
为了成为一名更好的程序员，我决定构建一个在线预订 Web 应用程序。如下图所示，我创建这个数据库时考虑了以下几点。
每个客户都可以预约或要求预约。
i) 预约有固定的时间和日期。
ii) 预约请求没有固定的时间和日期，直到通过后台手动预订。他们必须在两天或更多时间之间至少有两个时间范围，以声明客户何时有空，因此必须在这些时间范围之一内进行预约。
每个约会可能有多个客户（可能最多 3 个）
- 每个客户可能有不止一项服务（这里没有决定限制）
我决定的前端的期望结果如下（也将有分页和ajax，但我还没有）：
这方面的第一次成功尝试根本没有效率，因为我需要为每次约会对数据库运行 4 次查询，所以我立即开始考虑更好的方法，但我浪费了将近两个星期，但我什么也没有。
我不明白我在哪一部分缺少知识 MySQL 或 PHP（忘了提到我正在用 PHP 写这个，但迟到总比没有好）......很可能双方都知道，但我不知道该学什么解决这个问题，而我设法找到了很多其他的东西。
提前致谢！
更新：遵循我使用的代码（包括查询）：
模型
public function getAppointments()
{
$db = \Config\Database::connect();
// get future appointments
$query = $db->query('
SELECT DISTINCT`appointment_id`
FROM `appointments_schedule`
WHERE `datetime_from` > CURRENT_TIMESTAMP
');
$appointments = $query->getResultArray();
return $appointments;
}
public function getAppointmentDateTimes($appointment_id)
{
$db = \Config\Database::connect();
$query = $db->query('
SELECT `datetime_from`, `datetime_to`
FROM `appointments_schedule`
WHERE `appointment_id` = ' . $appointment_id
);
$appointmentDateTimes = $query->getResultArray();
return $appointmentDateTimes;
}
public function getAppointmentCustomers($appointment_id)
{
$db = \Config\Database::connect();
$query = $db->query('
SELECT `id`, `last_name`, `first_name`, customers.status, `is_booking`
FROM customers
INNER JOIN appointment_customer_services ON appointment_customer_services.customer_id = customers.id
WHERE id IN(
SELECT `customer_id`
FROM appointment_customer_services
WHERE appointment_id = ' . $appointment_id . ')
GROUP BY customers.last_name'
);
$appointmentCustomers = $query->getResultArray();
return $appointmentCustomers;
}
public function getCustomerServices($appointment_id, $customer_id)
{
$db = \Config\Database::connect();
$query = $db->query('
SELECT `name` AS `service_name`
FROM services
WHERE id IN(SELECT `service_id`
FROM appointment_customer_services
WHERE `customer_id` =' . $customer_id . '
AND `appointment_id` =' . $appointment_id . ')
');
$CustomerServices = $query->getResultArray();
return $CustomerServices;
}
控制器
$data['appointments'] = [];
foreach ( $model->getAppointments() as $appointment )
{
foreach ( $appointment as $id )
{
foreach ( $model->getAppointmentDateTimes($id) as $key => $timeDates )
{
$data['appointments'][$id]['timeDates'][] = $timeDates;
}
foreach ( $model->getAppointmentCustomers($id) as $appointmentCustomers )
{
foreach ( $model->getCustomerServices($id, $appointmentCustomers['id']) as $services )
{
foreach ( $services as $service )
{
$appointmentCustomers['services'][] = $service;
}
}
$data['appointments'][$id]['customers'][] = $appointmentCustomers;
}
}
}
PRINT_R 输出
Array
(
[1] => Array
(
[timeDates] => Array
(
[0] => Array
(
[datetime_from] => 2020-04-28 14:00:00
[datetime_to] => 2020-04-28 17:00:00
)
[1] => Array
(
[datetime_from] => 2020-05-06 12:00:00
[datetime_to] => 2020-05-06 17:00:00
)
[2] => Array
(
[datetime_from] => 2020-05-30 17:00:00
[datetime_to] => 2020-05-30 20:00:00
)
)
[customers] => Array
(
[0] => Array
(
[id] => 1
[last_name] => Jolie
[first_name] => Angelina
[status] => 1
[is_booking] => 1
[services] => Array
(
[0] => service1
)
)
)
)
[2] => Array
(
[timeDates] => Array
(
[0] => Array
(
[datetime_from] => 2020-05-29 14:00:00
[datetime_to] => 2020-05-29 16:00:00
)
)
[customers] => Array
(
[0] => Array
(
[id] => 2
[last_name] => Lopez
[first_name] => Jennifer
[status] => 1
[is_booking] => 1
[services] => Array
(
[0] => service1
[1] => service2
[2] => service3
)
)
)
)
[3] => Array
(
[timeDates] => Array
(
[0] => Array
(
[datetime_from] => 2020-05-28 15:00:00
[datetime_to] => 2020-05-27 17:00:00
)
)
[customers] => Array
(
[0] => Array
(
[id] => 3
[last_name] => Charlize
[first_name] => Theron
[status] => 1
[is_booking] => 1
[services] => Array
(
[0] => service1
[1] => service2
[2] => service3
)
)
[1] => Array
(
[id] => 4
[last_name] => Bullock
[first_name] => Sandra
[status] => 1
[is_booking] => 0
[services] => Array
(
[0] => Service1
[1] => Service2
)
)
[2] => Array
(
[id] => 5
[last_name] => Aniston
[first_name] => Joe
[status] => 1
[is_booking] => 0
[services] => Array
(
[0] => service1
)
)
)
)
)
VIEW（我用上面的数据填写的表格）
<table class="table table-hover">
<thead class="text-warning">
<?php foreach ( $table_head as $th) : ?>
<th <?php if ($th == "timeDates"){echo "style="."text-align:center;";} ?>>
<?php echo $th; ?>
</th>
<?php endforeach; ?>
</thead>
<tbody>
<?php
/* the table has 3 cells and does not contain nested tables
each row represents one customer, with his services in a unordered list
the times cell has a rowspan = the number of the appointment customers
*/
?>
<?php foreach ( $appointments as $appointment ) : ?>
<tbody class="appointments-table">
<?php foreach ( $appointment['customers'] as $index => $customer) : ?>
<tr class="appointments-table">
<td <?php echo $customer['is_booking'] ? "style='font-weight: bold;'" : ''; ?>><?php echo $customer['last_name'] . ' ' . $customer['first_name'];?></td>
<td>
<?php foreach ( $customer['services'] as $service ) : ?>
<ul style="list-style-type:none; padding-left: 0; margin-bottom: 0;">
<li><?php echo $service; ?></li>
</ul>
<?php endforeach; ?>
</td>
<?php if ( $index == 0 ) : ?>
<td class="appointments-table" rowspan=<?php echo count($appointment['customers']); ?>>
<ul class="pl-0 mb-0" style="list-style-type:none; text-align: center; margin-bottom: 0;">
<?php foreach ( $appointment['timeDates'] as $timeDate ) : ?>
<li><?php echo $timeDate['datetime_to'] ?
( date_format(date_create($timeDate['datetime_from']), "d/m/Y H:i") . ' until ' .
date_format(date_create($timeDate['datetime_to']), "d/m/Y H:i") ) :
date_format(date_create($timeDate['datetime_from']), "d/m/Y H:i"); ?></li>
<?php endforeach; ?>
</ul>
</td>
<?php endif; ?>
</tr>
<?php endforeach; ?>
</tbody>
<?php endforeach; ?>
</table>
注意：我打算改进视图以将尽可能多的逻辑移动到控制器
解决方案
现在我们可以看到您的查询，是的，您的预感是正确的——这不是一种有效的方法。这是所谓的n+1 问题SELECT的一个示例，您在其中为父记录 ( )做一个首字母appointments，然后为其中的每一个 ( n) 做另一个SELECT（或更多，在这种情况下），以找到相关的有关这些记录的数据。
另一个注意事项，SELECT .. WHERE IN ( ... subquery ... )很慢。如果可以的话，尽量避免这种情况。
您的架构似乎已经很好地规范化了，那么为什么不使用一些JOINs 呢？AFAICT，您应该能够通过单个查询找到所需的内容。所有连接都在都是INTs 的主键上，所以这应该是有效的。
这是我通过观察您的查询得出的最简单、最天真的表连接。您可能需要进行一些操作GROUP BY，或将 a 更改JOIN为INNER JOIN，或进行其他一些调整……我不知道您的数据或架构，所以这只是帮助您入门的第一步。当然，它不会以您当前拥有的格式生成结果，我的意思是嵌套数组等 - 您必须更新前端才能显示它。但它最终应该简单得多。
SELECT ... FROM appointments
JOIN appointments_schedule ON appointments.id = appointments_schedule.appointment_id
JOIN appointment_customer_services ON appointments.id = appointment_customer_services.appointment_id
JOIN customers ON appointment_customer_services.customer_id = customers.id
JOIN services ON appointment_customer_services.service_id = services.id
WHERE appointments_schedule.datetime_from > CURRENT_TIMESTAMP;
推荐阅读
- php - 来自 sparkfun 扫描仪的 client.print 字符串/字符
- python-3.x - 使用请求和硒在 python 中抓取网站时出现错误 403
- cypress - 如何阻止将请求发送到我的 API
- awk - 将列值折叠成行，复制第二列的值
- vue.js - Nuxt.js中如何根据window的存在动态切换组件？
- apache-flink - Flink 将 Apache Beam 捆绑在一起，不显示单个任务块
- java - 有没有办法使用命令行参数配置 JUnit 5 线程数？
- regex - 不包含连续换行符的正则表达式
- javascript - 反应状态没有正确更新
- cube - 我们可以做一个在微观策略中结合 2 个立方体的请求吗