Iterate with fetch join error

[jkabat]

First of all thanks for nice approach to the problem.

However I'm kind of stuck at the moment, any chance to shed a light on following exception I'm facing?

Iterate with fetch join in class App\Entity\CategoryTranslation using association translatable not allowed.

This is custom query I use:

->createAdapter(ORMAdapter::class, [
    'entity' => Category::class,
    'query' => function (QueryBuilder $builder) {
        $builder
            ->select('c', 't')
            ->from(Category::class, 'c')
            ->leftJoin('c.translations', 't', Expr\Join::WITH, 't.locale = :locale')
            ->setParameter('locale', $this->locale)
        ;
    },

There is only one translation being shown in the listing at one time - can this be fixed by changing hydration type? I'm fairly new to doctrine as well, is possible to solve this kind of problem at all? I know that iterate has some limits, but maybe I'm missing something obvious.

Also I have a question regarding custom types - is possible to use repositories when creating an adapter?

[curry684]

The DQL query itself is fine as far as I can see. The error refers to a translatable association however, which points to something in your entity declaration itself. It would seem the error is in Doctrine therefore. I'd recommend executing the DQL query standalone in the controller to debug it. @shades684 any idea?

Also I have a question regarding custom types - is possible to use repositories when creating an adapter?

The adapters bridge between a data source type and a data table implementation. The provided ORMAdapter works implicitly through DQL queries and/or ORM Query Builders. If you have custom queries in your repositories you want to show in a table you could just use the ArrayAdapter which renders data from a PHP array of objects/arrays.

[jkabat]

I'm using KnpLabs/DoctrineBehaviors. This is the definition of association:

    /**
     * @var Category
     * @ORM\ManyToOne(targetEntity="Category", inversedBy="translations", cascade={"remove"})
     * @ORM\JoinColumn(name="translatable_id", referencedColumnName="id")
     * @Assert\Valid()
     */
    protected $translatable;

I have tested all possible fetch modes, but exception remains.

[curry684]

Did you have a look at https://github.com/doctrine/doctrine2/issues/5868?

[jkabat]

Yes, but I don't think DISTINCT will help me here.

It seems to me some kind of limit for ITERATE. I don't know Doctrine internals very well, but I'm able to loop over results using doctrine paginator and the same query (without using datatables-bundle).

Also this code is working fine (getResult() instead of iterate()):

$builder
    ->select('c', 't')
    ->from(Category::class, 'c')
    ->leftJoin('c.translations', 't', Expr\Join::WITH, 't.locale = :locale')
    ->setParameter('locale', $this->locale)
    ->getQuery()
    ->getResult()

The other solution would be to lazy load translations for every iteration, which I'm not very big fan of.

[curry684]

Ah now I see, the iterate is the issue. The Doctrine paginator is actually a pretty thin wrapper around injecting LIMIT <count>,<start> in a query, which then uses getResult. I'm sure we used iterate for good reason, not aware that it might bring limitations like this. Worth looking into sometime.

[shades684]

Offending code is in SqlWalker of Doctine

       if ($this->query->getHint(Query::HINT_INTERNAL_ITERATION) === true &&
            (! $this->query->getHint(self::HINT_DISTINCT) || isset($this->selectedClasses[$joinedDqlAlias]))) {
            if ($association instanceof ToManyAssociationMetadata) {
                throw QueryException::iterateWithFetchJoinNotAllowed($owningAssociation);
            }
        }

So if you try to iterate over a query which is joined and the join would return multiple lines because of the to many association we throw an error.

[curry684]

But if that is the case it's a Doctrine limitation and we can't do anything to fix it right? So you should just provide a custom adapter in that case.

[shades684]

Approach @curry684 mentioned is the right one

[azribilel]

Hello @jkabat , Did you find a solution ? I'm stuck in same problem.

[Clorr]

I came across this issue because of the error message. It looks like there is a FetchJoinORMAdapter created for this purpose. I'm still trying to figure out how to use it, but it looks promising ;-)

[Clorr]

Ok, it's very simple and works like a charm!

Only problem I encountered is the option simple_total_query that was missing. You have to specify it explicitly (bug is reported here: https://github.com/omines/datatables-bundle/issues/152 )

My snippet as an example:

            ->createAdapter(FetchJoinORMAdapter::class, [
                'entity' => Client::class,
                'simple_total_query' => false,
                'query' => function (QueryBuilder $builder) {
                    $builder
                        ->select('client, societe')
                        ->from(Client::class, 'client')
                        ->innerJoin('client.societes', 'societe')
                    ;
                },
            ])