Query based on included data in Sequelize

user_1349895 0
Viewed 1368

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?

sequelize.js node.js postgresql
1 Answers

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);
        }
    });
user_4243810
Related