Easyadminbundle: setDefaultSort not working when sorting on association field

[picks44]

Describe the bug
I'm trying to configure CRUD to sort entities by a field contained within an association

 public function configureCrud(Crud $crud): Crud
    {
        return $crud
            ->setEntityLabelInSingular('User')
            ->setEntityLabelInPlural('Users')
            ->setSearchFields(['email'])
            ->setPaginatorPageSize(100)
            ->setDefaultSort(['account.last_name' => 'ASC'])
            ->setEntityPermission('ROLE_ADMIN');
    }

Each User entity has a Account associated.

The error raided is [Semantical Error] line 0, col 138 near 'last_name AS': Error: Class App\Entity\User has no field or association named account.last_name and the query exception SELECT entity FROM App\Entity\User entity LEFT JOIN entity.account account WHERE entity.roles LIKE '%ROLE_ADMIN%' ORDER BY entity.account.last_name ASC

User.php

<?php

namespace App\Entity;

use Doctrine\Common\Collections\ArrayCollection;
use Doctrine\Common\Collections\Collection;
use Doctrine\ORM\Mapping as ORM;
use Symfony\Component\Security\Core\User\UserInterface;
use Symfony\Component\Validator\Constraints as Assert;

/**
 * @ORM\Entity(repositoryClass="App\Repository\UserRepository")
 */
class User implements UserInterface
{
    /**
     * @ORM\Id()
     * @ORM\GeneratedValue()
     * @ORM\Column(type="integer")
     */
    private $id;

    /**
     * @ORM\OneToOne(targetEntity="App\Entity\Account", mappedBy="user", cascade={"persist", "remove"})
     * @Assert\Valid()
     */
    private $account;

    public function getAccount(): ?Account
    {
        return $this->account;
    }

    public function setAccount(?Account $account): self
    {
        $this->account = $account;

        // set (or unset) the owning side of the relation if necessary
        $newUser = null === $account ? null : $this;
        if ($account->getUser() !== $newUser) {
            $account->setUser($newUser);
        }

        return $this;
    }
}

Account.php

<?php

namespace App\Entity;

use Doctrine\ORM\Mapping as ORM;
use Symfony\Component\HttpFoundation\File\File;
use Symfony\Component\Validator\Constraints as Assert;
use Vich\UploaderBundle\Mapping\Annotation as Vich;

/**
 * @ORM\Entity(repositoryClass="App\Repository\AccountRepository")
 * @Vich\Uploadable
 */
class Account implements \Serializable
{
    /**
     * @ORM\Id()
     * @ORM\GeneratedValue()
     * @ORM\Column(type="integer")
     */
    private $id;

    /**
     * @ORM\OneToOne(targetEntity="App\Entity\User", cascade={"persist", "remove"}, inversedBy="account")
     */
    private $user;


    public function getUser(): ?User
    {
        return $this->user;
    }

    public function setUser(?User $user): self
    {
        $this->user = $user;

        return $this;
    }
}

[picks44]

It looks like the ON statement is missing when the LEFT JOIN is set in the SQL request, no?

[krewetka]

There is similar problem when you try to add this value to ->setSearchFields - it does not work as well.

Btw, problem in SQL above is ORDER BY entity.account.last_name -> should be just ORDER BY account.last_name

[jgwiazdowski]

Issue above is not only related with default sorting, if you actually have a field like account.last_name in your fields list and then try to sort by that column (by clicking on it) you would get a same error,
I tried to do a join in createIndexQueryBuilder but then I end up with an error like Error: 'trade' is already defined. (if your case it would probably say "account" is already defined)

[picks44]

@krewetka @jgwiazdowski yes, you are both correct.

[jgwiazdowski]

what looks like an issue - in my opinion - is this, let's look at my example, in my case I have Transaction and Trade entities, one Trade may have many Transactions and one Transaction may have only on Trade,
so it's just typical ManyToOne

here is query which fails
SELECT entity FROM App\Entity\Transaction entity LEFT JOIN entity.trade trade ORDER BY entity.trade.status DESC

shouldn't that be actually be this?
SELECT entity FROM App\Entity\Transaction entity LEFT JOIN entity.trade trade ORDER BY trade.status DESC
?
it's basically an attempt to try to sort by status from trade entity

[malteschlueter]

This incident comes from this change https://github.com/EasyCorp/EasyAdminBundle/commit/80e2cc78c301c999f1838a91eaa7d58106e887d2

For people who can't wait for a fix i recommend downgrade to 3.1.5.

[krewetka]

yes, in my opinion it should be like this second query.

in general it looks like easy admin cannot figure out right now that this items with dot syntax are the ones from related entities and try to treat it as properties from main entity

Btw @jgwiazdowski I can sort by entity. But I don't have account.last_name as column in index. I have just account and it has toString() which is returning last_name ( in this example) and then it works fine

[krewetka]

@malteschlueter good catch. It think it fixed the type of sorting which I am using i.e with column just for account and not acocunt.last_name .

Probably workaround can be also using my solution and putting there whole entity and not selected property as field in index list.

[jgwiazdowski]

@krewetka yes, I've just realized I can sort by entity, but unfortunately I need to sort by multiple columns, so it's not exactly solution for me :/
good catch @malteschlueter

one extra note, downgrade to 3.1.5 will fix issue above, but then one cannot sort by related entity directly - like @krewetka mentioned

[malteschlueter]

Thank you @jgwiazdowski and @krewetka.

I prepared a little fix that will work with both types of sorting. WDYT? https://github.com/EasyCorp/EasyAdminBundle/pull/3886

[jgwiazdowski]

@malteschlueter seems to be working fine, many thanks

[malteschlueter]

FYI
I tried to use the the entity instead of the property but then is my sorting incorrect even if i have a __toString method.

[krewetka]

@malteschlueter after double checking my option sorts by releted entity id - not really useful :see_no_evil:

In my opinion your fix looks good. Hope it will be ok for @javiereguiluz and will be merged soon :crossed_fingers:

[krewetka]

Btw, there is another issue with quite nice solution proposed in one on PRs.

It looks like fixing that other issue broke this use case.

[jgwiazdowski]

hmm not sure whether it's just me, but didn't "we" actually messed up filters now? I mean filter be related entity?

return $filters->add(DateTimeFilter::new('trade.createdAt', 'Signed Date'))

Call to a member function getAsDto() on array

or perhaps it doesn't work at all and I've realized that :|

[krewetka]

@jgwiazdowski - were these filters working for you earlier? Can you check after reverting this change?

Changes in addOrderClause should not be related in theory to filters :)

I wil try to test it in my scenario tomorrow :crossed_fingers: Especially PR got approved by @xabbuh today.

So would be good to be sure it didn't mess other cases.