在哪里sequelize.fn(...)和something ='something'排序问题进行排序 [英] Sequelize WHERE sequelize.fn(...) AND something='something' ordering issue

问题描述

我有一个Sequelize findOne函数，该函数看起来要选择给定点与多边形(col'geom')相交且状态='active'的行.

I have a Sequelize findOne function that looks to select a row where the given point intersects a polygon (col 'geom') AND status = 'active'.

var point = sequelize.fn('ST_GeomFromText', 'POINT(' + lng + ' ' + lat +')', 4326);
var intersects = sequelize.fn('ST_Intersects', sequelize.col('geom'), point);

GeoCounty.findOne({
  attributes: ['id', 'name' ],
  where: {
    status: 'active',
    $and: intersects
  },
  plain: true
})

截至目前，它工作正常.它产生的SQL看起来像:

As of right now, it works just fine. It produces SQL that looks like:

SELECT "id", "name" FROM "geocounty" AS "geocounty" WHERE "geocounty"."status" = 'active' AND (ST_Intersects("geom", ST_GeomFromText('POINT(-98.025006 43.714735)', 4326))) LIMIT 1;

我真正想要的是:

SELECT "id", "name" FROM "geocounty" AS "geocounty" WHERE (ST_Intersects("geom", ST_GeomFromText('POINT(-98.025006 43.714735)', 4326))) AND "geocounty"."status" = 'active' LIMIT 1;    

表示ST_Intersects子句排在第一位，而AND status ='active'在其后.

which is to say the ST_Intersects clause comes first and the AND status='active' comes after.

我的问题是:
1.以可行的第一种方式执行查询是否会对性能造成任何影响? 2.在Sequelize中是否可以构造这样的where子句?

My questions are:
1. Is there any sort of performance penalty for executing the query the first way which does work? 2. Is there a way to structure a where clause like this in Sequelize?

这不起作用:

GeoCounty.findOne({
  attributes: ['id', 'name' ],
  where: {
    intersects,
    $and: {
      status: 'active'
    }
  },
  plain: true
})    

它产生以下SQL:

SELECT "id", "name" FROM "geocounty" AS "geocounty" WHERE "geocounty"."intersects" = ST_Intersects("geom", ST_GeomFromText('POINT(-98.025006 43.714735)', 4326)) AND ("geocounty"."status" = 'active') LIMIT 1;    

没有geocounty.intersects ...

There is no geocounty.intersects...

推荐答案

在寻找类似问题时，我偶然发现了这篇文章，并为我找到了解决方案，可能会帮助您解决#2.

i stumbled over this post while searching for a similar problem and found a solution for me, that might help you with #2.

我将函数调用包装到了其他地方.我的代码如下所示(可在NodeJs 10.9.0，MariaDB上的Sequelize 4.38.0中运行):

I wrapped the function call into an extra where. My code looks like this (works in NodeJs 10.9.0, Sequelize 4.38.0 on a MariaDB):

Cat.findOne({
  where: {
    color: 'red',
    $and: sequelize.where(sequelize.fn('char_length', sequelize.col('cat_name')), 5)
  }
});
SELECT id, cat_name, color FROM cat_table WHERE color = 'red' AND char_length(cat_name) = 5;

在您的示例中，它看起来像这样(未经测试):

On your example it would look like this (not tested):

var intersects = sequelize.fn('ST_Intersects', sequelize.col('geom'), point);
GeoCounty.findOne({
  attributes: ['id', 'name' ],
  where: {
    $and: sequelize.where(intersects, 1),
    status: 'active'
  },
  plain: true
})

这篇关于在哪里sequelize.fn(...)和something ='something'排序问题进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！