CODEWITHSUNDEEP
phpmultidimensional-arraydoctrine-ormdoctrine
manix
manix

Reputation: 14747

Using column value as array index in doctrine

I am using doctrine 2.1 in order to create a model for settings table:

id |  arg  |  value  |  category
1  |  name |  foo    |  general_settings 
2  |  desc |  bar    |  general_settings

Suppose that I have a lot of setting for different categories. In order to get all the setting for a specific category I do something like this:

$q = Doctrine_Query::create()
    ->from('Setting p')
    ->where('p.category = ?', $category_name);

Everything works fine at this point. Well.. the question of $64,000 is: Do exist a data access alternative that allow me to read the result as below? 

$resultSet = $q->execute(); 

//the magic here could be use the -arg- column as index
$requested_setting = $resulSet['name']  

//print the setting value
echo $requested_setting['value'];  //should prints "foo"

//another way
echo $resulSet['desc']['value']; //should prints "bar"

Upvotes: 25

Views: 44627

Answers (4)

Martin
Martin

Reputation: 434

For the sake of completeness, when using NativeQuery, the index of the result array can be defined in the ResultSetMapping object. For example, by using the addIndexByScalar method.

$rsm = new ResultSetMapping();

$rsm->addScalarResult('ctime', 'ctime', 'datetime');
$rsm->addScalarResult('id', 'id', 'integer');
$rsm->addIndexByScalar('id'); // ← 

$stm = $this->getEntityManager()->createNativeQuery("SELECT ctime, id FROM table", $rsm);
$stm->getArrayResult();

Upvotes: 0

Mr. Sulaman Khan
Mr. Sulaman Khan

Reputation: 475

Doctrine IndexBy function is used, to display column value as array index

$this
// database table alias
->createQueryBuilder( 'app_settings' )
// first parameter should be alias and second parameter will be column name, which you want to show as array index
->indexBy('app_settings','app_settings.name')
// get query
->getQuery()
// get doctrine result in array format
->getArrayResult();

The result of mentioned query will be in this format: Result of mentioned query

Upvotes: 1

SMASHED
SMASHED

Reputation: 486

FYI when using createQueryBuilder in your EntityRepository, you can directly specify the INDEX BY along with the alias:

$this->createQueryBuilder('p', 'p.id')

This avoids handling manually the from which is automatically handled in EntityRepositories.

Upvotes: 23

manix
manix

Reputation: 14747

I got it: the trick here is use the INDEX BY word.

Query class

import the Query class (no always optional):

use \Doctrine\ORM\Query;

create the query:

$query = $this->data->em->createQuery('
    SELECT s 
    FROM models\Setting s 
    INDEX BY s.arg //to set array custom key
    WHERE s.category = :category');
$query->setParameter('category', 'general');

set the hidration mode in order to work with read-only arrays 

$settings = $query->getResult(Query::HYDRATE_ARRAY);

Display the value:

echo $settings['desc']['value'];  // prints "bar"

QueryBuilder

With the QueryBuilder object you can set the index at the from statement:

$qb = $em->createQueryBuilder();
$qb->select('s');
$qb->from('models\Settings', 's', 's.arg');  // here the magic
$result = $qb->getQuery()->getResult();

Then, you can access the object as:

$description = $result['desc'];
$value = $description->getValue();

Upvotes: 68

Related Questions