英文:
How to query from from joined table in Node.js and Sequlieze?
问题
我必须主要表Book（属性：name，author）和Collection（属性：collection_name），以及一个通过Book.BelongsToMany(Collection)和Collection.BelongsMany(Book)关联它们的表。
现在我想通过搜索name、author或collection_name来查找书籍。
以下是我的查询：
router.get("/search", (req, res) => {
const { term } = req.query;
console.log(term)
Book.findAll({
include: [
{
model: Collection,
},
],
where: {
[Op.or]:[
{name: { [Op.like]: "%" + term + "%" }},
{author: { [Op.like]: "%" + term + "%"}},
]
}
}).then((book) => {
res.status(200).json(book);
})
.catch((err) => res.status(404).json({ error: err }));
});
这个查询在我搜索name或author时显示正确的结果。但是如何在这个查询中搜索collection_name呢？请帮我解决这个问题。
英文:
I have to main table Book (atrributes: name, author) and Collection(atrribute: collection_name)and a table that joins them with Book.BelongsToMany(Collection) and Collection.BelongsMany(Book) association
Now I want to find the books by searching for name, author or collection_name
here is my query
router.get("/search", (req, res) => {
const { term } = req.query;
console.log(term)
Book.findAll({
include: [
{
model: Collection,
},
],
where: {
[Op.or]:[
{name: { [Op.like]: "%" + term + "%" }},
{author: { [Op.like]: "%" + term + "%"}}
]
}
}).then((book) => {
res.status(200).json(book);
})
.catch((err) => res.status(404).json({ error: err }));
});
This query shows me correct results when I searched for name or author . But how to search for collection_name in this query? Plaese help me out
答案1
得分: 0
在使用包含（include）并在collection_name列上进行查询时，为Collection添加一个别名。美元符号（$）用作引用关联模型中的列的占位符。
您还可以直接使用模型名称与$。
Book.findAll({
include: [
{
model: Collection,
as: 'collection' // 在这里使用别名 'collection'
},
],
where: {
[Op.or]:[
{name: { [Op.like]: "%" + term + "%" }},
{author: { [Op.like]: "%" + term + "%" }},
{'$collection.collection_name$': { [Op.like]: "%" + term + "%" }} // 使用别名引用collection_name列
]
}
})
英文:
Add an alias name for Collection while joining using include and then query on the collection_name column. The dollar symbol ($) is used as a placeholder to reference columns from associated models.
You can also directly use model name with $.
Book.findAll({
include: [
{
model: Collection,
as: 'collection'
},
],
where: {
[Op.or]:[
{name: { [Op.like]: "%" + term + "%" }},
{author: { [Op.like]: "%" + term + "%"}},
{'$collection.collection_name$': { [Op.like]: "%" + term + "%"}}}
]
}
)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论