如何在Sequelize中实现PostGIS地理坐标距离排序的ORDER BY写法

核心排序逻辑实现 #

PostGIS的<->距离运算符不属于标准SQL函数，无法直接用sequelize.fn封装，直接用sequelize.literal插入原生SQL片段即可实现对应排序，写法如下：

order: [
  [sequelize.literal(`coordinates <-> ST_MakePoint(${location.lat}, ${location.lng})::geography`), 'ASC']
]

如果参数来自用户输入需要避免SQL注入风险，可改用参数绑定写法：

order: [
  [sequelize.literal('coordinates <-> ST_MakePoint(?, ?)::geography', [location.lat, location.lng]), 'ASC']
]

完整查询参考 #

以下是对应你原SQL逻辑的Sequelize实现示例，你可以根据自己的模型关联关系调整细节：

const { Op } = require('sequelize');

const gigList = await Gig.findAll({
  attributes: [
    'id',
    'gigType',
    'gigCategory',
    'gigTitle',
    'gigDescription',
    'minOrderAmount',
    'unit',
    'unitPrice',
    'stock',
    'sold',
    'expireDate',
    ['userid', 'sellerId'],
    [sequelize.literal('"Grower"."growerType"'), 'sellerType'],
    [sequelize.literal('"Grower"."points"'), 'points'],
    [sequelize.fn('json_build_object',
      'id', sequelize.literal('"nearLocations"."locationId"'),
      'lat', sequelize.literal('"nearLocations"."lat"'),
      'lng', sequelize.literal('"nearLocations"."lng"')
    ), 'location']
  ],
  include: [
    {
      model: User,
      attributes: [],
      include: [
        { model: Customer, attributes: [] },
        { model: Grower, attributes: [] }
      ]
    },
    {
      model: Location,
      as: 'nearLocations',
      attributes: [],
      where: sequelize.literal(`
        "nearLocations"."id" IN (
          SELECT DISTINCT ON ("gigid") id FROM (
            SELECT id, "gigid" FROM "Locations"
            WHERE ST_DWithin(coordinates, ST_MakePoint(${location.lat}, ${location.lng})::geography, ${distance})
            ORDER BY coordinates <-> ST_MakePoint(${location.lat}, ${location.lng})::geography
            LIMIT ${limit}
          ) AS t
        ) AND "nearLocations"."gigid" = "Gigs"."id"
      `)
    }
  ],
  where: {
    expireDate: {
      [Op.gt]: sequelize.literal(`${today}::text::date`)
    }
  },
  order: [[sequelize.literal('"Grower"."points"'), 'DESC']],
  offset: offset,
  limit: 10,
  raw: true
});

注意事项 #

• 请提前确认你的PostgreSQL实例已开启PostGIS扩展，模型间的关联关系已配置正确
• PostGIS的ST_MakePoint默认参数顺序为(经度, 纬度)，如果原SQL的坐标顺序和实际存储规则不符请自行调整
• 所有动态拼接进sequelize.literal的用户输入参数都建议提前做格式校验，避免SQL注入风险

内容的提问来源于stack exchange，提问作者asinduvg