postgresql - Sequelize - 使用嵌套实体的条件搜索查询

Question

我正在使用 Sequelize 和 Posgresql。我也在使用 sequelize-typescript 。

我有一个模型 Compte 像:

export class Compte extends Model<Compte> {

    @ForeignKey(() => CompteRevision)
    @Column
    derniereRevisionId: number;

    @BelongsTo(() => CompteRevision, 'derniereRevisionId')
    public derniereRevision: CompteRevision;

    @Unique
    @AllowNull(false)
    @Column(DataType.STRING)
    public email: string;
}

和 CompteRevision 像:

export class CompteRevision extends Model<CompteRevision> {

    @Column(DataType.BIGINT)
    public compteId: number;

    @Column(DataType.STRING)
    public nom: string;

    @Column(DataType.STRING)
    public prenom: string;
}

我正在尝试发出搜索请求(基于字符串 searchText)，以检索以下帐户:

Compte.email 是 ILIKE : "%"+ searchText + "%"

或

Compte.derniereRevision.nom 是 ILIKE : "%"+ searchText + "%"

或

Compte.derniereRevision.prenom 是 ILIKE : "%"+ searchText + "%"

在查看了许多关于 SO 的答案后，我尝试这样做:

public async search(searchText: string): Promise<Array<Compte>> {
        return Compte.findAll({
            where: {
                email: { [Server.sequelize.Op.iLike]: "%" + searchText + "%" }
            },
            include: [{  
                model: CompteRevision, where: {
                    nom: { [Server.sequelize.Op.iLike]: "%" + searchText + "%" },
                    prenom: { [Server.sequelize.Op.iLike]: "%" + searchText + "%" }
                }
            }]
        });
    }

但我不确定 include 子句是否在做我想要的。实际上，这个方法总是返回一个空数组。有人知道如何通过检查 CompteRevision 等嵌套实体的条件来执行此类查找请求吗？

Answer 1

干得好 :

return Compte.findAll({
    where: {
        $or : [
            { email: { $iLike : "%" + searchText + "%" } },
            { '$derniereRevision.nom$' : { $iLike : "%" + searchText + "%" }, }, // <--- Magic is here
            { '$derniereRevision.prenom$' : { $iLike : "%" + searchText + "%" } } // <--- Magic is here
        ]
    },
    include: [{  
        model: CompteRevision
    }]
});

NOTES :

$CompteRevision.nom$ , $table_name.column$ syntax is to query on included table , without $$ sequelize will not consider as table name with column. Change this CompteRevision with your table name

When you do query inside include model , it will make inner join with included table , to make it left join you can use required:false, but its not the case that you need here

Instead of using Server.sequelize.Op.iLike long syntax , you can use short operators $iLike , read more