Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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
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;
What I really want is:    
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;    
which is to say the ST_Intersects clause comes first and the AND status='active' comes after.    
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?    
This does not work:    
GeoCounty.findOne({
  attributes: ['id', 'name' ],
  where: {
    intersects,
    $and: {
      status: 'active'
  plain: true
It produces this 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;    
There is no geocounty.intersects...
i stumbled over this post while searching for a similar problem and found a solution for me, that might help you with #2. 
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
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.