M2M 非重复选择的筛选器写法和 SQL

这个筛选器很难想到。在观察了 Directus App 的请求后才找出来：

const {
    data: availablePerformers,
    run: runQueryAvailablePerformers,
    loading: loadingAvailablePerformers,
} = useRequest(async () => {
    return await directus.request(readItems('cat_performer', {
        fields: [
            'id',
            'name',
            'photo',
        ],
        filter: {
            '$FOLLOW(cat_program_performer, performer_id)': {
                _none: { program_id: { _eq: programId } },
            },
        },
    }))
}, { manual: true })

$FOLLOW 用法的文档在 https://directus.io/docs/guides/connect/filter-rules#follow-syntax

其用途是在仅仅有 M2O 关联的时候，虚拟出 O2M 字段。

将这个虚拟出的 O2M 字段作为 Field Key, 以 _none 操作符去查找不符合条件的记录，就是 M2M 的非重复选择。（因为符合条件的记录，就是已经建立了 M2M 关联的记录。）

最后，记录一下 Directus 为这个查询生成的 SQL

select
    CASE
        WHEN `inner`.`eibfe_id` > 0 THEN `cat_performer`.`id`
    END as `id`,
    CASE
        WHEN `inner`.`eibfe_name` > 0 THEN `cat_performer`.`name`
    END as `name`,
    CASE
        WHEN `inner`.`eibfe_photo` > 0 THEN `cat_performer`.`photo`
    END as `photo`
from
    `cat_performer`
    inner join (
        select
            `cat_performer`.`id`,
            `cat_performer`.`sort` as `gvoiw`,
            COUNT(
                (
                    CASE
                        WHEN ((`cat_performer`.`user_created` = ?)) THEN 1
                    END
                )
            ) AS `eibfe_id`,
            COUNT(
                (
                    CASE
                        WHEN ((`cat_performer`.`user_created` = ?)) THEN 1
                    END
                )
            ) AS `eibfe_name`,
            COUNT(
                (
                    CASE
                        WHEN ((`cat_performer`.`user_created` = ?)) THEN 1
                    END
                )
            ) AS `eibfe_photo`
        from
            `cat_performer`
            left join `cat_program_performer` as `mmgxr` on `cat_performer`.`id` = `mmgxr`.`performer_id`
        where
            (
                `cat_performer`.`id` not in (
                    select
                        `cat_program_performer`.`performer_id` as `performer_id`
                    from
                        `cat_program_performer`
                        left join `cat_program` as `gadks` on `cat_program_performer`.`program_id` = `gadks`.`id`
                        left join `directus_users` as `lwgfr` on `gadks`.`user_created` = `lwgfr`.`id`
                        left join `cat_performer` as `kfuog` on `cat_program_performer`.`performer_id` = `kfuog`.`id`
                        left join `directus_users` as `djagy` on `kfuog`.`user_created` = `djagy`.`id`
                    where
                        `cat_program_performer`.`performer_id` is not null
                        and (
                            `cat_program_performer`.`program_id` = ?
                            and (
                                (
                                    `gadks`.`user_created` = ?
                                    and `kfuog`.`user_created` = ?
                                )
                            )
                        )
                )
                and ((`cat_performer`.`user_created` = ?))
            )
        group by
            `cat_performer`.`id`
        order by
            `cat_performer`.`sort` asc
        limit
            ?
    ) as `inner` on `cat_performer`.`id` = `inner`.`id`
order by
    `inner`.`gvoiw` asc

[
    dd1d3f34-6a3b-474a-8bf8-136a0e91ff2e, 
    dd1d3f34-6a3b-474a-8bf8-136a0e91ff2e, 
    dd1d3f34-6a3b-474a-8bf8-136a0e91ff2e, 
    37cff23f-3721-4154-ab9c-1faaf354cdc7, 
    dd1d3f34-6a3b-474a-8bf8-136a0e91ff2e, 
    dd1d3f34-6a3b-474a-8bf8-136a0e91ff2e, 
    dd1d3f34-6a3b-474a-8bf8-136a0e91ff2e, 
    100
]