How to get array of rows from DB without specifying columns in Symfony

February 20, 2024

I want to have Repository class that can execute every ‘SELECT’ query
that I enter in it, random query from outside.
And I dont want to specify columns that I want to get from this native query.

Usually, everybody would use this code :

$sql = 'select column_one, column_two from table';

$rsm = new ResultSetMapping();
$rsm->addScalarResult('column_one', 'columnOne');
$rsm->addScalarResult('column_two', 'columnTwo');

$query = $this->getEntityManager()->createNativeQuery($sql, $rsm);
$result = $query->getResult();

But in my case I can’t specify ‘column_one’ or ‘column_two’,
because sql query and columns will be different.
And without $rsm->addScalarResult(‘column_one’, ‘columnOne’) I don’t get any results.

How can I get result without specifying columns

>Solution :

If you don’t want to have to configure a predefined result set, then you should do it like the example here: https://symfony.com/doc/current/doctrine.html#querying-with-sql

$conn = $this->getEntityManager()->getConnection();

$sql = '
        SELECT * FROM product p
        WHERE p.price > :price
        ORDER BY p.price ASC
        ';

$resultSet = $conn->executeQuery($sql, ['price' => $price]);

// returns an array of arrays (i.e. a raw data set)
return $resultSet->fetchAllAssociative();

I don’t think NativeQuery will work for your requirements because it relies on the idea of mapping the results to a known entity. If you don’t know anything in advance what the query is going to be, then you can’t do that mapping.