Writing SQL in Doctrine

While using Doctrine, you might find yourself trying to write a query that DQL does not support. For example, if you want to use DELETE with JOIN, you simply cannot do that with DQL, because it is currently not supported. Instead, you could write the SQL query, but paying attention to not breaking Doctrine specific features.

When writing an SQL query in Doctrine, you should use:

Here's an example of how you could do that:

function deleteScriptsByLanguageId($languageId) {
    $connection = Doctrine_Manager::getInstance()->getConnection('db1');

    $scriptTable = $connection->getTable('Script');
    $languageTable = $connection->getTable('Language');

    $connection->exec(
        'DELETE s ' .
        'FROM ' . $scriptTable->getTableName() . ' s ' .
        'INNER JOIN ' . $languageTable->getTableName() . ' l ON ' .
            's.' . $scriptTable->getColumnName('languageId') . ' = ' .
            'l.' . $languageTable->getColumnName('id') .
        'WHERE l.' . $languageTable->getColumnName('id') . ' = ?',
        array($languageId)
    );
}