mysql - 在Sequelize的FROM子句中指定子查询

Question

我正在使用 Sequalize ORM 从数据库中获取数据。我有以下查询要执行，它在 Postgres DB 中运行良好，我想对其进行均衡，具体来说，我的查询与如何均衡 FROM 子句中的子查询部分有关。

SELECT
  "users"."id",
  "users"."user_id" AS "peopleId",
  "users"."first_name" AS "firstName",
  "users"."last_name" AS "lastName",
  "users"."email",
  "users"."mobile",
  "users"."status"
FROM (select * from users where address_id in (select addresses_id from location_view where location = 'Some, Location, India')) as "users"
  LEFT OUTER JOIN "addresses" AS "addresses" ON "users"."address_id" = "addresses"."id"
  LEFT OUTER JOIN "countries" AS "addresses->countries" ON "addresses"."country_id" = "addresses->countries"."id"
  LEFT OUTER JOIN "work_info" AS "work_info" ON "users"."work_info_id" = "work_info"."id"
WHERE
  "users"."status" = 'Active'
ORDER BY
  "users"."first_name" ASC
LIMIT
  5 OFFSET 0;

我知道我们确实有 Sequalize.query 和文字方法来实现这一点，我可以直接以原始格式指定我的子查询，但是无论我用谷歌搜索出什么例子，基本上都提供了一种如何在 WHERE 或 select 中编写原始查询的方法属性子查询条件。
我无法找到一种方法，在其中我将使用主模型的子查询来执行 findAndCountAll，例如，在这种情况下，假设是 usersModel.findAndCountAll。
此外，我需要对其他一些表进行 JOIN，我需要从中选择数据(查询中未提及)。
如何使用 Sequalize 实现相同的目标？

Answer 1

也许您根本不需要子查询？这是相同的查询，但已扁平化。

SELECT
  "users"."id",
  "users"."user_id" AS "peopleId",
  "users"."first_name" AS "firstName",
  "users"."last_name" AS "lastName",
  "users"."email",
  "users"."mobile",
  "users"."status"
FROM "users"
  LEFT OUTER JOIN location_view lv on "users".address_id = lv.addresses_id
  LEFT OUTER JOIN "addresses" AS "addresses" ON "users"."address_id" = "addresses"."id"
  LEFT OUTER JOIN "countries" AS "addresses->countries" ON "addresses"."country_id" = "addresses->countries"."id"
  LEFT OUTER JOIN "work_info" AS "work_info" ON "users"."work_info_id" = "work_info"."id"
WHERE
  "users"."status" = 'Active' AND lv.location = 'Some, Location, India'
ORDER BY
  "users"."first_name" ASC
LIMIT
  5 OFFSET 0;

为了尽可能接近您的查询，我保留了双引号，但请注意，仅当名称包含大写字母和/或非字母数字字符时才需要双引号。