-
Notifications
You must be signed in to change notification settings - Fork 59
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Doctrine OneToMany related entities are displayed as a single row each instead of one combined row only #98
Comments
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 #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. |
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. 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 #93. |
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: |
@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.... 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); |
I can very well imagine that, @ThaDafinser ! 💦 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? |
Your example worked @ThaDafinser . Do you want to close this issue or remain it open as an improvement? |
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! |
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); |
Hi, tried the GroupConcat magic but I got this...what should I do?
|
After you added it via composer you simply add the functions to your <?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',
),
),
),
),
); |
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 |
Yes thats the way to go. |
Closing this issue, working example provided, also see issue #104. Should I add an example to the docs, @ThaDafinser ? |
@webdevilopers examples are always fine! Thanks in advance |
I will wait for #97 . |
Please provide an example for this. |
@webdevilopers @ghiamar @alihammad-gist please try this diff out: https://gist.github.com/ThaDafinser/01c03483f07385b1af7d but there is one YOU have to inject a |
thanked for you. |
This issue was moved to zfc-datagrid/zfc-datagrid#9 |
I have an contract entity with many items:
The items column uses the phpArray type:
My repository uses this query:
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 #76.
The text was updated successfully, but these errors were encountered: