Question: ManyToOne Relation

[ISeeTWizard]

Hi I have 2 tables related to each other with a ManyToOne relation. I wanted now to display in my datatable the data from the relationship and I thought I would make use of a join or so but that doesn't seem to work. Taking your example here: $table->createAdapter(ORMAdapter::class, [ 'entity' => Employee::class, 'query' => function (QueryBuilder $builder) { $builder ->select('e') ->addSelect('c') ->from(Employee::class, 'e') ->leftJoin('e.company', 'c') ; }, ]);
and assuming comapny has a field label I thought I could use than c.label to display the text but that doesn't work. Just getting an error that can't do anything with that field.

Do you have any idea what I could do? Maybe I'm doing something wrong as I'm very new in dev this could easily be especially as I'm not working daily with that.

[ISeeTWizard]

Is something additionaly needed in the entity to make that work? As even choosing the field in my case territory_id I see nothing but he should than display me the numbers instead of the text of the relation, that is at least like I always understood it.

[curry684]

If you're new to development, do not override the query builder. The default will automatically resolve simple entity relationships if you specify them in your fields. Reference the most basic usage example:

$table = $this->createDataTable()
    ->add('firstName', TextColumn::class)
    ->add('lastName', TextColumn::class)
    ->add('company', TextColumn::class, ['field' => 'company.name'])
    ->createAdapter(ORMAdapter::class, [
        'entity' => Employee::class,
    ]);

In this case it is assumed that an Employee has a ManyToOne relationship called company, and we automatically resolve it because it is being referenced as company.name. So we magically create a query on this joining the relationship and selecting the name field.

Before you override the query builder in this bundle, be sure to know exactly how it works and how DQL works. Otherwise you're just poking at 2 unknowns without a clue as to whether any errors arise from not understanding our bundle correctly or just not using Doctrine correctly.

[ISeeTWizard]

Sorry, I completely misunderstood that :( Thanks for the information so I can continue now ;)

[ISeeTWizard]

Maybe I'm simply to dumb but:

   /**
     * @ORM\ManyToOne(targetEntity="App\Entity\SysTerritory", inversedBy="systems")
     */
    private $systerritory;

I have this in my entity systems now (I'm doing some tests)

and in the entity systerritory I have:

    /**
     * @ORM\OneToMany(targetEntity="App\Entity\Systems", mappedBy="systerritory")
     */
    private $systems;

and for the controller I use:

$table = $this->createDataTable()
        ->add('label', TextColumn::class, ['label' => 'Name'])
        ->add('level', TextColumn::class, ['label' => 'Level'])
        ->add('coordinates', TextColumn::class, ['label' => 'Coordinates'])
        ->add('territory', TextColumn::class, ['label' => 'Territory'])
        ->add('systerritory', TextColumn::class, ['field' => 'systerritory.label'])

        ->add('buttons', TwigColumn::class, [
            'className' => 'buttons',
            'label' => 'Options',
            'template' => 'Systems/systemsButtons.html.twig',
            'searchable' => false,
        ])

        ->createAdapter(ORMAdapter::class, [
            'entity' => Systems::class,
            'query' => function (QueryBuilder $builder) {
            $builder
            ->select('systems')
            ->from(Systems::class, 'systems')
            ->orderBy('systems.territory', 'ASC')
            ->addorderBy('systems.level', 'ASC')
            ->addorderBy('systems.label', 'ASC');

            },

            ])
            ->handleRequest($request);

            if ($table->isCallback()) {
                return $table->getResponse();
            }

but I just get nothing displayed looking at the error I see: Undefined index: systerritory

In the systems entity I also have the get and set defined (for adding this is working fine)

    public function getSysterritory(): ?SysTerritory

{
        return $this->systerritory;
    }

    public function setSysterritory(?SysTerritory $systerritory): self
    {
        $this->systerritory = $systerritory;

        return $this;
    }

I simply don't find any solution I even tried by adding something like
* @JoinColumn(name="systerritory_id", referencedColumnName="id") (I found this with google) but no effect...

[curry684]

Can you make your post a lot more readable...?

[ISeeTWizard]

What you mean with more readable? I suppose you mean this because I forgot to but the code as code but it was automaticaly done or you did it before I could? The rest which wasn’t declared as code I did now manually...

[curry684]

Now it more readable after your edits 😉

Undefined index: systerritory

Your query does not join this relationship into the result. As said before it's hard to learn multiple things at the same time, and this is simply a Doctrine error. You're not selecting the field, therefore it's not in the result, therefore our code throws the "Undefined index" error indicating it's trying to get a non-existent field on the result row.

[ISeeTWizard]

Hmm I need than to continue checking to understand.. For me it’s: I have a field named label in my entity systerritory and this one is mapped by a ManyToOne realtionship in my main entity systems so my logic tells me it’s systerritory.label...

I’m trying now querys directly in the database and independently from datatable, maybe I can find what I don’t see for the moment.

But thanks so far...