Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Sequelize WHERE sequelize.fn(...) AND something='something' ordering issue

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...

like image 623
Chase Avatar asked Apr 07 '17 09:04

Chase


People also ask

What is FN in Sequelize?

Function fnCreates a object representing a database function. This can be used in search queries, both in where and order parts, and as default values in column definitions. If you want to refer to columns in your function, you should use sequelize.

How do I use order by in Sequelize?

Order by included modelfindAll({ order: [[Survey_Questions, "orderIndex", "ASC"]], include: { model: Survey_Questions, }, }); We added model to include and the same model is used in order params, where we declare that we want to order results by orderIndex from joined table.


1 Answers

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
})
like image 97
Maik Avatar answered Nov 13 '22 18:11

Maik