Relacion and count don't show

[tkowalski29]

Hi,

I have problem with relation in DT.

My 2 entitys:

class TransportEntity extends BaseInstitutionEntity
{
    /**
     * @var Collection|null
     *
     * @ORM\OneToMany(targetEntity="TransportElementEntity", mappedBy="transport", cascade={"persist", "remove"})
     */
    private $elements;
}

class TransportElementEntity extends BaseInstitutionEntity
{
    /**
     * @var TransportEntity
     *
     * @ORM\ManyToOne(targetEntity="\App\Entity\PaintThis\TransportEntity", inversedBy="elements")
     * @Gedmo\Versioned
     */
    private $transport;
}

I try get count element in field in Table.

My table is:

->add(
    'number',
    TextColumn::class
)
->add(
    'dateArrive',
    DateTimeColumn::class
)
->add(
    'status',
    TextColumn::class
)
->add(
    'elements',
    TextColumn::class
)
->add(
    'id',
    TextColumn::class
);

createAdapter(
    ORMAdapter::class,
    [
        'entity' => TransportEntity::class,
        'query' => function (QueryBuilder $builder) use ($options) {
            $builder
                ->select('transport')
                ->addSelect('COUNT(elements.id)')
                ->from(TransportEntity::class, 'transport')
                ->leftJoin(TransportElementEntity::class, 'elements', Join::WITH, 'transport.id = elements.transport')
                ->groupBy('transport.id')
                ->orderBy('transport.id', 'DESC');
        },
    ]
)

How can i get get count elements in DT?

[curry684]

This is actually one of the trickiest possible use cases as SQL inherently needs to join and group to indicate counts. I believe @shades684 has done some work to this end and may be able to provide some insights on how to best approach.

[shades684]

Doctrine will actually return an array of of values (one with the entity and one with the count) for each result in that query. The ORM adapter only supports arrays of single objects at this point. I know a way to do this completely custom(it involves a custom orm adapter and adding the property path to each column definition), but based on your example this isn't needed.

The easiest way to accompish this is removing the join and the group and implementing a getTransportElementEntityCount() on the TransportEntity, which you can use in your column definitions. If you use the EXTRA_LAZY option on the association, the overhead should be doable.

[curry684]

Think we should add some sort of cookbook to the docs to cover common cases like this.

[shades684]

I was thinking along the lines of an examples project, that helped me in other projects

[curry684]

There is one 😉

[shades684]

I made a custom example in my latest commit. It uses:

• A custom ORM Adapter for returning all values returned in the group by query and using foreach in stead of iteratable
• Custom property paths in the datatable type, to directly access the returned results, because the automatic resolver won't know what to look for anymore
• A test that shows it reurns the expected result

Hope that helps

[curry684]

Fixed in 33fb34a71705f92c11e689f70c74e378ce57e44d

[mschrading]

Hi together. On groupBy I'll get a bunch of arrays which can't be iterated! Is this true? When I using groupBy how to use this in a datatable and view this results with foreach? Is there any example please

Thank you

[ducho]

Hi together. On groupBy I'll get a bunch of arrays which can't be iterated! Is this true? When I using groupBy how to use this in a datatable and view this results with foreach? Is there any example please

Thank you

Hi @mschrading! Did you manage to solve it?

[ducho]

Hydrate param not working, groupby not working... :( ERROR: The query returned multiple rows, change the query or use different resul function like getScalarResult()

The problem is is getCount() method becasue if $this->hasGroupByPart() is true more than one row is expected. I fiexd it currently on line 225: return (int) count($qb->getQuery()->getScalarResult());

    /**
     * @param $identifier
     *
     * @return int
     */
    protected function getCount(QueryBuilder $queryBuilder, $identifier)
    {
        $qb = clone $queryBuilder;

        $qb->resetDQLPart('orderBy');
        $gb = $qb->getDQLPart('groupBy');
        if (empty($gb) || !$this->hasGroupByPart($identifier, $gb)) {
            $qb->select($qb->expr()->count($identifier));

            return (int) count($qb->getQuery()->getScalarResult());
        } else {
            $qb->resetDQLPart('groupBy');
            $qb->select($qb->expr()->countDistinct($identifier));

            return (int) $qb->getQuery()->getSingleScalarResult();
        }
    }

@curry684 @shades684 Do you plan to fixt it or implement it in the near future? Or will this repository no longer be maintained? Because I don't understand why you integrate SF6 functionality when bugs are not fixed for SF5.