Location based query

[yu...@devsense.co.il]

Hi, i'm a newbie with sequelize and i'm trying to execute a query to find users ordered by their distance from a specific point.

this is my query:

User.findAll({
    attributes:[[Sequelize.literal('YEAR(CURRENT_TIMESTAMP) - YEAR(birthDay) - (RIGHT(CURRENT_TIMESTAMP, 5) < RIGHT(birthDay, 5))') , "age"]
        ,[Sequelize.literal('(DEGREES( ACOS( SIN( RADIANS(  `latitude` ) ) ' +
        '* SIN( RADIANS('+ lat +' ) ) + COS( RADIANS(  `latitude` ) ) ' +
        '* COS( RADIANS('+ lat +' ) ) ' +
        '* COS( RADIANS('+ lng +' -  `longtitude` ) ) ) ) * 69.09 * 1.6)') , "distance"],
        'id', 'firstName' ,'lastName','nickname', 'gender'] ,

    /* {model: model.Alias, as: 'aliases', attributes: ['name'], required: false},
     where: ["'aliases.name' like ?", terms[0]+"%"],*/

    limit:10,
    offset:page * 10,
    include:[{model:ProfileImage ,order:ProfileImage.order}],
    where: ["User.id != ? AND gender != ?", userId , pref],
    having:["distance < ? AND age BETWEEN ? AND ?", range , minAge, maxAge],
    order:'distance , ProfileImages.order'

})

the problem is, the returned subset of 10 results is ordered by distance and not the whole table.

what am i doing wrong?

[Mick Hansen]

Try changing order to:

order: [
  ['distance']
  [ProfileImage, 'order']
]

--

Mick Hansen

@mhansendev
mhansen.io

[yu...@devsense.co.il]

Nope, i get an unknown column error for user.distance in order clause

[Mick Hansen]

Hmm yeah, you might have to alias it to user.distance.
This is an interesting case, not sure we have full ideal support, by have to do some hackery.

[yu...@devsense.co.il]

Tried that already, when i use user.distance  it just returns an empty set....

I also need at some point to add a count field to return the number of total rows regardless of paging, and when i use

FindAndCountAll the query fails altogether...

Am i going about this all wrong? i mean, this type of query cant be uncommon, its basicly tinder or any other location based app

[Mick Hansen]

Uncommon or not, Sequelize does not have a great deal of spatial/geo support currently.

user.distance returns an empty set does user have a distance field defined?

[yu...@devsense.co.il]

No, the distance field is calculated within the query in the attributes section:

[Sequelize.literal('(DEGREES( ACOS( SIN( RADIANS(  `latitude` ) ) ' +
        '* SIN( RADIANS('+ lat +' ) ) + COS( RADIANS(  `latitude` ) ) ' +
        '* COS( RADIANS('+ lat +' ) ) ' +
        '* COS( RADIANS('+ lng +' -  `longtitude` ) ) ) ) * 69.09 * 1.6)') , "distance"]

a distance field cant be defined before since i only get the coords to calculate distance from in real time.

[Mick Hansen]

I see that, but do you have the field defined as a VIRTUAL property or similar on the model?
Even though it's a computed value it still needs something to hold it, for instance a VIRTUAL attribute.

[yu...@devsense.co.il]

i added a virtual distance field to the model, still executes the default query,

[Mick Hansen]

The default query?
You should still be doing your attributes stuff.

[yu...@devsense.co.il]

Im doing everything exactly the same, except i added the distance field in the model, and tried the query with the the old order clause,

as well as the one you suggested (with both User.distance, and distance).

with anything but the old version (order:'distance , profileimages....'), sequelize executes the following query:

SELECT `id`, `firstName`, `lastName`, `gender`, FROM `Users` AS `User` WHERE `User`.`id`='1';

and the old version still has the issue of only ordering the query subset

[Mick Hansen]

Hmm strange, could you perhaps put together a isolated piece of code?
Would like to play with it and see if i can make it work.