MySQL 如何在 zend db 模型中使用“distinct”

How to use 'distinct' in zend db model

提问by Nicky

I have search for a long time to get this thing work.

我已经搜索了很长时间才能让这件事发挥作用。

What I want is to know how I user the 'distinct' in a zend db model to make my selection for the followers of a user unique.

我想要的是知道我如何在 zend db 模型中使用“distinct”来使我对用户的追随者的选择独一无二。

My db model to count followers for a user (here I need to add the 'distinct')

我的数据库模型用于计算用户的关注者（在这里我需要添加“distinct”）

public function countFollowers($user_id) 
{    
    $rowset       = $this->fetchAll("user_id = $user_id");
    $rowCount     = count($rowset);

    if ($rowCount > 0) {
      return $rowCount;
    } else {
      return $rowCount;
    }
}

EDIT: This function is part of 'class Application_Model_DbTable_Followers extends Zend_Db_Table_Abstract'

编辑：此函数是“类 Application_Model_DbTable_Followers extends Zend_Db_Table_Abstract”的一部分

My table structure

我的表结构

• id
• article_id // Id of the article who is written by 'user_id'.
• user_id // user_id owner of the article
• follower_id // member who has following this article
• date // date of follow

• ID
• article_id // 'user_id' 所写文章的 ID。
• user_id // 文章所有者的 user_id
• follower_id // 关注这篇文章的成员
• date // 关注日期

'user_id' can be written various articles, the follower can follow various articles of the same writer. I want to make a unique follower count. As an example what I want, If a follower is following 8 articles of one writer it has to be compared to '1' in the count.

'user_id'可以写各种文章，关注者可以关注同一作者的各种文章。我想让一个独特的追随者计数。作为我想要的一个例子，如果一个追随者关注一位作家的 8 篇文章，它必须与计数中的“1”进行比较。

I hope this will be clear enough to understand what I tried to reach.

我希望这足够清楚，以理解我试图达到的目标。

With kind regards,

亲切的问候，

Nicky

尼基

回答by Ozair Kafray

Using distinct:

使用不同：

public function countFollowers($user_id) 
{
    $select = $this->select()
              ->distinct()
              ->where('user_id = ?', $user_id);

    $rowset = $this->fetchAll($select);
    $rowCount = count($rowset);

    return $rowCount;
}

EDIT: After edit in question to get count of followers of a user. You actually need to use groupNOT distinct. I have tested the following query works to fetch the data to be count()ed,

编辑：在有问题的编辑后获取用户的关注者数量。您实际上需要使用组NOT 不同。我已经测试了以下查询工作来获取要 count()ed 的数据，

SELECT * FROM followersWHERE user_id = 1 GROUP BY user_id, follower_id

SELECT * FROM followersWHERE user_id = 1 GROUP BY user_id, follower_id

I have not tested the code, but something like this should work:

我还没有测试过代码，但这样的事情应该可以工作：

public function countFollowers($user_id) 
{
    $select = $this->select()
              ->where('user_id = ?', $user_id)
              ->group(array('user_id', 'follower_id')); 

    $rowset = $this->fetchAll($select);
    $rowCount = count($rowset);

    return $rowCount;
}

回答by Richard Parnaby-King

You can specify mysql functions in the 'from' function that makes up select query function. To use the from function you need to pass the table name as the first parameter, however passing $this (your table model class) works fine.

您可以在构成选择查询函数的“from”函数中指定 mysql 函数。要使用 from 函数，您需要将表名作为第一个参数传递，但是传递 $this （您的表模型类）可以正常工作。

public function countFollowers($user_id)
{
  $rowset = $this->fetchAll(
    $this->select()
    ->from($this, array('DISTINCT user_id'))
    ->where('user_id = ?', $user_id)
  );

  return count($rowset);
}

[edit]

[编辑]

Based on your edit, 'group' may also work for you:

根据您的编辑，“组”也可能适合您：

public function countFollowers($user_id)
{
  $rowset = $this->fetchAll(
    $this->select()
    ->where('user_id = ?', $user_id)
    ->group('user_id')
  );

  return count($rowset);
}

This will group all matching user_id into one record. So if a user is found, it will return 1, else 0.

这会将所有匹配的 user_id 分组为一条记录。因此，如果找到用户，它将返回 1，否则返回 0。

回答by David Weinraub

Retrieving all the rows simply to get a count strikes me as overkill.

检索所有行只是为了得到一个计数让我觉得有点矫枉过正。

You can do a count using something like this:

您可以使用以下方法进行计数：

$select = $db->select();
$select->from('testcount', new Zend_Db_Expr('COUNT(id)'))
       ->where('user_id = ?', $someUserId);
return $db->fetchOne($select);

回答by Michael Druguet

don't write that :

不要这样写：

    public function countFollowers($user_id)
    {
      $rowset = $this->fetchAll(
        $this->select()
        ->from($this, array('DISTINCT user_id'))
        ->where('user_id = ?', $user_id)
      );

  return count($rowset);
}

But that :

但是：

public function countFollowers($user_id)
{
  $rowset = $this->fetchAll(
    $this->select()
    ->from($this, array('DISTINCT(user_id)'))
    ->where('user_id = ?', $user_id)
  );

  return count($rowset);
}

Else you will have an error wich looks like to Mysqli prepare error:

否则，您将遇到一个错误，类似于 Mysqli 准备错误：

Unknown column 'reperttheitroade.distinct idReperttheitroadeParent' in 'field list'

“字段列表”中的未知列“reperttheitroade.distinct idReperttheitroadeParent”

回答by PHP Developer

Today I tried DISTINCT in JOIN LEFT case and it doesn't work. But if you add a Group By to the DISTINCT column, it works fine.

今天我在 JOIN LEFT 案例中尝试了 DISTINCT，但它不起作用。但是，如果将 Group By 添加到 DISTINCT 列，则效果很好。

回答by Sergey

Also we have one method from the official manual

我们也有官方手册中的一种方法

Just use "distinct"

只需使用“不同”

Build this query: SELECT DISTINCT p."product_name" FROM "products" AS p

构建此查询：SELECT DISTINCT p."product_name" FROM "products" AS p

$select = $db->select()
             ->distinct()
             ->from(array('p' => 'products'), 'product_name');