Scott Scott - 29 days ago 21
Node.js Question

How to perform raw PostGIS queries with includes using sequelize

Trying to use sequelize's built in ORM convenience include syntax with a raw PostGIS query.

This call correctly joins the related promotionImage and category tables just as I need it, but it doesn't geo query my database like I need it to:

promotion.findAll({
include: [{
model: promotionImage
}, {
model: category
}]
}).then(promoters => {
res.json(promoters)
}).catch(err => {
console.log('ERROR: ', err)
})


On the other hand, this query correctly finds the "promotions" within the provided lat/lng bounds, but it does not correctly join the associated promotionImage and category tables:

const query = '\
SELECT * \
FROM "promotions" \
WHERE "promotions"."location" && ST_MakeEnvelope(:southWestLng, :southWestLat, :northEastLng, :northEastLat)'
replacements = {
southWestLng,
southWestLat,
northEastLng,
northEastLat
}

db.sequelize.query(query, {
replacements,
type: sequelize.QueryTypes.SELECT,
model: promotion,
include: [{
model: promotionImage
}, {
model: category
}]
}).then(promoters => {
res.json(promoters)
}).catch(err => {
console.log('ERROR: ', err)
})


Is there a way to both query using the
ST_MakeEnvelope
syntax and include the data from the other models with sequelize and postGIS?

Answer

Sequelize.query() doesn't support include, you can use Sequelize.fn() with findAll

 promotion.findAll({
    where: {
       location: {
           $overlap: db.sequelize.fn('ST_MakeEnvelope', southWestLng, southWestLat, northEastLng, northEastLat)
       }
    },
    include: [{
      model: promotionImage
    }, {
      model: category
    }]
  }).then(promoters => {
    res.json(promoters)
  }).catch(err => {
    console.log('ERROR: ', err)
  })