<-My Blog->

const { Sequelize } = require('sequelize');
const config = require('../config/database'); 

const sequelize = new Sequelize(config.database, config.username, config.password, {
    host: config.host,
    dialect: 'mysql',
    operatorsAliases: 'false',
    logging: false
});  

module.exports = sequelize;

const sequelize = require("../models/connection");
const User = require("../models/user.model");

const latitude = 28.626137;
const longitude = 79.821602;
const distance = 1;

const haversine = `(
    6371 * acos(
        cos(radians(${latitude}))
        * cos(radians(latitude))
        * cos(radians(longitude) - radians(${longitude}))
        + sin(radians(${latitude})) * sin(radians(latitude))
    )
)`;

const users = await User.findAll({
    attributes: [
        'id',
        [sequelize.literal(haversine), 'distance'],
    ],
    where: {
        status: 1
    },
    order: sequelize.col('distance'),
    having: sequelize.literal(`distance <= ${distance}`),
    limit: 5
});

const users = await User.findAll({
    attributes: [
        'id',
        [sequelize.literal(`round(${haversine}, 2)`), 'distance'],
    ],
    where: {
        status: 1
    },
    order: sequelize.col('distance'),
    having: sequelize.literal(`distance <= ${distance}`),
    limit: 5
});

const { DataTypes } = require('sequelize');
const sequelize = require('./connection');

const User = sequelize.define('User', {
    name: {
        type: DataTypes.STRING 
    },
    email: {
        type: DataTypes.STRING 
    }, 
    latitude: {
        type: DataTypes.FLOAT(10, 6) 
    },
    longitude: {
        type: DataTypes.FLOAT(10, 6) 
    },
    status: {
        type: DataTypes.TINYINT(2)
    }
}, {
    underscored: true
}); 

User.addScope('distance', (latitude, longitude, distance, unit = "km") => {
    const constant = unit == "km" ? 6371 : 3959;
    const haversine = `(
        ${constant} * acos(
            cos(radians(${latitude}))
            * cos(radians(latitude))
            * cos(radians(longitude) - radians(${longitude}))
            + sin(radians(${latitude})) * sin(radians(latitude))
        )
    )`;
    return {
        attributes: [ 
            [sequelize.literal(haversine), 'distance'],
        ],
        having: sequelize.literal(`distance <= ${distance}`)
    }
}) 
 

module.exports = User;

const users = await User.scope({ 
    method: ['distance', latitude, longitude, distance] 
})
.findAll({
    attributes: [
        'id'
    ],
    where: {
        status: 1
    },
    order: sequelize.col('distance'),
    limit: 5
});

const users = await User.scope({ 
    method: ['distance', latitude, longitude, distance, 'm'] 
})
.findAll({
    attributes: [
        'id'
    ],
    where: {
        status: 1
    },
    order: sequelize.col('distance'),
    limit: 5
});