使用模型关联查找

Question

方言：Postgres
数据库版本：@latest
续集版本：@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%")

update

我也尝试包括[{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%"}
    }]
}