Get data from multiple tables using Seqeulize

Question

I have two tables, Post and Comments, I am trying to get all posts by userId and also fetch the relevant comments for each post using the following code. I am getting all the posts but nodejs through an error " comment is not associated to post".

Post Table

const {Sequelize, DataTypes} = require('sequelize')
const db = require('../config/db')

const Post = db.define('post', {
    id:{
        type: DataTypes.INTEGER(255),
        autoIncrement: true,
        allowNull: false,
        primaryKey: true
    },
    postId:{
        type: DataTypes.UUID,
        defaultValue: Sequelize.UUIDV4
    },
    postText:{
        type: DataTypes.TEXT,
        allowNull: false
    },
    totalPostLikes: {
        type: DataTypes.INTEGER,
        allowNull: true,
        defaultValue: 0
    },
    totalPostStrikes: {
        type: DataTypes.INTEGER,
        allowNull: true,
        defaultValue: 0
    },
    totalPostQuotes: {
        type: DataTypes.INTEGER,
        allowNull: true,
        defaultValue: 0
    },
    totalPostShare: {
        type: DataTypes.INTEGER,
        allowNull: true,
        defaultValue: 0
    },
    userId:{
        type: DataTypes.INTEGER,
        allowNull: false,
        references: {
            model: 'users',
            key: 'id'
        } 
    }
},{
    freezeTableName: true
  })

module.exports = Post

Comments Table

const {Sequelize, DataTypes} = require('sequelize')
const db = require('../config/db')

const Comment = db.define('comment', {
    id:{
        type: DataTypes.INTEGER(255),
        autoIncrement: true,
        allowNull: false,
        primaryKey: true
    },
    commentUUID:{
        type: DataTypes.UUID,
        defaultValue: Sequelize.UUIDV4
    },
    commentText:{
        type: DataTypes.TEXT,
        allowNull: false
    },
    totalCommentLikes: {
        type: DataTypes.INTEGER,
        allowNull: true,
        defaultValue: 0
    },
    totalCommentStrikes: {
        type: DataTypes.INTEGER,
        allowNull: true,
        defaultValue: 0
    },
    totalCommentQuotes: {
        type: DataTypes.INTEGER,
        allowNull: true,
        defaultValue: 0
    },
    userId:{
        type: DataTypes.INTEGER,
        allowNull: false,
        references: {
            model: 'users',
            key: 'id'
        } 
    }, postId:{
        type: DataTypes.INTEGER,
        allowNull: false,
        references: {
            model: 'post',
            key: 'id'
        } 
    }
},{
    freezeTableName: true
  })

  module.exports = Comment

This is how I am creating tables and associations

const db = require('../config/db')

const User = require('./user')
const Post = require('./posts')
const Comments = require('./comments')


User.hasMany(Post)
Post.belongsTo(User)


Post.hasMany(Comments)
Comments.belongsTo(Post)
User.hasMany(Comments)
Comments.belongsTo(User)



db.sync({force: true})
.then((result) =>{
    console.log(result)
})
.catch((error) => {
    console.log(error)
})

Code to fetch data

router.get('/:id', authenticate, async (req,res) => {

    const { id } = req.params


    const data = await Post.findAll(
        {where: {userId: id},
        attributes: [
            'postText', 
            'totalPostLikes', 
            'totalPostStrikes',
            'totalPostQuotes',
            'totalPostShare'
        ],
         include:[{model: Comments, attributes:['commentText']}]   
    })


    try{
        res.send(data);

    } catch({errors}) {
        res.status(400).send(errors)
    }
})

did you solve problem ?

Wang Liang
– Wang Liang

2021-06-29 12:10:02 +00:00
Commented Jun 29, 2021 at 12:10 — Wang Liang
– Wang Liang, Commented Jun 29, 2021 at 12:10

Wang Liang · Accepted Answer · 2021-06-25 22:11:06Z

1

const Sequelize = require('sequelize');
const Op = Sequelize.Op;
const db = require('../models');

var main = async () => {
    try {
        const data = await db.post.findAll({
            where: { userId: 1 },
            attributes: ['postText', 'totalPostLikes', 'totalPostStrikes', 'totalPostQuotes', 'totalPostShare'],
            include: [{ model: db.comment, attributes: ['commentText'], as: 'comments' }],
        });
        console.log(JSON.parse(JSON.stringify(data)));
    } catch (e) {
        console.log(e);
    }
};
main();

edited Jun 25, 2021 at 22:11

answered Jun 25, 2021 at 5:27

Wang Liang

4,4627 gold badges26 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Get data from multiple tables using Seqeulize

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related