Doctrine OneToMany related entities are displayed as a single row each instead of one combined row only

[ThaDafinser]

From @webdevilopers on May 5, 2014 12:32

I have an contract entity with many items:

/**
 * @ORM\Entity(repositoryClass="Application\Entity\Repositories\ContractRepository")
 * @ORM\Table(name="contracts")
 */
class Contract
{
...
    /**
     * @ORM\OneToMany(targetEntity="Application\Entity\ContractItem", mappedBy="contract")
     */
    private $items;
...
}

/**
 * @ORM\Entity
 * @ORM\Table(name="contract_items")
 */
class ContractItem
{
...
    /**
     * @ORM\ManyToOne(targetEntity="Application\Entity\Contract", inversedBy="items")
     * @ORM\JoinColumn(name="contract_id", referencedColumnName="id")
     */
    private $contract;  
}

The items column uses the phpArray type:

        $col = new Column\Select('id', 'ci');
        $col->setType(new Type\PhpArray());
        $grid->addColumn($col);

My repository uses this query:

        $queryBuilder = $this->_em->createQueryBuilder();
        $queryBuilder->select('c')
            ->from('Application\Entity\Contract', 'c')
            ->leftJoin('c.items', 'ci')
            ->andWhere('c.id = 2')
            ->orderBy('c.' . $orderBy, $order)
        ;

        $q = $queryBuilder->getDql();
        #return $query = $this->_em->createQuery($q);
        return $queryBuilder;  

Instead of return a single row per contract entity and filling the items column with an pre-formatted array of items, jqgrid renders a single per each per item.

For debugging I reduced my query to a single ID with 163 items. My jqgrid genereates 163 rows.

When doing a count on the Doctrine query I get 1 item. In the background we know that Doctrine would indeed select 163 rows but build one object.

Is my configuration wrong?

Maybe related to issue https://github.com/ThaDafinser/ZfcDatagrid/issues/76.

Copied from original issue: ThaDafinser/ZfcDatagrid#98

[ThaDafinser]

From @webdevilopers on May 5, 2014 14:37

This also happens when using bootstrapTable. The only difference is the rendering of the array which looks like php plaintext:

Array
(
    [0] => 95
)

FYI just topic related to issue https://github.com/ThaDafinser/ZfcDatagrid/issues/8.

I also checked my query looking at the array result and it correctely returns one entity with an index items including my collection correctely.

[ThaDafinser]

From @webdevilopers on May 6, 2014 11:52

I have located the problem inside DataSource\Doctrine2\PaginatorFast.php.

It starts with the count() method that aleady returns 163 rows instead of 1. Well, actually there are 163 rows but Doctrine knows how to combine them using one entity only. But counting on the original query (see next method) should definetely return 1. Obviously some of the DQL resetting parts or similar are causing this problem. Maybe a simple method would work since Doctrine does the correct counting itself?

    public function count()
    {
        if ($this->rowCount !== null) {
            return $this->rowCount;
        }

        $q = $this->getQueryBuilder()->getDql();
        $query = $this->getQueryBuilder()->getEntityManager()->createQuery($q);   
        $this->rowCount = count($query);
        return $this->rowCount;
    }

The next problem is inside the getItems method. The DQL used here has already been altered by DataSource\Doctrine2.php inside the execute method.

Here is my DQL example before and after th execute method.

Before:

SELECT c, sb, s FROM Application\Entity\Contract c INNER JOIN c.branch b INNER JOIN c.supplierBranch sb INNER JOIN c.integratorBranch ib INNER JOIN c.projectManager pm LEFT JOIN c.items ci INNER JOIN c.states s WHERE c.compartment = 1 AND c.branch = 2 AND c.id = 2 ORDER BY c.number ASC

After:

