Is it possible to build INSERT INTO SELECT query with Doctrine QueryBuilder?

Question

I have some Criteria and need to create INSERT query based on SELECT with that Criteria.

Query should be like this:

INSERT INTO mytable (field1, field2)
SELECT f1, f2
FROM mytable2
JOIN mytable3 ON mytable3.field3 = mytable2.field2
WHERE mytable3.somefield = 'somevalue'

EDIT:

I know how to build INSERT INTO VALUES query. But I have conditions for SELECT in Criteria object and I need use them for building INSERT.

I need something like this

public function myInsert(Criteria $criteria)
    $qb = new QueryBuilder;
    $qb->insert('mytable')
        ->values('field1, field2') // Is it possible 
        ->select('f1, f2')         // somthing like this?
        ->from('mytable2')
        ->innerJoin('mytable2', 'mytable3', 'mytable3', 
            'mytable3.field3 = mytable2.field2')
        ->where($criteria);
    $qb->execute();
}

Possible duplicate of [Symfony2 (doctrine2) native sql insert](http://stackoverflow.com/questions/12538807/symfony2-doctrine2-native-sql-insert) — 1ed, Mar 10 '16 at 18:26
It is not duplicate. I know how to build INSERT INTO VALUES query, but how to build INSERT INTO SELECT query? — Nick, Mar 10 '16 at 19:16
@mnv DQL is something very strict, it is just tries to make things with programming language's object model, it cannot query as sql. I'm not sure why do you need ? you can also query native sql with doctrine. Could you please explain why do you need ? We may help in another way. — FZE, Mar 10 '16 at 19:42
I have page with filters. User may select something and show result. There are may be many rows. With pagination. User may click button for creating records in other table according to filter. As alternative, I can create select query with QueryBuilder and select nedded rows. And insert every record in loop. But it is bad idea in sense of performance. — Nick, Mar 10 '16 at 19:59

score 0 · Answer 1 · answered Nov 03 '21 at 14:14

I think you have to build your own statements. It should be something like this.

            $sql ="INSERT INTO mytable (field1, field2)
                   SELECT f1, f2
                   FROM mytable2
                   JOIN mytable3 ON mytable3.field3 = mytable2.field2
                   WHERE mytable3.somefield = 'somevalue'";
            /** @var Connection $connection */
            $connection = GeneralUtility::makeInstance(ConnectionPool::class)
                ->getConnectionForTable(self::TABLENAME);
            /** @var DriverStatement $statement */
            $statement = $connection->prepare($sql);
            $statement->execute();

Is it possible to build INSERT INTO SELECT query with Doctrine QueryBuilder?

1 Answers1