Sequelize where has NOT

Question

I have an Article Sequelize model where articles relate to each other. Some articles are translated copies of other articles. The relation is setup like so:

var Article = sequelize.define('Article', {     type                : DataTypes.ENUM('source', 'translated'),     sourceArticleId     : DataTypes.INTEGER });  db.Article.hasMany(db.Article, {     foreignKey: 'sourceArticleId',     as        : 'TranslatedArticles' }); 

So, an article with type = 'source' can have many translatedArticles where type = 'translated'.

Now, I want to query all source articles that do not have a translation.

Based on an issue at the Sequelize project github, this would be accomplished like so:

Article.findOne({     where: Sequelize.literal('translatedArticles.sourceArticleId IS NULL'),     include: [         {             model: Article,             as   : 'TranslatedArticles'         }     ] }); 

Yet when I run this I get:

SequelizeDatabaseError: ER_BAD_FIELD_ERROR: Unknown column 'translatedArticles.sourceArticleId' in 'where clause'

I've also tried variations in naming, including TranslatedArticles.sourceArticleId, articles.sourceArticleId, and Articles.sourceArticleId.

Am I missing something?

Note that I temporarily work around this problem by using a literal NOT EXISTS query, like so:

Article.findOne({     where: Sequelize.literal('NOT EXISTS (SELECT id FROM Articles WHERE Article.id = Articles.sourceArticleId LIMIT 1)') }); 

Answer 1

I don't know what database technology you are using on the backend but I am guessing it is case-sensitive. I believe it isn't finding the field because you need to capitalize the t in translatedArticles. This should work:

Article.findOne({     where: Sequelize.literal('TranslatedArticles.sourceArticleId IS NULL'),     include: [         {             model: Article,             as   : 'TranslatedArticles'         }     ] }); 

Answer 2

mabe i have find the solution, use subQuery: false, this option don't have in documentation.

Article.findOne({     subQuery: false,     where: Sequelize.literal('translatedArticles.sourceArticleId IS NULL'),     include: [         {             model: Article,             as   : 'TranslatedArticles'         }     ] });