postgresql - 在带有 Sequelize 的 Where 语句中使用包含的模型

Question

我正在使用带有 Postgres 的 Sequelize 4.38.0。我正在尝试实现搜索功能。但我无法验证查询。我必须检查查询是否与城市匹配。它不一定必须匹配。

我想将城市模型的查询移动到顶层 WHERE，如下所述:http://docs.sequelizejs.com/manual/tutorial/models-usage.html#top-level-where-with-eagerly-loaded-models但我收到此错误:Unhandled rejection SequelizeDatabaseError: missing an entry for the "City"table in the FROM clause

模型定义

const Property = sequelize.define('property', {
  id: {
    type: type.STRING,
    unique: true,
    allowNull: false,
    primaryKey: true
  },
  name: {
    type: type.STRING,
    allowNull: false
  },
  // ...
  isVisible: {
    type: type.BOOLEAN,
    default: false
  }
});


const City = sequelize.define('city', {
  name: {
    type: type.STRING
  },
}, {
  timestamps: false,
});

查询

sequelize.sync().then(() => {
  let query = 'new';
  let whereStatement = {
    $or: [{
        name: {
          $ilike: '%' + query + '%'
        }
      },
      {
        description: {
          $ilike: '%' + query + '%'
        }
      },
      // I get the Error if I uncomment this
      // { 
      //   '$City.name$': {
      //     $ilike: '%' + query + '%'
      //   }
      // }
    ]
  };

  Property.findAndCountAll({
    where: whereStatement,
    include: [{
        model: City,
        where: {
          name: {
            $ilike: '%' + query + '%'
          }
        }
        // I want to move this where to the upper level
        // where inside the OR opertator
      },
      {
        model: Photo
      }
    ],
    distinct: true,
    order: [
      ['createdAt', 'DESC']
    ]
  }).then(properties => {
    console.log(properties.count)
  });
});

实际查询有更多的属性。结果 SQL 是:

Executing (default): SELECT "property".*, "city"."id" AS "city.id", "city"."name" AS "city.name", "city"."provinceId" AS "city.provinceId", "photos"."id" AS "photos.id", "photos"."location" AS "photos.location", "photos"."slug" AS "photos.slug", "photos"."description" AS "photos.description", "photos"."isPrimary" AS "photos.isPrimary", "photos"."createdAt" AS "photos.createdAt", "photos"."updatedAt" AS "photos.updatedAt", "photos"."propertyId" AS "photos.propertyId" FROM (SELECT "property"."id", "property"."webSlug", "property"."name", "property"."description", "property"."direction", "property"."price", "property"."areaConstruction", "property"."areaLot", "property"."isVisible", "property"."isAvailable", "property"."isNegociable", "property"."isRural", "property"."latitude", "property"."longitude", "property"."map_radio", "property"."video_url", "property"."createdAt", "property"."updatedAt", "property"."cityId", "property"."propertyTypeId" FROM "properties" AS "property" WHERE ("property"."name" ILIKE '%My Query%' OR "property"."description" ILIKE '%My Query%' OR "property"."id" ILIKE '%My Query%' OR "properties_city"."name" ILIKE '%My Query%') AND "property"."isVisible" = true ORDER BY "property"."createdAt" DESC LIMIT 10 OFFSET 0) AS "property" LEFT OUTER JOIN "cities" AS "city" ON "property"."cityId" = "city"."id" LEFT OUTER JOIN "photos" AS "photos" ON "property"."id" = "photos"."propertyId" ORDER BY "property"."createdAt" DESC;

---

更新

如果我使用相同的模型和相同的 whereStatement 但我将 findAndCountAll() 函数更改为 findAll() 搜索功能有效适本地，我可以将 '$city.name$': { $ilike: '%' + query + '%' } 添加到上层 WHERE。

Property.findAll({
    where: whereStatement,
    include: [{model: City}, {model: Photo}],
    distinct: true,
    order: [['createdAt', 'DESC']],
  }).then(properties => {
    res.render('search', {
      title: 'Buscar',
      properties: properties,
      query: query,
      propertyTypeId: propertyTypeId,
      propertyTypes: req.propertyTypes,
    })
  })

SQL 输出:

Executing (default): SELECT "property"."id", "property"."webSlug", "property"."name", "property"."description", "property"."direction", "property"."price", "property"."areaConstruction", "property"."areaLot", "property"."isVisible", "property"."isAvailable", "property"."isNegociable", "property"."isRural", "property"."latitude", "property"."longitude", "property"."map_radio", "property"."video_url", "property"."createdAt", "property"."updatedAt", "property"."cityId", "property"."propertyTypeId", "city"."id" AS "city.id", "city"."name" AS "city.name", "city"."provinceId" AS "city.provinceId", "photos"."id" AS "photos.id", "photos"."location" AS "photos.location", "photos"."slug" AS "photos.slug", "photos"."description" AS "photos.description", "photos"."isPrimary" AS "photos.isPrimary", "photos"."createdAt" AS "photos.createdAt", "photos"."updatedAt" AS "photos.updatedAt", "photos"."propertyId" AS "photos.propertyId" FROM "properties" AS "property" LEFT OUTER JOIN "cities" AS "city" ON "property"."cityId" = "city"."id" LEFT OUTER JOIN "photos" AS "photos" ON "property"."id" = "photos"."propertyId" WHERE ("property"."name" ILIKE '%City_Query%' OR "property"."description" ILIKE '%City_Query%' OR "property"."id" ILIKE '%City_Query%' OR "city"."name" ILIKE '%City_Query%') AND "property"."isVisible" = true ORDER BY "property"."createdAt" DESC;

Answer 1

试试这个它应该工作。包括

"duplicating":false

在这

sequelize.sync().then(() => {
      let query = 'new';
      let whereStatement = {
        $or: [{
            name: {
              $ilike: '%' + query + '%'
            }
          },
          {
            description: {
              $ilike: '%' + query + '%'
            }
          },
          { 
             '$City.name$': {
               $ilike: '%' + query + '%'
             }
          }
        ]
      };

      Property.findAndCountAll({
        where: whereStatement,
        include: [{
            model: City,
            attributes: [],
            duplicating:false
          },
          {
            model: Photo
          }
        ],
        distinct: true,
        order: [
          ['createdAt', 'DESC']
        ]
      }).then(properties => {
        console.log(properties.count)
      });
    });