tux-world tux-world - 5 months ago 44
Javascript Question

Nodejs use JOIN for two tables on sequelize

I'm trying to implementing this mysql command on sequelize, but as far as i'm newbie to use this library i can't implementing that

i want to make this sql command:

SELECT * FROM users
join users_contacts_lists on users_contacts_lists.mobile_number = users.mobile_number
WHERE users_contacts_lists.user_id = 1


My models to create database schema:

'use strict';
var config = require('../config');

var User = config.sequelize.define('users', {
id: {
type: config.Sequelize.INTEGER,
autoIncrement: true,
primaryKey: true
},
password: {
type: config.Sequelize.STRING
},
username: {
type: config.Sequelize.STRING
},
mobileNumber: {
type: config.Sequelize.STRING,
field: 'mobile_number'
},
status: {
type: config.Sequelize.STRING
},
}, {freezeTableName: true});

var UsersContactsLists = config.sequelize.define('users_contacts_lists', {
id: {
type: config.Sequelize.INTEGER,
autoIncrement: true,
primaryKey: true
},
userId: {
type: config.Sequelize.INTEGER,
field: 'user_id'
},
mobileNumber: {
type: config.Sequelize.STRING,
field: 'mobile_number', defaultValue: 0
}
}, {freezeTableName: true});

UsersContactsLists.belongsTo(ChannelsTypes, {foreignKey: 'user_id'});
User.hasMany(Channels, {foreignKey: 'id'});

User.sync();
UsersContactsLists.sync();

module.exports =
{
users: User,
usersContactsLists: UsersContactsLists
};


how can i resolve this problem? Thanks in advance

Answer

you can define the target key and the foreign key both in a relation like this :

User.belongsTo(UsersContactsLists, {targetKey:'mobileNumber',foreignKey: 'mobileNumber'});

then you can use this :

User.findAll({
include: [{
    model: UsersContactsLists,
    where: {
        userId: 1
    }
}]