How to use 'distinct' in zend db model

Question

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.

My db model to count followers for a user (here I need to add the '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'

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

'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.

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

With kind regards,

Nicky

Answer 1

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 group NOT distinct. I have tested the following query works to fetch the data to be count()ed,

SELECT * FROM followers WHERE 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;
}

Answer 2

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.

Answer 3

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.

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.

Answer 4

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 = $db->select()
             ->distinct()
             ->from(array('p' => 'products'), 'product_name');

Answer 5

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:

Unknown column 'repertoire.distinct idRepertoireParent' in 'field list'

Answer 6

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);