Better Query

rrbot · February 7, 2011, 8:53pm

I have a ConsoleCommand to run some mail queue stuff via cron.

I’m trying to send emails based on the current time and the time a mail should be sent.

I ended up writing this in MySQL, and it works fine, but I know there’s a simpler way to achieve this, possibly with a ->compare().

Can someone rewrite this for me in the most Yii manner?




$mq = MailQueue::model();

$con = array(

      'condition' => "try_sent < 1 AND sent_time is NULL AND time_to_send < NOW()",

 );

$results = $mq->findAll($con);

Thanks!

samdark · February 7, 2011, 9:15pm

In your case it’s more efficient to use




Yii::app()->db->createCommand($sql)->queryAll();

You can move it into the method of MailQueue model and use it like this:




$emails = MailQueue::model()->getEmailsToSend();

foreach($emails as $email){

  //

}

rrbot · February 7, 2011, 9:40pm

samdark:

In your case it’s more efficient to use
Yii::app()->db->createCommand($sql)->queryAll();
You can move it into the method of MailQueue model and use it like this:
$emails = MailQueue::model()->getEmailsToSend();

foreach($emails as $email){

  //

}

Thanks samdark!

The biggest part of that query I struggled with was the time comparison. Does Yii have any built in methods for comparing dates?

samdark · February 8, 2011, 12:12am

Depends on how are you storing dates.

rrbot · February 8, 2011, 5:02am

Datetime

‘2011-2-7 11:33:33’

samdark · February 8, 2011, 11:16am

As I know, you can compare dates with just >, < or BETWEEN.

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

rrbot · February 9, 2011, 8:53pm

Thanks for the references Sam. I was trying to get too tricky with it I suppose.