英文:
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添加一个别名，并在collection_name列上进行查询。美元符号（$）用作占位符，用于引用关联模型的列。
您还可以直接使用模型名称与$。
Book.findAll({
include: [
{
model: Collection,
as: 'collection'
},
],
where: {
[Op.or]:[
{name: { [Op.like]: "%" + term + "%" }},
{author: { [Op.like]: "%" + term + "%"}},
{'$collection.collection_name$': { [Op.like]: "%" + term + "%"}}}
]
}
)
英文:
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 + "%"}}}
]
}
)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论