Double nested entities problem

[konradja100]

I've found some issues with double nested relation between entities. Entity "SiteVisit" has ManyToOne relation with "Ip", and "Ip" has ManyToOne relation with "Company".

Everything is working fine, but when i try to search by Company.name field, i get this error:

My builder fields:

$this->columnBuilder
                ->add('id', Column::class, [
                    'title' => 'Id',
                    'searchable' => false
                ])
                ->add('newStartedAt', DateTimeColumn::class, [
                    'title' => $this->translator->trans('site_visit.started_at'),
                    'date_format' => 'YYYY-MM-DD hh:mm:ss',
                    'name' => 'datefilter',
                    'filter' => [DateRangeFilter::class, [
                        'cancel_button' => true,
                    ]],
                ])
                ->add('ip.number', Column::class, [
                    'title' => $this->translator->trans('ip.number'),
                    'filter' => [TextFilter::class,
                        [
                            'cancel_button' => true,
                        ],
                ]])
                ->add('ip.status', Column::class, [
                    'title' => $this->translator->trans('ip.status'),
                    'filter' => [SelectFilter::class,
                        [
                            'multiple' => false,
                            'cancel_button' => false,
                            'search_type' => 'eq',
                            'select_options' =>
                                [ '' => $this->translator->trans('label.all') ]
                                + $this->getOptionsArrayFromEntities($ipStatuses, 'id', 'name')
                        ],
                ]])
                ->add('ip.country.alpha2Code', Column::class, [
                    'title' => $this->translator->trans('ip.country_code'),
                    'filter' => [TextFilter::class,
                        [
                            'cancel_button' => true,
                        ],
                ]])
                ->add('ip.hostname', Column::class, [
                    'title' => $this->translator->trans('ip.hostname'),
                    'filter' => [TextFilter::class,
                        [
                            'cancel_button' => true,
                        ],
                ]])
                ->add('ip.company.name', Column::class, [
                    'title' => $this->translator->trans('ip.company_name'),
                    'default_content' => '',
                    'filter' => [TextFilter::class,
                        [
                            'cancel_button' => true,
                        ],
                ]])
                ->add('ip.status.name', Column::class, [
                    'searchable' => false,
                    'visible' => false,
                ])
                ->add(null, ActionColumn::class, [
                    'title' => '',
                    'actions' => [
                        [
                            'route' => 'app_site_visit_views',
                            'route_parameters' => ['site' => 'site.id', 'id' => 'id'],
                            'label' => $this->translator->trans('action.show'),
                            'attributes' => [
                                'rel' => 'tooltip',
                                'title' => $this->translator->trans('action.show'),
                                'class' => 'btn btn-action btn-sm btn-default',
                                'role' => 'button',
                            ],
                        ],
                    ],
                ])
            ;

So as you can see, other nested relations are working fine. Relating to this issue: #826 I've aslo tried to add this fields:

                ->add('ip.company.id', Column::class, [
                    'searchable' => false,
                    'visible' => false,
                ])
                ->add('ip.id', Column::class, [
                    'searchable' => false,
                    'visible' => false,
                ])

but it's still the same. Any ideas? You can notice that company field in Ip entity is nullable.

[ProfM2]

You have a couple of options.

1. Use getLineFormatter, in there pull the appropriate repository entity referenced from your datatable entity and place whatever data you want into a VirtualColumn

Something like this could work for you ... (located in your EntityDatatable class)

    public function getLineFormatter() {
      $formatter = function($line){
        $subLoc = $this->getEntityManager()->getRepository(SubscriberLocation::class)->find($line['id']);
        /** @var Subscriber $subscriber */
        $subscriber = $subLoc->getSubscriber();

        if (count($subscriber->getSubscriberLocation()) > 1)
            $line['virtual_contactName'] = $line['contactName'] . '  (' . $line['locationName'] . ')';
        else
            $line['virtual_contactName'] = $line['contactName'];

        return $line;
      };
      return $formatter;
    }

2. Modify your datatableQueryBuilder in the controller to join your tables before it gets to the datatable.

This code would be located in your Controller for the datatable, and you would then use the QueryBuilder to join whichever tables you want. This example filters out any non-enabled users in my project.

    try {
        $datatable = $this->dtFactory->create(UserDatatable::class);
        $datatable->buildDatatable();
        if ($isAjax) {
            $responseService = $this->dtResponse;
            $responseService->setDatatable($datatable);
            $datatableQueryBuilder = $responseService->getDatatableQueryBuilder();

            /* Get only users that are enabled */
            /** @var QueryBuilder $qb */
            $qb = $datatableQueryBuilder->getQb();
            // NOTE: entityName must preface a non-relational property
            $qb->andWhere("user.enabled = :c");
            // DO WHATEVER JOINS YOU WANT TO DO HERE ...
            $qb->setParameter('c', true);

            return $responseService->getResponse();
        }

        return $this->render('admin/user_home.html.twig', array(
            'datatable' => $datatable,
        ));
    }

I don't know if there is a "best-practices" for what you're trying to accomplish.