我有一个 Sequelize findOne 函数,它会选择给定点与多边形(col 'geom')相交且状态 = '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 如下所示:
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' 在后。
我的问题是:
1. 以第一种有效方式执行查询是否会带来任何性能损失?
2. 有没有办法在 Sequelize 中构造这样的 where 子句?
这不起作用:
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...