SELECT c.id c_id, c.showInOem c_showInOem, s.state s_state, c.updatedAt c_updatedAt, c.number c_number, b.id b_id, c.problemNumber c_problemNumber, c.problemDescription c_problemDescription, ci.id ci_id, sb.name sb_name, ib.name ib_name, pm.displayName pm_displayName, c.isResidentLog c_isResidentLog FROM Application\Entity\Contract c INNER JOIN c.branch b INNER JOIN c.supplierBranch sb INNER JOIN c.integratorBranch ib INNER JOIN c.projectManager pm LEFT JOIN c.items ci INNER JOIN c.states s WHERE c.compartment = 1 AND c.branch = 2 AND c.id = 2 ORDER BY c.number ASC

Dumping the before query correctely returns 1 entity with X items inside my collection states. The after query returns entities - as many as there are inside the states collection.

I simply hacked into it by overwriting the after query with my before query:

    public function getItems($offset, $itemCountPerPage)
    {       
        $qb = $this->getQueryBuilder();
        $qb->setFirstResult($offset)->setMaxResults($itemCountPerPage);

        $q = "SELECT c, sb, s FROM Application\Entity\Contract c INNER JOIN c.branch b INNER JOIN c.supplierBranch sb INNER JOIN c.integratorBranch ib INNER JOIN c.projectManager pm LEFT JOIN c.items ci INNER JOIN c.states s WHERE c.compartment = 1 AND c.branch = 2 AND c.id = 2 ORDER BY c.number ASC";
        $query = $this->getQueryBuilder()->getEntityManager()->createQuery($q);

        return $query->getArrayResult();
    }

and the Datagrid would correctly render only one row with the correct count - using my hack (see above).

Unfortunately no values are displayed since my before query did not include the aliases yet.

The only difference between the queries are the columns added inside the select part. Can this cause these different results?

Related issue https://github.com/ThaDafinser/ZfcDatagrid/issues/93.

[ThaDafinser]

From @webdevilopers on May 6, 2014 12:41

I figured out what actually causes the different results.

As expected:

SELECT c, sb, s
FROM Application\Entity\Contract c
INNER JOIN c.branch b
INNER JOIN c.supplierBranch sb
INNER JOIN c.integratorBranch ib
INNER JOIN c.projectManager pm
LEFT JOIN c.items ci
INNER JOIN c.states s
WHERE c.compartment = 1 AND c.branch = 2 AND c.id = 2 ORDER BY c.number ASC

Unexpected:

SELECT c
FROM Application\Entity\Contract c
INNER JOIN c.branch b
INNER JOIN c.supplierBranch sb
INNER JOIN c.integratorBranch ib
INNER JOIN c.projectManager pm
LEFT JOIN c.items ci
INNER JOIN c.states s
WHERE c.compartment = 1 AND c.branch = 2 AND c.id = 2 ORDER BY c.number ASC

As soon as the aliases of the join tables are left out, the DQL returns the same parent entity each time an element was found inside a joined collection.

Solution: ZfcDatagrid needs to have knowledge of the entitys attribute state - column or collection and the include the join aliases instead of creating one single column alias. E.g. when $col->setType(new Type\PhpArray()); is used?

[ThaDafinser]

@webdevilopers this is reall hard to handle...already thought about it. There are different pagination things and Doctrine special things...believe me especially the Paginator things is crazy.... I had to reimplement Paginator already from Doctrine :cry:

The easiest way to achive this, is a subselect at the column:

//this is your contract entity
        $col = new Column\Select('description', 'training');
        $col->setLabel('Description');
        $col->setWidth(30);
        $grid->addColumn($col);

