Using Raw SQL with Doctrine

Execute raw SQL using Doctrine 2

I found out the answer is probably:

A NativeQuery lets you execute native
SQL, mapping the results according to
your specifications. Such a
specification that describes how an
SQL result set is mapped to a Doctrine
result is represented by a
ResultSetMapping.

Source: Native SQL.

Using Raw SQL with Doctrine

$q = Doctrine_Manager::getInstance()->getCurrentConnection();
$result = $q->execute(" -- RAW SQL HERE -- ");

See the Doctrine API documentation for different execution methods.

Raw SQL Query from Non Default Connection

Here is how i'm doing it.

First you need to update your config to set your different connections in config/package/doctrine.yaml :

doctrine:
    dbal:
        default_connection: default
        connections:
            default:
                url: '%env(resolve:DATABASE_URL)%'
            alt:
                url: '%env(resolve:DATABASE_URL_ALT)%'

Then, in your controller or service you can inject the ManagerRegistry and get the connection :

public function index(ManagerRegistry $managerRegistry)
{
    $connection = $managerRegistry->getConnection("alt");
    $result = $connection
        ->prepare("SELECT * FROM your_table_in_alt_db")
        ->executeQuery()
        ->fetchAllAssociative()
    ;
    dump($result);
}

How to get the raw sql of doctrine

According to Logging Doctrine SQL queries in Symfony2:

You can use a Doctrine logger such as DebugStack or own implementation of SQLLogger interface.

$logger = new \Doctrine\DBAL\Logging\DebugStack();

/* @var Doctrine\DBAL\Connection $connection */
$connection->getConfiguration()->setSQLLogger($logger);

After execution of some queries you can get query strings and parameters from public property of the logger.

var_dump($logger->queries);

---

You second question is very different. Btw, as said in documentation:

Objects that were not already loaded from the database are replaced
with lazy load proxy instances. Non-loaded Collections are also
replaced by lazy-load instances that fetch all the contained objects
upon first access. However relying on the lazy-load mechanism leads to
many small queries executed against the database, which can
significantly affect the performance of your application. Fetch Joins
are the solution to hydrate most or all of the entities that you need
in a single SELECT query.

Doctrine 1 Migrations, How to execute raw SQL

What I do in these cases is create an empty migration class using the symfony doctrine:generate-migration command and then fill in both up() and down() methods similar to this:

public function up()
{
    $dbh = Doctrine_Manager::connection()->getDbh();
    $query = "INSERT INTO `some_table` (`id`, `created_at`, `updated_at`)
              VALUES 
              ('1', NOW(), NOW()), 
              ('2', NOW(), NOW()),
              ('3', NOW(), NOW());
    ";
    $stmt = $dbh->prepare($query);
    $stmt->execute();
}

Execute raw SQL using Doctrine 2 with Doctrine paginator

Please try that :

$maxpage = ceil(count($paginator) / $limit);

Read the doc

Doctrine: Hydrate models from raw SQL

Found it! Through the EntityManager's createNativeQuery function. And for the RSM I need to use the ResultSetMappingBuilder class.

$rsm = new \Doctrine\ORM\Query\ResultSetMappingBuilder($this->getEntityManager());
$rsm->addRootEntityFromClassMetadata(\Path\To\Model::class, 'alias');

$nativeQuery = $this->getEntityManager()->createNativeQuery('-- query--', $rsm);

$nativeQuery->getResult();

From Raw query (with subqueries) To doctrine query builder

Finally Query was a little bit easier and I could achieve that:

$sub = $this->createQueryBuilder('n');

        $sub->select('MAX(n.fecha)')
            ->where('n.alumno = :id')
            ->groupBy('n.asignatura');

        $qb  = $this->_em->createQueryBuilder('main');
        $qb->select('mm')
            ->from('App:Nota', 'mm')
            ->where($qb->expr()->In('mm.fecha', $sub->getDQL())
            );

        $qb->setParameter('id', $id);
        $query  = $qb->getQuery();

        return $query->getResult();

---

Related Topics