Write an sql Query in Zend

Question

As a beginner in zend Framework i need some help to write an sql query. This is my Query:

   SELECT 
    COUNT( * ) AS Ouvert , SUBSTRING_INDEX( emails.email_value,  '@', -1 ) AS FAI, track_open.open_date
    FROM emails, track_open
    WHERE emails.email_id = track_open.email_id
    AND DATE( open_date ) = CURDATE( ) 
    GROUP BY SUBSTRING_INDEX( emails.email_value,  '@', -1 )

And this my function where i have to write it:

public function getOpen()
{
    $query = $this->select()
                  ->setIntegrityCheck(false)
                  ->from(array('e' => 'emails'))
                  ->join(array('to' => 'track_open'), 'e.email_id = to.email_id')
                ??????????

    $result = $this->fetchAll($query);

    if($result)
    {
        return $result->toArray();
    }

    return false;
}

So if anyone could give an example to write this query, because i don't know how to complete it.

Answer 1

It has been a long time since I've worked with Zend_Db and I cant really test the code, but you can try the following:

 $query = $this->select()
              ->setIntegrityCheck(false)
              ->from(array('e' => 'emails'), array(
                  'Ouvert' => new Zend_Db_Expr('COUNT( * )'),
                  'FAI' => new Zend_Db_Expr("SUBSTRING_INDEX( emails.email_value,  '@', -1 )"),
                  'track_open.open_date',
              ))
              ->join(array('to' => 'track_open'), 'e.email_id = to.email_id')
              ->where('emails.email_id = track_open.email_id')
              ->where('DATE( open_date ) = CURDATE( )')
              ->group(new Zend_Db_Expr("SUBSTRING_INDEX( emails.email_value,  '@', -1 )"))