组合这两个查询 - Combining these 2 queries

Combining these 2 queries

本文关键字：两个查询组合 | 更新日期: 2024-05-17

我有两个查询。

public function fetchEvents(){
    $events = $this->cmsdb->query("SELECT * FROM tickets_events WHERE `active` = 1", true); //true just means if only one row is returned it will be key [0] so I can loop no matter how many rows returned
if(empty($events)){
    return array();
    }
    foreach ($events as $k => $event) {
        $events[$k]['items'] = $this->cmsdb->query("SELECT *, FROM_UNIXTIME(date, '%W, %D %M %Y') as dateString
                               FROM tickets_items WHERE eventId = '" . intval($event['id']) . "'
                               AND public = 1 AND confirmed = 1 AND date > '" . time() . "'
                               ORDER BY date ", true);
    }
    return $events;
}

我已经简化了第一个查询，我向函数传递了一些参数来过滤它返回的事件，但这部分与这个问题无关。

我遇到的问题是，如果我想获取每个事件及其项目，它需要进行大量查询（目前有691个事件）。我是否可以将这些查询组合在一起？

您可以将表JOIN放在一起以获得相同的答案。它也应该表现得更好：

SELECT 
     i.*
    ,FROM_UNIXTIME(i.date, '%W, %D %M %Y') as dateString
FROM tickets_items  i
JOIN tickets_events e
  ON i.eventId = e.id
WHERE i.public    = 1
  AND i.confirmed = 1
  AND e.active    = 1
  AND date > CURRENT_TIMESTAMP
ORDER BY date