CODEWITHSUNDEEP
javascriptsequelize.js
Sandokan El Cojo
Sandokan El Cojo

Reputation: 818

Sequelize: Include.where filtering by a 'parent' Model attribute

I have two Models related, Catalog and ProductCategory. The latter has a composed PK, 'id, language_id'. Here are the models simplified:

var Catalog = sequelize.define("Catalog", {
id: {
  type: DataTypes.INTEGER,
  primaryKey: true,
  autoIncrement: true
},
user_id: {
  type: DataTypes.INTEGER,
  allowNull: false
},
product_category_id: {
  type: DataTypes.STRING(7)
},
language_id: {
  type: DataTypes.INTEGER
},  
... more stuff ...
}

var ProductCategory = sequelize.define("ProductCategory", {
id: {
  type: DataTypes.STRING(7),
  primaryKey: true
},
language_id: {
  type: DataTypes.INTEGER,
  primaryKey: true
},
... more stuff ...
}

Catalog.belongsTo(models.ProductCategory, {foreignKey: 'product_category_id'});

I'm trying to include some info from ProductCategory table related to Catalog, but ONLY when the language_id matches.

At the moment I'm getting all the possible matches from both tables. This is the query right now:

Catalog.find({where:
    {id: itemId},
    include: {
        model: models.ProductCategory, 
        where: {language_id: /* Catalog.language_id */}
    }
})

Is there a way to use an attribute from Catalog to filter the include where both models have the same language?

By the way, I've also tried changing the where clause, without any consecuence:

where: {'ProductCategory.language_id': 'Catalog.language_id'}

Upvotes: 39

Views: 83311

Answers (5)

Arqam Rafay
Arqam Rafay

Reputation: 135

This block of code work for me, no need to add model: models.ProductCategory and {[op.col]: 'Catalog.language_id'}

Catalog.find({where:
    {id: itemId},
    include: {
        model: ProductCategory, 
        where: {
          language_id: 'Catalog.language_id'
        }
    }
})

Upvotes: 0

Ouda22
Ouda22

Reputation: 1

Or even just try this:

const models = require("models directory");
    {
       model: models.ModelName,
       where: {
            column-name: column-value
         }
    }

Upvotes: -2

itsHarshad
itsHarshad

Reputation: 1639

You can try this (Especially if you are using MariaDB) -

const Sequelize = require('sequelize'); 
const op = Sequelize.Op;

Catalog.find({where:
    {id: itemId},
    include: {
        model: models.ProductCategory, 
        where: {
          language_id: {[op.col]: 'Catalog.language_id'}
        }
    }
})

Upvotes: 20

Frederik Kammer
Frederik Kammer

Reputation: 3177

Sequelize provides an extra operator $col for this case so you don't have to use sequelize.literal('...') (which is more a hack).

In your example the usage would look like this:

Catalog.find({where:
    {id: itemId},
    include: {
        model: models.ProductCategory, 
        where: {
          language_id: {$col: 'Catalog.language_id'}
        }
    }
})

Upvotes: 63

Sandokan El Cojo
Sandokan El Cojo

Reputation: 818

This seems this do the trick:

where: {language_id: models.sequelize.literal('Catalog.language_id')}

Upvotes: 5

Related Questions