Doctrine raw SQL

Doctrine 1.2 is great, but there are some thing that are simply impossible to do using DQL. For example UNION or OUTFILE. Well, usually you don't have to use them and it's not even recomended but sometimes it's easier or it's just the only option.

 1 
 2 
$conn = Doctrine_Manager::connection();
$result = $conn->exec("SQL Query");

At work I'm working on a website where visitors can subscribe to a newsletter and they can choose how often they want to recieve it (once a week or once a month). Then it's up to our client, if he wants to send newsletter to all, only week or only month subscribers. And there comes the problem. There are over 16 000 subscribers and if I tried to use standard Doctrine ORM approach it always died on not enought memory after approximately 11 000 inserted objects. Memory management in PHP 5.2.x and older is really bad.

 1 
 2 
 3 
 4 
 5 
 6 
 7 
 8 
 9 
 10 
 11 
 12 
 13 
 14 
 15 
 16 
 17 
$array = array(
    'created_at' => $now,
    'updated_at' => $now,
    'newsletter_id' => $nid,
);
$users = $recipientsQuery->execute(array(), DOCTRINE::HYDRATE_NONE);

while ($users && count($users) > 0) {
    $conn->beginTransaction();
    foreach ($users as $user) {
        $totalCount++;
        $array['email'] = $user[0];
        $conn->insert($table, $array);
    }
    $conn->commit();
    $recipientsQuery->offset($totalCount);
}

And this works pretty good. 16 000 inserted rows in 15 seconds without any problem.

blog comments powered by Disqus