The ambiguous error occurs while using the models.sequelize.col () in the include in sequelize(node.js express)

Question

mariadb,

show tables;
Board
Comment

my code,

models.Board.findAll({
attributes: [
  '_no', 'title', 'content', 'createdAt'
],
include: [
  {
    model: models.Comment,
    tableAlias: 'Comment',
    attributes: [
      [models.sequelize.fn('count', models.sequelize.col('_no')), 'comment']
    ]
  }
],
group: ['_no', 'title', 'content', 'createdAt'],
order: [
  ['createdAt', 'DESC']
],
    raw: true
   }).then(function(boards)

     {
         res.send(JSON.stringify(boards));

      });

Why error occurs?

Unhandled rejection SequelizeDatabaseError: ER_NON_UNIQ_ERROR: Column '_no' in field list is ambiguous

models.sequelize.col('_no') -> models.sequelize.col('models.Comment._no') error, too.

models.sequelize.col ( '_ no') in the _no want to use Comment table.

thanks.

score 6 · Accepted Answer · answered Aug 15 '16 at 06:55

6

Appearently both Board and Comment have a _no column? In that case you need to specify which one you want to count, fx: models.sequelize.col('board._no')) (make sure the table name matches the pluralization and capitalization of the table in the rest of the query)

answered Aug 15 '16 at 06:55

Jan Aagaard Meier

28,078
8
95
66

But how to do this on a table when you don't know from where it will be included? Any clue? – Patrick Geyer Feb 07 '19 at 14:18
@Jan, can you not give me a hint? – Patrick Geyer Jul 17 '19 at 17:15
Did you solve it ? We have same issue my root table have full_name column the the child table is belongsTo root table that have full_name column to ... I got ambiguitas. How to ignore them ? Without refactor the column name .. – Yogi Arif Widodo Sep 17 '22 at 10:28

Ajay Prajapati · Answer 2 · 2022-08-02T04:44:58.183

0

if multiple table is there in query then you need to specify which table createdAt you want to group by because node have all table createdAt(default created)

group: ["your_table_name_here\".\"createdAt"]

if we use multiple model for get record in that case same field name in 2 or more model then you need to specify which model record you want to group by so in that case i write group: ["your_table_name_here"."createdAt"]

edited Aug 02 '22 at 04:44

answered Aug 01 '22 at 08:10

Ajay Prajapati

174
4

1

Your explanation is really difficult to understand. Please try to clean it up. – General Grievance Aug 01 '22 at 17:23
if we use multiple model for get record in that case same field name in 2 or more model then you need to specify which model record you want to group by so in that case i write group: ["your_table_name_here\".\"createdAt"] – Ajay Prajapati Aug 02 '22 at 04:44

score 0 · Answer 3 · answered Oct 16 '22 at 08:24

Try to specify the model where createdAt is located

import { Parking } from "../database/models";

const parking = await Parking.findAndCountAll({
    where: {
      "$Parking.createdAt$": {
         [Op.between]: [new Date(startDate), new Date(endDate)]
      }
    }
})

The ambiguous error occurs while using the models.sequelize.col () in the include in sequelize(node.js express)

3 Answers3