按位置和顺序对复杂的聚合属性进行续集 [英] Sequelize complex aggregate attribute in where and order

问题描述

您好，我正在尝试使用 Sequelize 来执行基于位置的查询，但我做噩梦了！

Hello I'm trying to use Sequelize to perform location based queries, and I'm having a nightmare!

我正在尝试生成 SQL:

I'm trying to generate the SQL:

SELECT *, (3959 * acos(cos(radians([user-latitude])) * cos(radians(latitude)) * cos(radians(longitude) - radians([user-longitude])) + sin(radians([user-latitude])) * sin(radians(latitude)))) AS distance FROM myModel HAVING distance <= 25 ORDER BY distance ASC LIMIT 0 , 10;

其中 [user-latitude] 和 [user-longitude] 是变量.到目前为止，我已经有了这个:

where [user-latitude] and [user-longitude] are variables. So far I've got this:

myModel.findAll({
    attributes: [
        '*',
        [`(3959 * acos(cos(radians(${user-latitude})) * cos(radians(latitude)) * cos(radians(longitude) - radians(${user-longitude})) + sin(radians(${user-latitude})) * sin(radians(latitude))))`, 'distance'],
    ],
    where: {
        distance: {
            $lte: 25,
        },
     },
    order: [
        ['distance', 'ASC'],
    ],
    limit: 10,
    offset: 0,
});

产生:

SELECT *, (3959 * acos(cos(radians([user-latitude])) * cos(radians(latitude)) * cos(radians(longitude) - radians([user-longitude])) + sin(radians([user-latitude)) * sin(radians(latitude)))) AS `distance` FROM `myModels` AS `myModel` WHERE `myModel`.`distance` <= 15 ORDER BY `myModel`.`distance` ASC LIMIT 0, 10;

这不起作用，因为 `myModel`.`distance` 不是一个字段.有没有办法在不使用原始查询的情况下完成这项工作?

which doesn't work because `myModel`.`distance` is not a field. Is there a way to make this work without using raw queries?

推荐答案

很遗憾，您不能在 WHERE 或 HAVING 语句中使用别名字段，只能在 中使用订购方式.您必须在 WHERE 子句中重复您的语句，而不是使用别名(正如 SQL 在 Where 语句中使用别名).

Unfortunately you cannot use alias field in the WHERE or HAVING statement, only in the ORDER BY. You must repeat your statement in the WHERE clause instead using alias (just as it is explained in SQL Use alias in Where statement).

另外，你得到的错误是因为当你在where和order属性中使用字段distance时，Sequelize会自动处理它作为 myModel 的一个字段而不是你的 alias，所以你需要按字面写它，这样它就不会被当作你选择的表的列.

What is more, the error you obtained happens because when you used field distance in the where and order attributes, Sequelize automatically treated it as a field of myModel instead your alias, so you need to write it literally so it won't be treated as a column of table you select.

myModel.findAll({
    attributes: {
        include: [[`(3959 * acos(cos(radians(${user-latitude})) * cos(radians(latitude)) * cos(radians(longitude) - radians(${user-longitude})) + sin(radians(${user-latitude})) * sin(radians(latitude))))`, 'distance']]
    },
    where: sequelize.where(
        sequelize.literal(`(3959 * acos(cos(radians(${user-latitude})) * cos(radians(latitude)) * cos(radians(longitude) - radians(${user-longitude})) + sin(radians(${user-latitude})) * sin(radians(latitude))))`),
        '<=',
        25
    ),
    order: 'distance ASC',
    limit: 10,
    offset: 0
});

sequelize 在这种情况下是您的 Sequelize 实例.

sequelize in this case is your instance of Sequelize.

这篇关于按位置和顺序对复杂的聚合属性进行续集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！