Menu
I have the following Sequelize relations:
Shop.hasMany(ShopAd, {foreignKey : 'shop_id', as : 'ads'}); ShopAd.belongsTo(Shop, {foreignKey : 'id'}) For the following Sequelize query:
Shop.findAll({ where: {id:shopId}, include: [{model:ShopAd, as:'ads', where:{is_valid:1, is_vertify:1}}] }).success(function(result) { callback(result); }); the SQL that Sequelize runs for this query is:
SELECT `Shop`.`id`, `Shop`.`user_id`, `Shop`.`short_name`, `Shop`.`description`, `Shop`.`tips`, `Shop`.`city`, `Shop`.`province`, `Shop`.`address`, `Shop`.`logo`, `Shop`.`publicity_photo`, `Shop`.`taobao_link`, `Shop`.`is_vertify`, `Shop`.`create_time`, `Shop`.`update_time`, `ads`.`id` AS `ads.id`, `ads`.`shop_id` AS `ads.shop_id`, `ads`.`pic_url` AS `ads.pic_url`, `ads`.`description` AS `ads.description`, `ads`.`link` AS `ads.link`, `ads`.`is_valid` AS `ads.is_valid`, `ads`.`is_vertify` AS `ads.is_vertify`, `ads`.`create_time` AS `ads.create_time`, `ads`.`update_time` AS `ads.update_time` FROM `weshop_shop` AS `Shop` INNER JOIN `weshop_shop_advertsing` AS `ads` ON `Shop`.`id` = `ads`.`shop_id` AND `ads`.`is_valid`=1 AND `ads`.`is_vertify`=1 WHERE `Shop`.`id`='1'; which does an INNER JOIN.
I want to use LEFT JOIN instead. How can I do this with Sequelize?
882
There are two ways you can create JOIN queries and fetch data from multiple tables with Sequelize: Create raw SQL query using sequelize. query() method. Associate related Sequelize models and add the include option in your Sequelize query method.
To make join queries using Sequelize on Node. js, we can use the include option with findAll . const posts = await Posts. findAll({ include: [{ model: User, required: true }] }) //...
The LEFT JOIN command returns all rows from the left table, and the matching rows from the right table. The result is NULL from the right side, if there is no match.
Different Types of SQL JOINs (INNER) JOIN : Returns records that have matching values in both tables. LEFT (OUTER) JOIN : Returns all records from the left table, and the matched records from the right table. RIGHT (OUTER) JOIN : Returns all records from the right table, and the matched records from the left table.
using: required:false
sentences：
Shop.findAll({ where:{id:shopId}, include:[ { model:ShopAd, as:'ads', where:{ is_valid:1, is_vertify:1}, required:false } ] }) .success(function(result) { callback(result); }); If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With