Sequelize how to return column of joined table in the results

Question

I'm using the sequelize module for my node.js mvc project and the query i'd like to execute is the following

SELECT answer_text, isNew, name FROM answer JOIN topic ON answer.topic_id = topic.id

answer_text and isNew are columns of the answer table while name is a column that only exists in the topic table.

How can i have the topic table name column appear in the results next to isNew column so that i can access it easily? Does sequelize provide such a feature or it's my responsibility to format the result?

I've tried to add various things in attributes like 'topic.name' but none worked.

The way i've set up the file structure is based on their documentation Sequelize usage with express

var models = require('../models')

var answers = await models.Answer.findAll({
    include: [{
        model: models.Topic
    }],
    attributes: [
        'answer_text',
        'isNew'
    ]
})
console.log(answers)

The output of the following is

{ answer_text: 'maybe it is robots',
  isNew: true,
  Topic:
   Topic {
     dataValues:
      { id: 830,
        mid: 'm.0bjmp5',
        name: 'Robot Arena',
        description:
         'Robot Arena is a computer game made by Infogrames. It features robotic combat similar to that of Battlebots Robotica and Robot Wars. There are a number of different chassis and on top of that there are numerous attachments. Weapons accessories tires and other forms of mobility batteries and air tanks are among the customization choices. A sequel called Robot Arena 2 Design and Destroy was made which allows for total customization of your 
robot.',
        type: 'cvg.computer_videogame' },
     _previousDataValues:
      { id: 830,
        mid: 'm.0bjmp5',
        name: 'Robot Arena',
        description:
         'Robot Arena is a computer game made by Infogrames. It features robotic combat similar to that of Battlebots Robotica and Robot Wars. There are a number of different chassis and on top of that there are numerous attachments. Weapons accessories tires and other forms of mobility batteries and air tanks are among the customization choices. A sequel called Robot Arena 2 Design and Destroy was made which allows for total customization of your 
robot.',
        type: 'cvg.computer_videogame' },
     _changed: {},
     _modelOptions:
      { timestamps: false,
        validate: {},
        freezeTableName: false,
        underscored: false,
        paranoid: false,
        rejectOnEmpty: false,
        whereCollection: null,
        schema: null,
        schemaDelimiter: '',
        defaultScope: {},
        scopes: {},
        indexes: [],
        name: [Object],
        omitNull: false,
        sequelize: [Sequelize],
        hooks: {} },
     _options:
      { isNewRecord: false,
        _schema: null,
        _schemaDelimiter: '',
        include: undefined,
        includeNames: undefined,
        includeMap: undefined,
        includeValidated: true,
        raw: true,
        attributes: undefined },
     isNewRecord: false } }

Does this answer your question? [How to use an include with attributes with sequelize?](https://stackoverflow.com/questions/21883484/how-to-use-an-include-with-attributes-with-sequelize) — Rafael Tavares, Apr 08 '20 at 11:22
No because if i specify attributes inside include it will also bring the specified attributes of the table in the form 'tablename.attribute1' etc etc. I've posted the answer to my question. — prof chaos, Apr 08 '20 at 11:28

prof chaos · Answer 1 · 2020-04-08T11:56:48.690

Working answer:

Sequelize must be required in order to use [sequelize.col('Topic.name'), 'name'] inside attributes so that we can fetch name column of Topic table and rename 'Topics.name' to name. (Tried models.col but it is not a function)

raw: true is required if you want to get only the columns inside answers[0]

attributes:[] is required inside include because if you don't put it the result will include all the columns from the joined table (Topic).

const models = require('../models')
const sequelize = require('sequelize');

var answers = await models.Answer.findAll({
                include: [{
                    model: models.Topic,
                    attributes: []
                }],
                attributes: [
                    'answer_text',
                    'isNew',
                    [sequelize.col('Topic.name'), 'name']
                ],
                raw: true
            })
console.log(answers[0])

output:

{ answer_text: 'robot arena',
  isNew: 'true',
  name: 'Robot Arena' }

score 2 · Answer 2 · answered Apr 08 '20 at 10:31

2

Please try the following sequelize statement -

var answers = await models.Answer.findAll({
    include: [{
        model: models.Topic,
    attributes: ['name']
    }],
    attributes: [
        'answer_text',
        'isNew'
    ],
    raw: true
})

I hope it helps!

answered Apr 08 '20 at 10:31

Soham Lawar

1,165
1
12
20

Thnx a lot thats great progress for me :). the following outputs { answer_text: 'robot arena', isNew: 'true', 'Topic.name': 'Robot Arena' } Is it possible instead of 'Topic.name' to have just name: 'Robot Arena'? – prof chaos Apr 08 '20 at 10:49
@profchaos, you are welcome! I don't think there is any way to get only the `name` instead of `Topic.name` – Soham Lawar Apr 08 '20 at 11:47
you actually can, check my answer bellow – prof chaos Apr 08 '20 at 11:48
@profchaos, that's great, glad to know that your problem is solved! – Soham Lawar Apr 08 '20 at 13:00

Sequelize how to return column of joined table in the results

2 Answers2