php - 如何处理(MySQL查询): Only find newest rows by DISTINCT WHERE the newst one IS NOT a specific value (with symfony2)

Question

我工作和阅读了几个小时，除了执行未执行的查询或向代码添加新字段和更新事件之外，我找不到任何合适的解决方案。正如你可以想象的那样，这个问题让我发疯。

基本想法

我确实有一个名为ping的表，其中包含有关网站响应时间的信息，并且与网站处于ManyToOne关系中。< br/>另一个名为 website 的表包含有关网站的数据，并且与 ping 存在 oneToMany 关系。
因此，一个 网站 可以有数千个 ping，但一行 ping 只能有一个网站。
到目前为止，一切都很好。

表格数据
表:ping - 包含一个示例行:

=================================================================================
|      id       |  website_id   |   response    | is_reachable  |  created_at   |
=================================================================================
|     6994      |      15       |      49       |       1       |  22/12/2013   |
|               |               |               |               |   12:14:23    |
---------------------------------------------------------------------------------

表:网站 - 包含一个示例行:

=================================================================================================
|      id       |  company_id   |     name      |      url      |     logo      |  description  |
=================================================================================================
|      15       |       7       |   LinkedIn    |http://www.link| linkedin.svg  |  US Website   |
|               |               |               |   edin.com    |               |               |
-------------------------------------------------------------------------------------------------

<小时/>

案例

我想要做的是制作一个简单的列表，列出最后 10 个最慢的响应(毫秒)。听起来很容易？也许我的数据库设计错误，或者我只是没有明白这一点。
无论如何，我需要过滤掉那些无法可达的人(is_reachable = 0或/和response=null)但这不能通过 WHERE 子句来完成，因为这会向我显示最后一个 ping 行，其中 is_reachable = 1 这不是我的目标。

据我所知是这样的:

        $qb = $this->createQueryBuilder('s')
            ->Select('DISTINCT(s.website)')
            ->AddSelect('s.id, s.response')
            ->AddSelect('w.id as wid, w.name')
            ->leftJoin("s.website", 'w')
            ->orderBy('s.id', 'DESC')
            ->addorderBy('s.response, 'DESC');

到目前为止它产生了这个:

array (size=10)
  0 => 
    array (size=5)
      1 => string '21' (length=2)
      'id' => int 14063
      'response' => int 75
      'wid' => int 21
      'name' => string 'Google Mail' (length=11)
  1 => 
    array (size=5)
      1 => string '20' (length=2)
      'id' => int 14062
      'response' => int 27
      'wid' => int 20
      'name' => string 'Google DE' (length=9)
  2 => 
    array (size=5)
      1 => string '19' (length=2)
      'id' => int 14061
      'response' => int 25
      'wid' => int 19
      'name' => string 'Google' (length=6)
  3 => 
    array (size=5)
      1 => string '18' (length=2)
      'id' => int 14060
      'response' => null
      'wid' => int 18
      'name' => string 'Twitter DE' (length=10)
  4 => 
    array (size=5)
      1 => string '17' (length=2)
      'id' => int 14059
      'response' => null
      'wid' => int 17
      'name' => string 'Twitter UK' (length=10)

如上所述，如果您添加一个简单的 WHERE 语句，这只会导致 TWITTER DE/TWITTER UK 使用 is_reachable=1 显示其最后一行。我不知道如何解决这个问题。如有任何帮助，我们将不胜感激!

临时灵魂(寻找更好的灵魂!)

$em = $this->getEntityManager();
$rsm = new ResultSetMapping;
$rsm->addScalarResult('website_id', 'website_id');
$rsm->addScalarResult('name', 'name');
$rsm->addScalarResult('response', 'response');
$query = $em->createNativeQuery(
    "
    select inf.website_id, w.name, inf.response, inf.is_reachable, inf.max_date
    from (
        select p.website_id, p.response, p.id, p.is_reachable, lp.max_date
        from ping p
        inner join (
            select website_id, max(created_at) as max_date
            from ping
            group by website_id
            ) lp
        on lp.website_id = p.website_id
        and lp.max_date = p.created_at
        ) inf, website w
    where inf.website_id = w.id
    and inf.is_reachable != 0
    order by inf.response ".$orderBy."
    LIMIT ?
    ", $rsm)
    ->setParameter(1, $max)
    ->getResult();

return $query;

Answer 1

这是一个经典的“查找最大行对应的信息”问题。

在不执行子请求的情况下，这是通过在表本身上执行左连接来完成的:

SELECT w.id, s_last.id, s_last.response, w.name
FROM website w
JOIN ping s_last ON w.id = s.websiteId
# Left join on pings higher than us
LEFT JOIN ping s ON s.id > s_last.id
WHERE 
  # only take ping which has no higher ping
  s.id IS NULL
  AND
  # Filter out unreachable websites
  s_last.is_reachable = 1
ORDER BY s.response DESC

根据您的示例，Twitter DE 和 Twitter UK 将不会出现在结果集中，因为我们上次尝试时未到达它们。 (AFAIU，这就是你想要的)

尽管如此，我不知道它是否适用于具有许多结果的数据。如果性能不够好，请尝试将最后一次 ping 保存到另一个表中。