tux-world tux-world - 18 days ago 9
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
    }
}]