CODEWITHSUNDEEP
phpmysqlsqlzend-framework
Scoobler
Scoobler

Reputation: 9719

Zend SQL statement to find and order results by occurances

I have a table which looks like this:

ID | name | phone_number | date
1  | bob  | 4567         | 1/1/2011
2  | bob  | 8912         | 1/1/2010
3  | bob  | 1234         | 1/1/2010
4  | bob  | 1234         | 1/1/2014
5  | bow  | 4567         | 1/1/2014

I am creating an auto suggest so as a user types a name it will pick up previous names and phone numbers. As people can change their number, I want to order the results and display a count of the numbers based on the most popular phone number. As a second sort it should be based on the name and a final sort on the date it was added (newest first).

So if someone types 'bo' it should return:

bob 1234 (2) - Because it is alphabetically highest plus has the most phone numbers.
bob 4567 (1) - Because it is alphabetically highest and its last entry is newer.
bob 8912 (1) - Because it is alphabetically highest and its last entry was older.
bow 4567 (1) - Because it is alphabetically lower.

Normally I would use the Zend methods for example:

public function getAllNames($part)
{
    $select = $this->select();
    $select->where('name LIKE ?', '%'.$part.'%')
           ->order('name');
    return $this->fetchAll($select)->toArray();
}

But I am stuck on how to count the entries for phone numbers and then perform the three orders.

OR is it better to just get all the entries and use PHP to process the results and order them accordingly?

Upvotes: 1

Views: 462

Answers (3)

Scoobler
Scoobler

Reputation: 9719

Here is the answer I have actually used thanks to user3511578. As in the comments, I don't have the expression module in my zend library, so I did it like this:

Just in case it helps anyone in the future:

public function getAllNames($part)
{
    $select = $this->select();
    $select->from($this->_name, array('name', 'phone_number', 'count'=>'COUNT(phone_number)'))
           ->where('name LIKE ?', '%'.$part.'%')
           ->group('name')
           ->group('number')
           ->order('name')
           ->order('count DESC')
           ->order('date desc');
    return $this->fetchAll($select)->toArray();
}

Upvotes: 1

Strawberry
Strawberry

Reputation: 33935

If I understand correctly...

 DROP TABLE IF EXISTS my_table;

 CREATE TABLE my_table
 (ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY
 ,name VARCHAR(12) NOT NULL
 ,phone_number VARCHAR(12) NOT NULL
 ,date DATE NOT NULL
 );

 INSERT INTO my_table VALUES
 (1  ,'bob','4567','2011-01-01'),
 (2  ,'bob','8912','2010-01-01'),
 (3  ,'bob','1234','2010-01-01'),
 (4  ,'bob','1234','2014-01-01'),
 (5  ,'bow','4567','2014-01-01');


 SELECT x.*
      , y.total
   FROM my_table x
   JOIN
      ( SELECT name
             , phone_number
             , MAX(date) n_date
             , COUNT(*) total
          FROM my_table
         WHERE name LIKE 'bo%'
         GROUP
            BY name
             , phone_number
      ) y
     ON y.name = x.name
    AND y.phone_number = x.phone_number
    AND y.n_date = x.date
  ORDER
     BY x.name
      , y.total DESC
      , x.date DESC;
 +----+------+--------------+------------+-------+
 | ID | name | phone_number | date       | total |
 +----+------+--------------+------------+-------+
 |  4 | bob  | 1234         | 2014-01-01 |     2 |
 |  1 | bob  | 4567         | 2011-01-01 |     1 |
 |  2 | bob  | 8912         | 2010-01-01 |     1 |
 |  5 | bow  | 4567         | 2014-01-01 |     1 |
 +----+------+--------------+------------+-------+

Upvotes: 0

malte
malte

Reputation: 1444

What about following? 

public function getAllNames($part)
{
   $select = $this->select();
   $select->where('name LIKE ?', '%'.$part.'%')
          ->order('name')
          ->order(new \Zend\Db\Sql\Expression('LENGTH(phone_number)'))
          ->order('date desc');
   return $this->fetchAll($select)->toArray();
}

UPDATED ANSWER 

public function getAllNames($part)
{
   $select = $this->select();
   $select->where('name LIKE ?', '%'.$part.'%')
          ->group('name')
          ->group('phone_number')
          ->order('name')
          ->order(new \Zend\Db\Sql\Expression('count(phone_number) DESC'))
          ->order(new \Zend\Db\Sql\Expression('max(date) DESC'));
   return $this->fetchAll($select)->toArray();
}

Another UPDATE: using max(date) is not necessary 

public function getAllNames($part)
{
   $select = $this->select();
   $select->where('name LIKE ?', '%'.$part.'%')
          ->group('name')
          ->group('phone_number')
          ->order('name')
          ->order(new \Zend\Db\Sql\Expression('count(phone_number) DESC'))
          ->order('date DESC');
   return $this->fetchAll($select)->toArray();
}

Upvotes: 1

Related Questions