Unknown generated column in 'having clause' when findAndCountAll

[jjacquesf]

I am trying to get back rows and count with specific

            models.Billboard.findAndCountAll({
                attributes: [
                    'Id',
                    'status',
                    ['( 6371 * acos( cos( radians('+location[0]+') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('+location[1]+') ) + sin( radians('+location[0]+') ) * sin( radians( lat ) ) ) )', 'distance'],
                ],
                having: {
                    distance: {
                        $lt: 5
                    }
                },
                order: ['id'],
                limit: 15,
                offset: page - 1
            })
            .then( function(result) {

                // cache save
                cache.set( hash, result, function(err, success) {

                    res.setHeader('Content-Type', 'application/json');
                    res.send(JSON.stringify(result, null, 3));
                    res.end();
                } );

            });

Query executed by Sequelize in findAll:

SELECT `Id`, `status`, ( 6371 * acos( cos( radians(20.6596988) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-103.34960920000003) ) + sin( radians(20.6596988) ) * sin( radians( lat ) ) ) ) AS `distance` FROM `Billboards` AS `Billboard` HAVING `distance` < 5 ORDER BY `id` LIMIT 15;

Query executed by Sequelize in findAndCountAll:

SELECT count(*) AS `count` FROM `Billboards` AS `Billboard` HAVING `distance` < 5;

Result:

Unhandled rejection SequelizeDatabaseError: ER_BAD_FIELD_ERROR: Unknown column 'distance' in 'having clause'

[mickhansen]

attributes are ignored for the count part, which obviously poses a problem for this particular query.

[straube]

I've seen a similar problem in another framework/language before. For that specific case, the solution was to have the count part using a subquery instead of just count(*) with the same options. I'm not sure how easy it is to make something like this with Sequelize. Is it possible at all?

[janmeier]

@straube There's not great support for subqueries in sequelize currently.

Could you show an example of how that query would look?

[straube]

@janmeier Using the query @jjacquesf posted, the count query should look something like:

SELECT COUNT(*) FROM (
    SELECT `Id`, `status`, ( 6371 * acos( cos( radians(20.6596988) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-103.34960920000003) ) + sin( radians(20.6596988) ) * sin( radians( lat ) ) ) ) AS `distance` FROM `Billboards` AS `Billboard` HAVING `distance` < 5
) AS `sub`;

We need to keep the columns in the subquery because they may be used by HAVING clause.

[jamesone]

@mickhansen - I actually found out that you can use your attributes in such queries (having, etc...).

Notice that I've put 'distance' in the brackets, sequelize just prints this out as 'distance' inside the query, if you were to add distance without the brackets it would try find distance in your model.

 having: {
           'distance': {
                $lt: 5
            }
 },

This can be closed.

[straube]

@jamesone By brackets ({ and }) you mean single quotes or apostrophes (')? If so, maybe I'm wrong, but I don't think that make any difference in the runtime. Both snippets below, although the slightly different syntax, give the same result:

'distance': {
    $lt: 5
}

and

distance: {
    $lt: 5
}

However, if you're definitely talking about the brackets, @jjacquesf used in the exact same syntax you mentioned – refer to the first comment. And it didn't work.

[jamesone]

I was running a similar query (making calculation in attribute, named it distance), the only way to reference that value in my having: clause was to put single quotes around it (probably works with double aswell), I'm pretty sure Sequelize tried to look for the column distance in my model and then just defaulted to 'distance in my query'

Sent from Outlook

On Fri, Apr 15, 2016 at 4:02 AM -0700, "Gustavo Straube" notifications@github.com wrote:

@jamesone By brackets ({ and }) you mean single quotes or apostrophes (')? If so, maybe I'm wrong, but I don't think that make any difference in the runtime. Both snippets below, although the slightly different syntax, give the same result:

'distance': { $lt: 5 }

and

distance: { $lt: 5 }

However, if you're definitely talking about the brackets, @jjacquesf used in the exact same syntax you mentioned – refer to the first comment. And it didn't work.

— You are receiving this because you were mentioned. Reply to this email directly or view it on GitHub

[hardikphp]

@jamesone did you get any solutions ? I am also facing same issue

[tapanand]

@jamesone

This may Help:

var Sequelize = require('sequelize');
var sequelize = new Sequelize(.....);

var Loc = sequelize.define('loc', {
        idx: {
            type: BIGINT,
            allowNull: false,
            autoIncrement: true,
            primaryKey: true,
        },
        name: {
            type: STRING(100),
            allowNull: false
        },
        sLatitude: {
            type: DOUBLE,
            allowNull: false
        },
        sLongitude: {
            type: DOUBLE,
            allowNull: false
        },
        geoPoints: {
            type: GEOMETRY('POINT'),
            allowNull: true
        },
        active: {
            type: BOOLEAN,
            allowNull: false,
            defaultValue: '0'
        }
    });

Loc.findAll({
        replacements: {
            latitude: parseFloat(req.params.lat),
            longitude: parseFloat(req.params.lng),
            maxDistance: 100
        },
        attributes: {
            include: [[sequelize.fn('ST_Distance_Sphere', sequelize.col('geoPoints'), sequelize.fn('ST_GeomFromText', sequelize.literal("\"POINT(:longitude :latitude)\""))), 'distance']]
        },
        having: { 'distance': { [Sequelize.Op.lte]: ':maxDistance' } }
    }).then(locs => {
        console.log(locs);
    }).catch(err => {
        console.error(err);
    });

[francessco410]

I'm facing the same issue. Did something change in this topic?

[NideoTV]

Not working for me neither, tried all the solutions you mentioned... :/

[karthickraja81222]

I am also facing same issue please help me out if anyone got the solution

[github-actions[bot]]

This issue has been automatically marked as stale because it has been open for 14 days without activity. It will be closed if no further activity occurs within the next 14 days. If this is still an issue, just leave a comment or remove the "stale" label. 🙂

[niraj-khatiwada]

7 years and I keep coming back.

[gamusta]

I am facing the same issue. is there something new ?