Squelize - geolocation in places where MariaDB is used

Question

I have a node.js application using MariaDB. So far, all my SQL are in stored procedures.

I am considering Sequelize, the only thing I did not find there - what I need is to use functions in cases where there are sentences.

I have something like this in my current request:

Select * from places p
where ST_WITHIN(p.geolocation, ST_BUFFER(GeomFromText(in_geolocation), radius)) = 1

(in_gelocation and radius are SP parameters).

Is there anyway to do this in Sequelize or another ORM?

thank

Answer 1

Try using Sequelize.fn:

, . , , , , .

http://docs.sequelizejs.com/en/latest/api/sequelize/#fn-fn-args-sequelizefn

Answer 2

Sequelize , . SQL where.

if(!query.includes(',') && lat && lng) {
    attributes.push([
        DataTypes.fn('concat',
        DataTypes.col('city'),
        DataTypes.col('state')
    ),'city_state'])
    attributes.push([
        DataTypes.fn('ST_Distance',
        DataTypes.col('location'),
        DataTypes.literal('ST_GeomFromText('POINT(${lat} ${lng})', 4326)')
    ), 'distance'])
}
if(!query.includes(',')) {
    attributes.push([DataTypes.literal('CASE
        WHEN
            name = '' + query + '' THEN 4
        WHEN
            city LIKE '%' + query + '%' THEN 3
        WHEN
            name LIKE '%' + query + '%' THEN 2
        WHEN
            name LIKE '' + query + '%' THEN 1
    END'), 'exact_like_match'])
}
var whereAnd = []
whereAnd.push(
    DataTypes.literal('MATCH(name, city, state, zip) AGAINST(:search_query)')
)
whereAnd.push({
    menu_enabled: 'Y'
})
var search_params = {   
    where: whereAnd,
    replacements: {
        search_query: '+' + query + '*',
        type: DataTypes.QueryTypes.SELECT
    },
    attributes: attributes,
    offset: parseInt(req.query.start),
    limit: req.query.limit ? parseInt(req.query.limit) : 12
}
search_params['order'] = [
    DataTypes.literal('exact_like_match DESC')
]
if(!query.includes(',')) {
    // console.log('comma detected!')
    search_params['order'] = [
        DataTypes.literal('exact_like_match DESC, distance ASC')
    ]
}
dispensary.findAndCountAll(search_params).then(disp_rx => {

})