gpt4 book ai didi

sql - 使用所需的 : true generates invalid join 对嵌套包含进行 Sequelize

转载 作者:行者123 更新时间:2023-12-03 22:17:45 25 4
gpt4 key购买 nike

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

我试图在 nodeJs 中使用 sequelize 来查询带有嵌套包含中的 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: truelimit 选项,生成的查询是有效的,因为它不再执行奇怪的内部连接。但是,我似乎无法让它与所需的嵌套包含一起工作。

仅供引用:我使用 sequelize 4.37.10 和 1.5.3 mysql2 1.5.3。

非常感谢你!

最佳答案

在预先加载时,我们可以强制查询仅返回具有关联模型的记录,从而有效地将查询从默认的 OUTER JOIN 转换为 INNER JOIN。只需删除 required:true 它通常会正常工作,当我们急切加载时会发生这种情况,可能在您的情况下我们正在急切加载。
我希望你觉得这个解释是有益的。

关于sql - 使用所需的 : true generates invalid join 对嵌套包含进行 Sequelize ,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/51585830/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com