CODEWITHSUNDEEP
sqlsymfonydoctrine-ormdoctrine
juuga
juuga

Reputation: 1314

Why is doctrine not selecting all columns from entity?

I believe doctrine should always select all fields for an entity when it queries it. I'm using Kitpages' DataGrid bundle with the following code:

    // create query builder
    $repository = $this->getDoctrine()->getRepository('MyApplicationBundle:Application');
    $queryBuilder = $repository->createQueryBuilder('a', 'u');
    $queryBuilder->leftJoin('a.created_by', 'u'); // join user info

    $gridConfig = new GridConfig();
    $gridConfig
        ->setCountFieldName('a.id')
        ->addField(new Field('a.name', array('label'=>'col.name', 'sortable' => true, 'filterable'=>true)))
        ->addField(new Field('a.app_type', array('label'=>'col.type', 'sortable' => true, 'filterable'=>true)))
        ->addField(new Field('a.created_by', array(
            'label'=>'col.user',
            'sortable' => true,
            'filterable'=>true,
            'nullIfNotExists'=>true,
            'formatValueCallback' => function($createdBy) {
                if($createdBy instanceof \My\UserBundle\Entity\User)
                    return $createdBy->getName();
            }
        )))
    ;

Now I have some entities where created_by is null, and some where it is an id referencing a row in the fos_user table. I should be getting the name value for the entities that actually have a value in createdBy, but I'm getting empty columns in every row of the grid for the creator.

I'm my logs I can see the SQL that doctrine is fetching.

SELECT a0_.id AS id0, a0_.name AS name1, a0_.app_type AS app_type2, a0_.public AS public3, a0_.enabled AS enabled4, a0_.likegating AS likegating5, a0_.mobile_optimized AS mobile_optimized6, a0_.share_data AS share_data7, a0_.created_date AS created_date8, a0_.updated_date AS updated_date9 FROM application a0_ LEFT JOIN fos_user f1_ ON a0_.created_by_id = f1_.id LIMIT 15 OFFSET 15

It doesn't select a0_.created_by_id at all. How do I get the select to include that field? I also tried changing the Field to 'u.name', but it didn't help.

Notice: In addition to changing my code to include the addSelect('u') like the accepted answer mentions, I also changed my 'formatValueCallback' to treat the variable as an array. Like so:

'formatValueCallback' => function($createdBy) {
    if(isset($createdBy['name']))
        return $createdBy['name'];
}

Upvotes: 1

Views: 6881

Answers (1)

Cerad
Cerad

Reputation: 48893

So basically you are not getting u.name even though a relation exists?

Change:

$queryBuilder = $repository->createQueryBuilder('a', 'u');

The createQueryBuilder only takes one argument which will be an alias for your application object. The 'u' is ignored.

To:

$queryBuilder = $repository->createQueryBuilder('a');
$queryBuilder->addSelect('u');

And that will populate your user entity.

a0_.created_by_id does not show up in your select clause because it is used on the join statement to get the linked user object. created_by_id never actually get's stored in you application object. After making the above change, you can check the sql and will see a bunch of user attributes are now being selected.

===========================================================

And just for future reference, this is the code that your repository uses to make a query builder:

class Doctrine\ORM\EntityRepository

public function createQueryBuilder($alias)
{
    return $this->_em->createQueryBuilder()
        ->select($alias)
        ->from($this->_entityName, $alias);
}

Upvotes: 3

Related Questions