How to get an associative array from a ResultSetMapping?

Question

I'm working on a Symfony2 project (Doctrine) and I would like to get a simple associative array from a ResultSetMapping object.

This is my request :

$rsm = new ResultSetMapping;

//$rsm->addIndexByScalar('name');
$rsm->addScalarResult('name', 'name');
$rsm->addScalarResult('rate', 'rate');

return $this->_em->createNativeQuery(sprintf('
                SELECT DISTINCT t1.sold_drug as name, (COUNT(*)/(SELECT COUNT(*) FROM Transaction t WHERE t.sold_drug != ""))*100 as rate
                         FROM Transaction t1

                 WHERE t1.sold_drug != "" AND t1.prescribed_but_not_sold != "No"
                 GROUP BY name
                 ORDER BY rate DESC', $this->getFilterQuery()), $rsm)->getResult();

So I would like to get an associative array such as :

array('nameA' => 10, 'nameB' => 2, ...);

I tried to add ScalarResult / ScalarIndex but no success !

Any idea ? Thanks !

Answer 1

Your question has unnecessarily complicated query that draws attention from your question. But here is what you basically need:

    $rsm = new ResultSetMapping();
    $query = $this->_em->createNativeQuery('SELECT unique_name, price FROM drugs', $rsm);
    $rsm->addIndexByScalar('unique_name');
    $rsm->addScalarResult('price', 'price');
    //more scalars can follow
    var_dump($query->getResult());

In this case you will get something like this:

array (size=4)
  'NameA' => 
    array (size=1)
      'price' => int 10
  'NameB' => 
    array (size=1)
      'price' => int 20
  'NameC' => 
    array (size=1)
      'price' => int 30
  'NameD' => 
    array (size=1)
      'price' => int 40

Please let me know if it's what you needed. And accept my answer if it is.

P.S. Please note, that if unique_name is not really unique, you will lose some results that have duplicate indexes.