CODEWITHSUNDEEP
postgresqlassociationssequelize.js
Nam
Nam

Reputation: 1

sequelize associations foreignKey

I want INNER JOIN with sequelize db: postgress I have 2 database(users and posts)

Users

import Sequelize from "sequelize";
import { sequelize } from "../databases/database";
import Post from "./Post";
const User = sequelize.define(
    "user",
    {
        id: {
      type: Sequelize.INTEGER,
      autoIncrement: true,
      primaryKey: true
        },
        username: {
            type: Sequelize.STRING,

        },
        password: {
            type: Sequelize.STRING
        },
        image: {
            type: Sequelize.STRING
        }
    },
    { timestamps: false }
);
User.hasMany(Post, { foreignKey: "author", sourceKey: "username" });
Post.belongsTo(User, { foreignKey: "author", sourceKey: "username" });
export default User;

Posts

import Sequelize from "sequelize";
import { sequelize } from "../databases/database";

const Post = sequelize.define("post", {
  id: {
    type: Sequelize.INTEGER,
    primaryKey: true
  },
  title: {
    type: Sequelize.TEXT
  },
  content: {
    type: Sequelize.TEXT
  },
  image: {
    type: Sequelize.STRING
  },
  description: {
    type: Sequelize.TEXT
  },
  tags: {
    type: Sequelize.STRING
  },
  author: {
    type: Sequelize.STRING
  },
  createdAt: {
      field: 'createdat',
      type: Sequelize.DATE
  },
  updatedAt: {
    field: 'updatedat',
    type: Sequelize.DATE
  }
});

export default Post;

Controller

export const listAllPosts = async params => {
    const { offset } = params;

    try {
        const allPosts = await Post.findAll({
            // limit: 20,
            // offset: offset ? offset * 20 : 0,
            // order: [["id", "DESC"]],
            attributes: ["title", "content","tags","description","createdAt","updatedAt"],
            required: true,
            include:[
                {
                    model:User,
                    attributes: ["username", "image"],
                    required:true,
                }
            ]
        });
        return allPosts;
    } catch (error) {
        throw error;
    }
};

and when i collect api show error:

Executing (default): SELECT "post"."id", "post"."title", "post"."content", "post"."tags", "post"."description", "post"."createdat"AS "createdAt", "post"."updatedat" AS "updatedAt", "user"."id" AS "user.id", "user"."username" AS "user.username", "user"."image" AS "user.image" FROM "posts" AS "post" INNER JOIN "users" AS "user" ON "post"."author" = "user"."id"; error: SequelizeDatabaseError: operator does not exist: text = integer

But i want show exact and i dont want change primaryKey: 

SELECT "post"."title", "post"."content", "post"."tags", "post"."description", "post"."createdat"AS "createdAt", "post"."updatedat" AS "updatedAt", "user"."username" AS "user.username", "user"."image" AS "user.image" FROM "posts" AS "post" INNER JOIN "users" AS "user" ON "post"."author" = "user"."username";

How i can do that?

Upvotes: 0

Views: 173

Answers (1)

Uladzislau Vavilau
Uladzislau Vavilau

Reputation: 941

You should better use 'userId' then 'author'. It'll fix your problem. And edit association 

User.hasMany(Post, { foreignKey: "userId", sourceKey: "id" });
Post.belongsTo(User, { foreignKey: "id", sourceKey: "userId" });

And advice you to read https://www.essentialsql.com/get-ready-to-learn-sql-database-normalization-explained-in-simple-english/

Upvotes: 0

Related Questions