Nested Filtering doesn't work with virtual column

[v4ssi404]

Nested relation filtering works properly with columns but not with virtual column.

Error encountered

[Nest] 29684  - 11/12/2023, 5:54:13 PM   ERROR [ExceptionsHandler] Unknown column '__root_memberOrganizations_rel.isMembershipComplete' in 'where clause'

QueryFailedError: Unknown column '__root_memberOrganizations_rel.isMembershipComplete' in 'where clause'
    at Query.onResult (/home/alberto/WebstormProjects/ad-gestionale/src/driver/mysql/MysqlQueryRunner.ts:222:33)
    at Query.execute (/home/alberto/WebstormProjects/ad-gestionale/node_modules/mysql2/lib/commands/command.js:36:14)
    at PoolConnection.handlePacket (/home/alberto/WebstormProjects/ad-gestionale/node_modules/mysql2/lib/connection.js:478:34)
    at PacketParser.onPacket (/home/alberto/WebstormProjects/ad-gestionale/node_modules/mysql2/lib/connection.js:97:12)
    at PacketParser.executeStart (/home/alberto/WebstormProjects/ad-gestionale/node_modules/mysql2/lib/packet_parser.js:75:16)
    at Socket.<anonymous> (/home/alberto/WebstormProjects/ad-gestionale/node_modules/mysql2/lib/connection.js:104:25)
    at Socket.emit (node:events:514:28)
    at addChunk (node:internal/streams/readable:343:12)
    at readableAddChunk (node:internal/streams/readable:316:9)
    at Socket.Readable.push (node:internal/streams/readable:253:10)

Virtual column (Error)

SELECT DISTINCT
    `distinctAlias`.`__root_id` AS `ids___root_id`
FROM
    (
    SELECT
        `__root`.`id` AS `__root_id`,
        `__root`.`taxCode` AS `__root_taxCode`,
        `__root`.`telephone` AS `__root_telephone`,
        `__root`.`email` AS `__root_email`,
        `__root`.`medicalCertificateDate` AS `__root_medicalCertificateDate`,
        (
        SELECT
            CONCAT(`__root`.name, ' ', `__root`.surname)
    ) AS `__root_fullName`,
    (
    SELECT CASE WHEN
        DATEDIFF(
            CURDATE(), `__root`.medicalCertificateDate) <= 0 THEN 'notValid' WHEN DATEDIFF(
                CURDATE(), `__root`.medicalCertificateDate) > 365 THEN 'expired' WHEN DATEDIFF(
                    CURDATE(), `__root`.medicalCertificateDate) >= 335 THEN 'expiring' ELSE 'valid'
                END
            ) AS `__root_medicalCertificateValidity`,
            `__root_roles_rel`.`name` AS `__root_roles_rel_name`,
            `__root_memberOrganizations_rel`.`id` AS `__root_memberOrganizations_rel_id`,
            `__root_memberOrganizations_rel`.`status` AS `__root_memberOrganizations_rel_status`,
            `__root_memberOrganizations_rel`.`cardNumber` AS `__root_memberOrganizations_rel_cardNumber`,
            `__root_memberOrganizations_rel`.`sportOrganizationId` AS `__root_memberOrganizations_rel_sportOrganizationId`,
            (
            SELECT
                `__root_memberOrganizations_rel`.status = TRUE AND `__root_memberOrganizations_rel`.cardNumber != ''
        ) AS `__root_memberOrganizations_rel_isMembershipComplete`
    FROM
        `member` `__root`
    LEFT JOIN `member_roles_member_role` `__root___root_roles_rel` ON
        `__root___root_roles_rel`.`memberId` = `__root`.`id`
    LEFT JOIN `member_role` `__root_roles_rel` ON
        `__root_roles_rel`.`id` = `__root___root_roles_rel`.`memberRoleId`
    LEFT JOIN `member_organization` `__root_memberOrganizations_rel` ON
        `__root_memberOrganizations_rel`.`memberId` = `__root`.`id`
    WHERE
        1 = 1 AND(
            `__root_memberOrganizations_rel`.`isMembershipComplete` = 1
        )
        ) `distinctAlias`
    ORDER BY
        "__root_fullName" ASC,
        `__root_id` ASC
    LIMIT 20

Normal Column (Works like a charm)

SELECT
    `__root`.`id` AS `__root_id`,
    `__root`.`taxCode` AS `__root_taxCode`,
    `__root`.`telephone` AS `__root_telephone`,
    `__root`.`email` AS `__root_email`,
    `__root`.`medicalCertificateDate` AS `__root_medicalCertificateDate`,
    (
    SELECT
        CONCAT(`__root`.name, ' ', `__root`.surname)
) AS `__root_fullName`,
(
    SELECT CASE WHEN
        DATEDIFF(
            CURDATE(), `__root`.medicalCertificateDate) <= 0 THEN 'notValid' WHEN DATEDIFF(
                CURDATE(), `__root`.medicalCertificateDate) > 365 THEN 'expired' WHEN DATEDIFF(
                    CURDATE(), `__root`.medicalCertificateDate) >= 335 THEN 'expiring' ELSE 'valid'
                END
            ) AS `__root_medicalCertificateValidity`,
            `__root_roles_rel`.`name` AS `__root_roles_rel_name`,
            `__root_memberOrganizations_rel`.`id` AS `__root_memberOrganizations_rel_id`,
            `__root_memberOrganizations_rel`.`status` AS `__root_memberOrganizations_rel_status`,
            `__root_memberOrganizations_rel`.`cardNumber` AS `__root_memberOrganizations_rel_cardNumber`,
            `__root_memberOrganizations_rel`.`sportOrganizationId` AS `__root_memberOrganizations_rel_sportOrganizationId`,
            (
            SELECT
                `__root_memberOrganizations_rel`.status = TRUE AND `__root_memberOrganizations_rel`.cardNumber != ''
        ) AS `__root_memberOrganizations_rel_isMembershipComplete`
    FROM
        `member` `__root`
    LEFT JOIN `member_roles_member_role` `__root___root_roles_rel` ON
        `__root___root_roles_rel`.`memberId` = `__root`.`id`
    LEFT JOIN `member_role` `__root_roles_rel` ON
        `__root_roles_rel`.`id` = `__root___root_roles_rel`.`memberRoleId`
    LEFT JOIN `member_organization` `__root_memberOrganizations_rel` ON
        `__root_memberOrganizations_rel`.`memberId` = `__root`.`id`
    WHERE
        (
            1 = 1 AND(
                `__root_memberOrganizations_rel`.`sportOrganizationId` = 1
            )
        ) AND(`__root`.`id` IN(1, 3))
    ORDER BY
        "__root_fullName" ASC

[vsamofal]

can you write a simple test for reproducing this issue?

we have coverage for virtual cols but probably it's not sufficient

[v4ssi404]

Sorry for the late response, I was busy.

I have forked the repo and created a branch with the test case.

It seems that the problem is the virtual column with the OneToMany relation, when it arrives at the end of extractVirtualProperty the metadata is undefined, and that's seems caused by Typeorm. The columns of toys are not there at all instead home are there.

I have created a test and if you comment isMouse lines and uncomment isBox you will see that with a OneToOne relation works.

Maybe is related to this?

[v4ssi404]

Hello, did you checked the repo?

[Helveg]

Hi! Thanks for the reproducer, I'm subscribing myself to remind me to take a look when I have time (not often).