API Platform Raw Query

0
votes

Anyone knows how to get raw query working in API platform controller/handler,

I am trying to do something like,

 $temp_table_query = $this->entityManager->createQuery('CREATE TEMPORARY TABLE {temp_validation_code_table} LIKE {validation_code}');
        $qb = $this->entityManager->createQueryBuilder();
        $qb = $qb->select('LOAD DATA LOCAL INFILE {$file} INTO TABLE {temp_validation_code_table} (code) SET offer = {$offer}');

Trying to load file data into a temporary table for validation before inserting in actual db table

1
api-platform.com

1 Answers

1
votes

This needs to be done using the underlying Doctrine PDO abstraction (Doctrine\DBAL\Connection), which can be obtained via $entityManager->getConnection();.

First however, you need to make sure that the underlying PDO connection is configured to allow LOAD LOCAL INFILE (by setting PDO::MYSQL_ATTR_LOCAL_INFILE to true). This can be done by providing the following Doctrine configuration (in doctrine.yaml):

doctrine:
    dbal:
        #...
        options:
            1001: true # This sets PDO::MYSQL_ATTR_LOCAL_INFILE to true

Be warned that allowing LOAD LOCAL INFILE possibly introduces security vulnurabilities.

Given I have a file named example.csv containing the following CSV data:

1,"foo"
2,"bar"
3,"baz"

I can then insert this data using the Doctrine PDO abstraction:

<?php
//...
// Local path to the CSV file
$file = __DIR__ . '/example.csv';
// Obtaining the PDO connection
$connection = $this->entityManager->getConnection();
// Creating the temporary table
$connection->executeStatement('CREATE TEMPORARY TABLE foobar (id int AUTO_INCREMENT PRIMARY KEY , name VARCHAR(256) NOT NULL)');
// Inserting the CSV data
$connection->executeStatement("LOAD DATA LOCAL INFILE '$file' INTO TABLE foobar FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'");