sequelize: Unknown generated column in 'having clause' when findAndCountAll

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’

About this issue

  • Original URL
  • State: open
  • Created 11 years ago
  • Comments: 16 (3 by maintainers)

Most upvoted comments

@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.

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

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

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

@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.

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

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