php - 多个查询按创建日期排序

Question

我们需要按日期列出和排序多个事件。事件存储在多个表中，并由多个查询选择。我们不能使用 union + order by，因为一个表可能有 3-4 列，而另一个表可能有 20 列左右。

我们提出了以下解决方案:

$stmt = $db->query("
  SELECT 'login' table, id, created
  FROM login
  WHERE date(created) BETWEEN 'somedate' AND 'somedate'
  UNION
  SELECT 'order' table, id, created
  FROM order
  WHERE date(created) BETWEEN 'somedate' AND 'somedate'
  UNION
  SELECT 'receipt' table, id, created
  FROM receipts
  WHERE date(created) BETWEEN 'somedate' AND 'somedate'
  ORDER BY created
");

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $sorted[] = array(
      "table" => $row['table'],
      "id" => $row['id']
    );
}

foreach (array_chunk($sorted, 5000) as $dataChunk) {
  foreach ($dataChunk as $row) {
    if ($row['table'] == "login") {
      $stmt = $db->query("
        SELECT some columns
        FROM logins
        WHERE id = ".$row['id']."
      ");
      $row_count = $stmt->rowCount();
      if ($row_count != '0') {
        while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
          do something
        }
      }
    }
    if ($row['table'] == "order") {
      $stmt = $db->query("
        SELECT some columns
        FROM logins
        WHERE id = ".$row['id']."
      ");
      $row_count = $stmt->rowCount();
      if ($row_count != '0') {
        while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
          do a lot here
        }
      }
    }
    if ($row['table'] == "receipt") {
      $stmt = $db->query("
        SELECT some columns
        FROM logins
        WHERE id = ".$row['id']."
      ");
      $row_count = $stmt->rowCount();
      if ($row_count != '0') {
        while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
          do something
        }
      }
    }
  }
}

使用这个解决方案，我们的数据库将会有数千个查询，所以我们的问题是 - 可以用更好的方式来完成吗？

希望收到你们的来信...谢谢!

Answer 1

** 添加括号。否则，ORDER BY 仅适用于SELECT:

( SELECT ... )
UNION ALL
( SELECT ... )
UNION ALL
( SELECT ... )
ORDER BY ...

** 如果您希望重复并希望消除它们(不太可能基于您的代码)，请显式指定 UNION DISTINCT ，或者为提高速度而指定 UNION ALL。

** 为了速度，如果您有 INDEX(created)

WHERE created >= 'start...'
  AND created  < 'end...' + INTERVAL 1 DAY`