Query based on included data in Sequelize

Question

I have a table for people with a self-association so people can have parents/children/cousins/etc.

const People = sequelize.define('People', {
  gender: Sequelize.STRING,
  name: Sequelize.STRING,
  age: Sequelize.INTEGER
})

const Relationships = sequelize.define('Relationships')
Items.belongsToMany(Items, { through: Relationships, as: 'relationships' })

I want to be able to select the data in two ways:

1. Select all of the relations of a person who are the age of 21

// Returns all of johns relatives who are 21
return People.findOne({
  where: { name: 'John' },
  include: [{
    required: false,
    model: Items,
    as: 'relationships',
    where: { age: 21 }
  }]
})

2. Select all of the people who have a relation who is the age of 21. This will need to accept multiple queries like: Select all of the people who have a relative who is 21 or/and a Male.

Any ideas?

Do you need to find all people who is 21 and Male? Try just `where { age:21 , gender: 'Male'` if you need the OR use $or `where: { $or [ {age: 21}, {gender:'Male'} ] }` — Ellebkey, Oct 04 '17 at 14:44
Can you share the code where you defined the self-association? — Ryan Wu, Oct 27 '17 at 18:33
Items.belongsToMany(Items, { through: Relationships, as: 'relationships', foreignKey: 'field_name' }) — shivshankar, Nov 13 '17 at 13:46
Possible duplicate of [Is it possible to filter a query by the attributes in the association table with sequelize?](https://stackoverflow.com/questions/31678813/is-it-possible-to-filter-a-query-by-the-attributes-in-the-association-table-with) — gwintrob, Dec 19 '17 at 21:21
Could you provide the code where you define the Items tables? Also as Ryan Wu says could you also show how you use the People table to achieve the association? Seems I have the solution for you but I need the full info to test the whole thing. — Antonio Narkevich, Jan 04 '18 at 07:26
change required: `false` to `true` then the query with have proper where on it. Also - if this query can return more than one user - then also add `duplicating: true,` to tje includes of this model. — Seti, Nov 26 '20 at 17:34

score 0 · Answer 1 · answered May 02 '18 at 16:29

Here's some full code to toy with and I hope it will be useful to someone. Note that in this example the relationships are not reciprocal, which means if John has relationship with Mary, then Mary doesn't automatically also have a relationship with John (it's more of a John follows Mary situation). But it still serves as an example of how to do self-associations with a explicit join table.

let Sequelize = require('sequelize');
let sequelize = new Sequelize('test', 'test', 'test', {dialect: 'mysql'});

let Person = sequelize.define('Person', {
    name: Sequelize.STRING,
    gender: Sequelize.STRING,
    age: Sequelize.INTEGER
});

let PersonRelationship = sequelize.define('PersonRelationship' /* , more fields could be defined here */);

Person.belongsToMany(Person, {as: 'Relationships', through: PersonRelationship, foreignKey: 'from'});
Person.belongsToMany(Person, {as: 'ReverseRelationships', through: PersonRelationship, foreignKey: 'to'});

let john, mary;

sequelize.sync()
    .then(() => Person.create({name: 'John', gender: 'm', age: 25}))
    .then(p => john = p)
    .then(() => Person.create({name: 'Mary', gender: 'f', age: 21}))
    .then(p => mary = p)
    .then(() => john.addRelationship(mary))
    .then(() => john.getRelationships({where: {age: 21}}))
    .then(relationships => {
        for (let relationship of relationships) {
            console.log('Found relationship:', relationship.name);
        }
    });

score 0 · Answer 2 · answered Jun 18 '23 at 18:31

For the 1st question: Select all of the relations of a person who are of age 21. Your query is correct.

return People.findOne({
  where: { name: 'John' },
  include: [{
    required: false,
    model: People,
    as: 'relationships',
    where: { age: 21 }
  }]
});

For the 2nd query: To select all of the people who have a relation who is the age of 21 or/and a Male.

People.findAll({
  include: [{
    required: true,
    model: People,
    as: 'relationships',
    where: {
      $or: [
        { age: 21 },
        { gender: 'Male' }
      ]
    }
  }]
});

Hope this will be helpful to anyone who comes across the post

Query based on included data in Sequelize

2 Answers2