Invalid DQL query generated when filtering on a column that represents a one-to-many relationship

[reckybalboa]

Hello,

I have a question which might be a bug (either in doctrine or the bundle).

I have these two entities: user and email. A user can have multiple email addresses.

I want a user grid which includes all the email addresses on separate lines. I was able to construct the grid and have it displayed as I wanted by doing the following:

Annotation on the user entity class : @GRID\Source(groupBy={"id"})

Annotation on the user entity class for the private class variable $emails: @GRID\Column(field="emails.email:group_concat", title="Email", groupBy={"id"})

Source definition in the controller: $source = new Entity('UserBundle:User');

Manipulation of the cell rendering to get the emails show up as mailto links:

$grid->getColumn('emails.email:group_concat')
    ->manipulateRenderCell(function ($value, $row) {
        $emailArray = explode(',', $value);
        $result = '';
        foreach ($emailArray as $email) {
            $result .= '<a href="mailto:'.$email.'">'.$email.'</a><br />';
        }
        return $result;
    });

By doing this everything seems to be OK. The rendering is fine and there is a column filter shown on the grid. But if I try to filter on an email address I get the following error:

[Syntax Error] line 0, col 698: Error: Expected '.' or '(', got 'emails__dot__email__col__group_concat'

The generated DQL query is:

SELECT _a.createdAt, _a.updatedAt, _a.id, _a.firstName, _a.lastName, _a.password, group_concat(_emails.email) as emails__dot__email__col__group_concat, _a.birthday, _a.website, _a.phoneNumber, _a.phoneNumberMobile, _a.fax, _a.nationalityCountryId, _a.regionalNUTSCode, _a.isDeleted, _a.isExpert, _a.encryption, _a.isActive, _a.activationToken, _a.activationTokenSetAt, _a.activatedAt, _a.passwordResetToken, _a.passwordResetRequestedAt, _a.passwordResetAt, _a.newsletterSubscription, _a.orcidId, _a.cookieConsent, _a.cookieConsentExpiryDate, _a.lastLoginDate, CONCAT(_a.firstName, ' ', _a.lastName) AS fullName FROM UserBundle\Entity\User _a LEFT JOIN _a.emails _emails GROUP BY _a.id HAVING LOWER(emails__dot__email__col__group_concat) LIKE LOWER(?123)

Yet the following DQL query runs just fine:

$dql = "SELECT _a.createdAt, _a.updatedAt, _a.id, _a.firstName, _a.lastName, _a.password, group_concat(_emails.email) as emails__dot__email__col__group_concat, _a.birthday, _a.website, _a.phoneNumber, _a.phoneNumberMobile, _a.fax, _a.nationalityCountryId, _a.regionalNUTSCode, _a.isDeleted, _a.isExpert, _a.encryption, _a.isActive, _a.activationToken, _a.activationTokenSetAt, _a.activatedAt, _a.passwordResetToken, _a.passwordResetRequestedAt, _a.passwordResetAt, _a.newsletterSubscription, _a.orcidId, _a.cookieConsent, _a.cookieConsentExpiryDate, _a.lastLoginDate, CONCAT(_a.firstName, ' ', _a.lastName) AS fullName FROM UserBundle\Entity\User _a LEFT JOIN _a.emails _emails GROUP BY _a.id HAVING LOWER(group_concat(_emails.email)) LIKE LOWER('master')";

Notice the difference in the 'HAVING LOWER(' condition.

Any thoughts or recommendations would be really welcome. Thank you in advance.

[DonCallisto]

I think this is a bug introduced with latest releases. I'll try to take a look at this ASAP but I don't know when I'll have free time. Hopefully other contributors will take action before me, but I'm afraid they won't...

[DonCallisto]

I tried to look at this in my project but I’m not able to replicate. Can you make a repo example where we can test this wrong behavior? Thanks

[romaricdrigon]

I run into the same issue, and reported it to Doctrine (https://github.com/doctrine/orm/pull/7847). Sadly apparently it is a won't fix, DQL functions over aliases and not supported.