//this is your contractItem Entity
        $col = new Column\Select(new Expr\Select('
            (
                SELECT
                    GROUP_CONCAT(
                        hierarchy2.titleShort
                    )
                FROM ...\Entity\Training training2
                JOIN training2.hierarchies hierarchy2
                WHERE
                    training2.id = training.id
            )
        '), 'training_hierarchies');

        $col->setLabel('Hierarchy');
        $col->setWidth(35);
        $grid->addColumn($col);

[ThaDafinser]

From @webdevilopers on May 9, 2014 9:37

I can very well imagine that, @ThaDafinser ! :sweat_drops:

I will give it try using your example. I wasn't aware that using Doctrine's Expr\Select like this was possible - similar to the Zend\Db usage.

Did I miss it in the docs or examples?

[ThaDafinser]

From @webdevilopers on May 19, 2014 7:57

Your example worked @ThaDafinser . Do you want to close this issue or remain it open as an improvement?

[ThaDafinser]

From @mgscreativa on June 13, 2014 14:8

Hi @webdevilopers, @ThaDafinser

I have fallen in the same issue! Can you help me?

I have my user entity like this

<?php
namespace User\Entity;

use Zend\Form\Annotation;
use Doctrine\ORM\Mapping as ORM;
use Doctrine\Common\Collections\ArrayCollection;

/**
 * Doctrine ORM implementation of User entity
 * 
 * @ORM\Entity
 * @ORM\Table(name="`user`")
 * @Annotation\Name("User")
 */
class User
{
    /**
     * @var integer
     *
     * @ORM\Column(name="id", type="integer", nullable=false)
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="IDENTITY")
     * @Annotation\Exclude()
     */
    protected $id;

   ...
   ...

    /**
     * @var User\Entity\Role
     * 
     * @ORM\ManyToOne(targetEntity="User\Entity\Role")
     * @ORM\JoinColumn(name="role_id", referencedColumnName="id", nullable=false)
     * @Annotation\Type("DoctrineModule\Form\Element\ObjectSelect")
     * @Annotation\Filter({"name":"StripTags"})
     * @Annotation\Filter({"name":"StringTrim"})
     * @Annotation\Validator({"name":"Digits"})
     * @Annotation\Required(true)
     * @Annotation\Options({
     *   "required":"true",
     *   "empty_option": "Select User Role",
     *   "target_class":"User\Entity\Role",
     *   "property": "name"
     * })
     */
    protected $role;

    /**
     * @var User\Entity\State
     *
     * @ORM\ManyToOne(targetEntity="User\Entity\State")
     * @ORM\JoinColumn(name="state_id", referencedColumnName="id", nullable=false)
     * @Annotation\Type("DoctrineModule\Form\Element\ObjectSelect")
     * @Annotation\Filter({"name":"StripTags"})
     * @Annotation\Filter({"name":"StringTrim"})
     * @Annotation\Validator({"name":"Digits"})
     * @Annotation\Required(true)
     * @Annotation\Options({
     *   "required":"true",
     *   "empty_option": "User State",
     *   "target_class":"User\Entity\State",
     *   "property": "state"
     * })
     */
    protected $state;
    ...
    ...  

As you can see $role and $state are ManyToOne members, and I want to show their names in my datagrid.

Is that possible?, if you can give me some example, it will be great!

Thanks!

[ThaDafinser]

From @webdevilopers on June 16, 2014 8:18

This was the code i used afterwards:

        $col = new Column\Select(new \Doctrine\ORM\Query\Expr\Select('
            (
                SELECT
                    GroupConcat(pt2ptgr.name)
                FROM Application\Entity\Contract contract2
                JOIN contract2.items items2
                JOIN items2.parttype pt2
                JOIN pt2.parttypeGroup pt2ptgr
            )
        '), 'contract_items');  

There are some more levels of joins. I think it's easier to adapt the example provided by @ThaDafinser .

Untested, maybe something like:

//this is your contract entity
        $col = new Column\Select('name', 'user');
        $col->setLabel('User name');
        $col->setWidth(30);
        $grid->addColumn($col);

//this is your contractItem Entity
        $col = new Column\Select(new Expr\Select('
            (
                SELECT
                    GROUP_CONCAT(
                        state.name
                    )
                FROM ...\Entity\User user2
                JOIN user2.states states2
                WHERE
                    user2.id = user.id
            )
        '), 'user_states');

        $col->setLabel('State');
        $col->setWidth(35);
        $grid->addColumn($col);

[ThaDafinser]

From @mgscreativa on June 16, 2014 12:11

Hi, tried the GroupConcat magic but I got this...what should I do?

[Syntax Error] line 0, col 53: Error: Expected known function, got 'GroupConcat'

[ThaDafinser]

From @webdevilopers on June 16, 2014 12:39

GroupConcat refers to the MySQL function GROUP_CONCAT. Since Doctrine DQL` does not support database specific numeric functions you have to define them. Fortunately there is a module for that - unfortunately no longer maintained by @beberlei - but most of the functions are there: https://github.com/beberlei/DoctrineExtensions/blob/master/lib/DoctrineExtensions/Query/Mysql/GroupConcat.php

After you added it via composer you simply add the functions to your /config/doctrine.orm.local.php:

<?php
return array(
    'doctrine' => array(
        'connection' => array(
            // default connection name
            'orm_default' => array(
                'driverClass' => 'Doctrine\DBAL\Driver\PDOMySql\Driver',
                'params' => array(
                    'host'     => 'localhost',
                    'port'     => '3306',
                    'user'     => '',
                    'password' => '',
                    'dbname'   => '',
                )
            )
        ),
        // @see http://stackoverflow.com/questions/19120182/how-to-add-a-custom-dql-function-in-doctrine-2-using-zend-framework-2
        'configuration' => array(
            'orm_default' => array(
                'filters' => array(
                ),
                'numeric_functions' => array(
                    'CAST'          => 'DoctrineExtensions\Query\Mysql\Cast',
                    'COUNTIF'       => 'DoctrineExtensions\Query\Mysql\CountIf',
                    'DATE'          => 'DoctrineExtensions\Query\Mysql\Date',
                    'GROUPCONCAT'   => 'DoctrineExtensions\Query\Mysql\GroupConcat',
                    'IFELSE'        => 'DoctrineExtensions\Query\Mysql\IfElse',
                    'WEEK'          => 'DoctrineExtensions\Query\Mysql\Week',
                    'WEEKDAY'       => 'DoctrineExtensions\Query\Mysql\WeekDay',
                    'MONTH'         => 'DoctrineExtensions\Query\Mysql\Month',
                    'YEAR'          => 'DoctrineExtensions\Query\Mysql\Year',
                    'STRTODATE'     => 'DoctrineExtensions\Query\Mysql\StrToDate',
                    'TIMEDIFF'      => 'DoctrineExtensions\Query\Mysql\Timediff',
                    'TIMETOSEC'     => 'DoctrineExtensions\Query\Mysql\TimeToSec',
                ),
                'datetime_functions' => array(),
                'string_functions'   => array(),
            )
        ),
        'eventmanager' => array(
            'orm_default' => array(
                'subscribers' => array(
                    // pick any listeners you need
                    'Gedmo\Timestampable\TimestampableListener',
                ),
            ),
        ),
    ),
);

[ThaDafinser]

From @mgscreativa on June 16, 2014 13:10

Ohhhh, I see, I completely misundertood GroupContact behaiviour! in my case I needed to have Role.name and Role.id available on the datagrid view, so I thought that something like this was possible, but it's not:

    GroupConcat(Role.name, Role.id)

In my particular case I had have to add one column with subselect for the Role.name and another hidden column subselect for the Role.id

[ThaDafinser]

Yes thats the way to go. No better idea for that...

[ThaDafinser]

From @webdevilopers on June 17, 2014 12:38

Closing this issue, working example provided, also see issue https://github.com/ThaDafinser/ZfcDatagrid/issues/104.

Should I add an example to the docs, @ThaDafinser ?

[ThaDafinser]

@webdevilopers examples are always fine! Thanks in advance

[ThaDafinser]

From @webdevilopers on July 8, 2014 8:41

I will wait for https://github.com/ThaDafinser/ZfcDatagrid/issues/97 .

[ThaDafinser]

From @alihammad-gist on July 20, 2014 13:19

Please provide an example for this.

[ThaDafinser]

@webdevilopers @ghiamar @alihammad-gist please try this diff out: https://gist.github.com/ThaDafinser/01c03483f07385b1af7d

but there is one big problem if you use like it doesn't work (because for Doctrine limitation) Maybe someone has an idea?

YOU have to inject a QueryBuilder object, see here: https://gist.github.com/ThaDafinser/01c03483f07385b1af7d#file-your-php

[ThaDafinser]

From @faviem on January 28, 2016 16:22

thanked for you.