How to write an insert request in Doctrine
I'm still not convinced that this is the correct approach you are taking, but if you really need a SQL query to run for any reason, you can do it in Doctrine with the function $entityManager->createNativeQuery();
:
http://doctrine-orm.readthedocs.org/en/latest/reference/native-sql.html
Doctrine is not a query manipulation tool. The whole idea is to work at the Entity level, not the SQL level (tables, etc.). Doctrine 2 QueryBuilder
doesn't even support INSERT
DQL operations .
A small pseudo-code snippet below to illustrate how this can be done in Doctrine:
$qb = $entityManager->createQueryBuilder();
$qb->select('s')
->from('\Foo\Source\Entity', 's')
->where('s.col1 = :col1')
->setParameter('col1', 'mycriteria');
$sourceEntities = $qb->getQuery()->getResult();
foreach($sourceEntities as $sourceEntity) {
$targetEntity = new \Foo\Target\Entity();
$targetEntity->col1 = $sourceEntity->col1;
$targetEntity->col2 = $sourceEntity->col2;
$entityManager->persist($targetEntity);
}
$entityManager->flush();
source to share
Doctrine documentation says:
If you want to execute DELETE, UPDATE or INSERT commands, the SQL API cannot be used and will probably propagate errors. use EntityManager # getConnection () to access your own database connection and call executeUpdate () method on those requests.
Examples of
// Get entity manager from your context.
$em = $this->getEntityManager();
/**
* 1. Raw query
*/
$query1 = "
INSERT INTO target (tgt_col1, tgt_col2)
SELECT 'flag' as marker, src_col2 FROM source
WHERE src_col1='mycriteria'
";
$affectedRows1 = $em->getConnection()->executeUpdate($query1);
/**
* 2. Query using class metadata.
*/
$metadata = $em->getClassMetadata(Your\NameSpace\Entity\Target::class);
$tableName = $metadata->getTableName();
$niceTitle = $metadata->getColumnName('niceTitle');
$bigDescription = $metadata->getColumnName('bigDescription');
$metadata2 = $em->getClassMetadata(Your\NameSpace\Entity\Source::class);
$table2Name = $metadata2->getTableName();
$smallDescription = $metadata2->getColumnName('smallDescription');
$query2 = "
INSERT INTO $tableName ($niceTitle, $bigDescription)
SELECT 'hardcoded title', $smallDescription FROM $table2Name
WHERE $niceTitle = 'mycriteria'
";
$affectedRows2 = $em->getConnection()->executeUpdate($query2);
source to share