sequelieJS选取数据库语句

Question

// mysql原生语句
select * from user_organization where organization_id in(select id from organization where name like 'ext-%');



   
   

求老司机如何写成sequelize的语句？

Answer 1

const Sequelize = require('sequelize');
const sequelize = new Sequelize(...);

sequelize.query( //原始查询
     'select * from user_organization where organization_id in(select id from organization where name like 'ext-%')' ,
     { type:Sequelize.QueryTypes.SELECT }
);

或者分两步简单查询：

const ids = await DBmodel.Organization.findAll({
    where: { name: {$like: 'ext-%'} },
    attributes: ['id']
})
const result = await DBmodel.User_organization.findAll({
    where: {
        organization_id: {$in: ids}
    }
})

Answer 2

    
    await organizationModel.findAll({
        where:{
            name: {
                $like: 'ext-%'
            }
        }
    }).then(ori => {
        // 这里需要修改，不能循环查询数据库
        Array.from(ori).forEach(record => {
            return userOrganizationModel.findAll({
                where: {
                    'organization_id':record.id
                }
            }).then(result => {
                console.log(result);
                ctx.json({
                    status: 100,
                    result
                });
            })
        });
        
    }).catch(err => {
        console.log(err);
        ctx.json({
            status: 101
        });
    });

};