sql - 使用模型关联查找

Question

方言:postgres
数据库版本:@latest
Sequelize 版本:@latest

我正在尝试找出如何使用关联模型。我有 3 个模型: post 、 postCity 和 region 。它们有以下关系:postCity (post_id, region_id) 关联到 post (post_id) 和 region (region_id) 。我正在使用这样的搜索功能:

include: [
    {
      model: models.postCity,
      include:[{model:models.region}],
      attributes: [[models.sequelize.fn('count', 'post_id'), 'count']],
    }
    ],
    where: {
    $or: [
        {
            "create_by" : {$not: 67}
        },
        {
       //   "postCities.region_name":{$iLike: "%Guangazhou2%"}
        },
        {
          "description":{$iLike: "%India%"}
        }
      ]
    }

这导致:

SELECT "post"."post_id", "post"."description", "post"."create_by",
       "post"."create_time", "post"."update_time", "post"."country_id",
       "postCities"."post_id" AS "postCities.post_id",
       "postCities"."region_id" AS "postCities.region_id",
       "postCities"."order_no" AS "postCities.order_no",
       "postCities.region"."region_id" AS "postCities.region.region_id",
       "postCities.region"."region_name" AS "postCities.region.region_name",
       "postCities.region"."country_id" AS "postCities.region.country_id",
       "postCities.region"."province_id" AS "postCities.region.province_id"
FROM "t_post" AS "post"
   LEFT OUTER JOIN "t_post_city" AS "postCities"
      ON "post"."post_id" = "postCities"."post_id"
   LEFT OUTER JOIN "t_region" AS "postCities.region"
      ON "postCities"."region_id" = "postCities.region"."region_id"
WHERE ("post"."create_by" != 67 OR "post"."description" ILIKE '%India%');

当我取消注释 "postCities.region_name":{$iLike: "%Guangazhou2%"} 时，我收到此错误

column post.postCities.region_name does not exist

我只是喜欢我的查询是这样的

... WHERE ("post"."create_by" != 67
           OR "post"."description" ILIKE '%India%'
           OR "postCities.region_name" ILIKE: "%Guangazhou2%")

更新

我也尝试包含 [{model:models.region, where:{"region_name":{$iLike: "%Guangazhou2%"}}}] 但这并没有给我合适的结果。

Answer 1

为了向包含的表添加条件，您应该使用 $ 符号包装条件，如下所示:

include: [{
  model: models.postCity,
  include:[{model:models.region}],
  attributes: [[models.sequelize.fn('count', 'post_id'), 'count']],
}],
where: {
    $or: [{
        "create_by" : {$not: 67}
    }, {
        "$postCities.region.region_name$":{$iLike: "%Guangazhou2%"}
    }, {
        "description":{$iLike: "%India%"}
    }]
}