I have an article table, where an article can cite multiple articles. Each article can also have a single author.
Article model :
class Article extends DefaultModel {
static get tableName() {
return "articles";
}
static get relationMappings() {
return {
author: {
relation: DefaultModel.BelongsToOneRelation,
modelClass: "Author",
join: {
from: "articles.authorId",
to: "authors.id"
}
},
mentions: {
relation: DefaultModel.ManyToManyRelation,
modelClass: "Article",
join: {
from: "articles.id",
through: {
from: "mentions.citingArticle",
to: "mentions.citedArticle"
},
to: "articles.id"
}
}
};
}
}
Mention model :
class Mention extends DefaultModel {
static get tableName() {
return "mentions";
}
static get idColumn() {
return ["citingArticle", "citedArticle"];
}
}
What I'm trying to do, is insertGraph, the main article + the articles that are mentioned in it, here is what I have :
async function insertData(fullArticle) {
const articleTx = await transaction(Article.knex(), async tx => {
const references = fullArticle.references
.map(articleObj => {
return {
...articleObj.article,
author: articleObj.author
};
});
const article = await Article.query(tx).insertGraph({
...fullArticle.article,
author: fullArticle.author,
mentions: references[0]
});
return article;
});
console.log(articleTx);
}
I know this is only inserting the main row + the first row, but I ran into multiple problems :
- The mention model has a composite key, when I try to insert using the code above I get a
(node:5944) UnhandledPromiseRejectionWarning: error: column "citedArticle" of relation "mentions" does not exist
. - I also got the same error when I try to add
"#id"
to the main article mentions relation, and"#ref"
to the cited article, and insert them in 2 different objects. - A third problem I ran into here, was a unique constraint on both the citing and the cited article authors. What if both of them were written by the same author? I have a unique string column in the authors table that isn't the id, when I tried to insert I got
duplicate key value violates unique constraint "author_isn"
. I'm not sure how I can update the row to reference the existing author id if that happens.