带有required:true的Sequelize嵌套include生成无效联接

Question

我提前为冗长的帖子道歉！

我正在尝试在nodeJs中使用sequelize来查询一个嵌套include中带有required: true的Wordpress-mysql数据库。

但是，生成的查询包含一个错误的联接(我希望联接像嵌套的where子句一样嵌套(。我似乎不知道我是错误地配置了我的模式，还是我只是在做其他愚蠢的事情。

如有任何帮助，我们将不胜感激！

本质上，我的模式是：

const Post = sequelize.define('wp_posts', {
ID: {
type: DataType.BIGINT,
primaryKey: true
}
});
const TermRelationship = sequelize.define('wp_term_relationships', {
object_id: {
type: DataType.BIGINT,
primaryKey: true,
allowNull: false
},
term_taxonomy_id: {
type: DataType.BIGINT,
primaryKey: true,
allowNull: false
}
});
const TermTaxonomy = sequelize.define('wp_term_taxonomy', {
term_taxonomy_id: {
type: DataType.BIGINT,
primaryKey: true
}
});
const Term = sequelize.define('wp_terms', {
term_id: {
type: DataType.BIGINT,
primaryKey: true
}
});

我定义的关系是：

Post.belongsToMany(TermTaxonomy, {
through: TermRelationship,
otherKey: 'term_taxonomy_id',
foreignKey: 'object_id',
as: 'termTaxonomies'
});
TermTaxonomy.belongsTo(Term, {
foreignKey: 'term_id',
as: 'term'
});

我正在执行的查询是

const query = {
limit: 1,
include: [
{
model: TermTaxonomy,
required: true,
as: 'termTaxonomies',
include: [
{
model: Term,
as: 'term',
required: true,
}
]
}
]
};

但是，生成的查询包含一个错误的联接。这是生成的查询。我在看到错误的地方加入了评论：

SELECT
`wp_posts`.*,
`termTaxonomies`.`term_taxonomy_id` AS `termTaxonomies.term_taxonomy_id`,
`termTaxonomies`.`term_id` AS `termTaxonomies.term_id`,
`termTaxonomies`.`taxonomy` AS `termTaxonomies.taxonomy`,
`termTaxonomies`.`description` AS `termTaxonomies.description`,
`termTaxonomies`.`parent` AS `termTaxonomies.parent`,
`termTaxonomies`.`count` AS `termTaxonomies.count`,
`termTaxonomies->wp_term_relationships`.`object_id` AS `termTaxonomies.wp_term_relationships.object_id`,
`termTaxonomies->wp_term_relationships`.`term_taxonomy_id` AS `termTaxonomies.wp_term_relationships.term_taxonomy_id`,
`termTaxonomies->wp_term_relationships`.`term_order` AS `termTaxonomies.wp_term_relationships.term_order`
FROM
(
SELECT
`wp_posts`.`ID`,
`wp_posts`.`post_author`,
`wp_posts`.`post_date_gmt`,
`wp_posts`.`post_content`,
`wp_posts`.`post_title`,
`wp_posts`.`post_excerpt`,
`wp_posts`.`post_status`,
`wp_posts`.`comment_status`,
`wp_posts`.`ping_status`,
`wp_posts`.`post_password`,
`wp_posts`.`post_name`,
`wp_posts`.`to_ping`,
`wp_posts`.`pinged`,
`wp_posts`.`post_modified_gmt`,
`wp_posts`.`post_content_filtered`,
`wp_posts`.`post_parent`,
`wp_posts`.`guid`,
`wp_posts`.`menu_order`,
`wp_posts`.`post_type`,
`wp_posts`.`post_mime_type`,
`wp_posts`.`comment_count`,
-- ERROR
-- wp_terms cannot be joined to wp_posts
`termTaxonomies->term`.`term_id` AS `termTaxonomies.term.term_id`,
`termTaxonomies->term`.`name` AS `termTaxonomies.term.name`,
`termTaxonomies->term`.`slug` AS `termTaxonomies.term.slug`,
`termTaxonomies->term`.`term_group` AS `termTaxonomies.term.term_group`
FROM
`wp_posts` AS `wp_posts`
-- ERROR: bad join!
-- wp_terms cannot be joined to wp_posts
INNER JOIN `wp_terms` AS `termTaxonomies->term` ON `termTaxonomies`.`term_id` = `termTaxonomies->term`.`term_id`
WHERE
(
SELECT
`wp_term_relationships`.`object_id`
FROM
`wp_term_relationships` AS `wp_term_relationships`
INNER JOIN `wp_term_taxonomy` AS `wp_term_taxonomy` ON `wp_term_relationships`.`term_taxonomy_id` = `wp_term_taxonomy`.`term_taxonomy_id`
INNER JOIN `wp_terms` AS `wp_term_taxonomy->term` ON `wp_term_taxonomy`.`term_id` = `wp_term_taxonomy->term`.`term_id`
WHERE
(
`wp_posts`.`ID` = `wp_term_relationships`.`object_id`
)
LIMIT
1
) IS NOT NULL
LIMIT
1
) AS `wp_posts`
INNER JOIN (
`wp_term_relationships` AS `termTaxonomies->wp_term_relationships`
INNER JOIN `wp_term_taxonomy` AS `termTaxonomies` ON `termTaxonomies`.`term_taxonomy_id` = `termTaxonomies->wp_term_relationships`.`term_taxonomy_id`
) ON `wp_posts`.`ID` = `termTaxonomies->wp_term_relationships`.`object_id`;

由于连接不正确，我得到的错误是'Unknown column 'termTaxonomies.term_id' in 'on clause'。

如果删除required: true或limit选项，则生成的查询是有效的，因为它不再执行奇怪的内部联接。然而，我似乎无法将其用于所需的嵌套include。

仅供参考：我使用的是带有1.5.3 mysql2 1.5.3的sequelize 4.37.10。

非常感谢！

Answer 1

当急切加载时，我们可以强制查询只返回那些具有关联模型的记录，从而有效地将查询从默认的OUTER JOIN转换为INNER JOIN。只需删除必填项：的确，它会很好地工作——通常情况下，当我们急于加载时会发生这种情况，在您的情况下，我们可能正在加载。

我希望你觉得这个解释有益。