Why is Sequelize only returning one object using findAll()?

Question

My goal is to be able to find all products by their brand name and model name. However, Sequelize is only returning one record out of many other similar records. If it does return more than one record, other records with identical attributes as the first record found will be null. For example, the first record in the array will have the attribute name: iPhone, the second record which has the exact same attribute will be shown as name: null when it should be name: iPhone.

In my database, I have the following tables:

Products, Brands, Models, and Suppliers. The Products table contains foreign keys such as brand_id, model_id, etc.. Brands, Models, and Suppliers have the attribute: id.

I have set the relationship up as the following:

Products.hasOne(Brands, { foreignKey: 'id' });
Products.hasOne(Models, { foreignKey: 'id' });
Products.hasOne(Suppliers, { foreignKey: 'id' });

Brands.belongsTo(Products);
Models.belongsTo(Products);
Suppliers.belongsTo(Products);

In my search function, I attempt to find all products by brand and model name that match my query.

const getSearch = (req, res) => {
  const { query: { query } } = req;

  Products.findAll({
    where: Sequelize.where(Sequelize.fn('concat', Sequelize.col('Brand.name'), ' ', Sequelize.col('Model.name')), {
      [Op.substring]: query
    }),
    include: [
      { model: Brands, attributes: ['name'] },
      { model: Models, attributes: ['name'] },
      { model: Suppliers, attributes: ['name'] },
    ],
    attributes: ['id', 'price']
  })
    .then((data) => {
      console.log(data);
      res.send(data);
    })
    .catch((err) => (console.log(err)));
};

In my database, I have two product rows with the exact same data but different ids. When calling getSearch I expect to see two objects in the array as they have the same brand name and model name. Instead I see one.

Here's what my models look like:

Products

class Products extends Model {
  static init(sequelize, DataTypes) {
    return super.init(
      {
        id: {
          type: DataTypes.INTEGER,
          primaryKey: true,
          autoIncrement: true
        },
        url_id: {
          type: DataTypes.INTEGER,
          allowNull: true
        },
        brand_id: {
          type: DataTypes.INTEGER,
          allowNull: true
        },
        model_id: {
          type: DataTypes.INTEGER,
          allowNull: true
        },
        supplier_id: {
          type: DataTypes.INTEGER,
          allowNull: true
        },
        image: {
          type: DataTypes.STRING,
          allowNull: true
        },
        description: {
          type: DataTypes.STRING,
          allowNull: true
        },
        price: {
          type: DataTypes.DOUBLE,
          allowNull: true
        }
      },
      {
        modelName: 'Products',
        timestamps: false,
        sequelize
      }
    );
  }
}

Models

class Models extends Model {
  static init(sequelize, DataTypes) {
    return super.init(
      {
        id: {
          type: DataTypes.INTEGER,
          primaryKey: true,
          autoIncrement: true
        },
        name: {
          type: DataTypes.STRING,
          allowNull: true
        },
        colour_id: {
          type: DataTypes.INTEGER,
          allowNull: true
        },
        storage_capacity_id: {
          type: DataTypes.INTEGER,
          allowNull: true
        }
      },
      {
        modelName: 'Models',
        timestamps: false,
        sequelize
      }
    );
  }
}

Brands

class Brands extends Model {
  static init(sequelize, DataTypes) {
    return super.init(
      {
        id: {
          type: DataTypes.INTEGER,
          primaryKey: true,
          autoIncrement: true
        },
        name: {
          type: DataTypes.STRING,
          allowNull: true
        }
      },
      {
        modelName: 'Brands',
        timestamps: false,
        sequelize
      }
    );
  }
}

Suppliers

class Suppliers extends Model {
  static init(sequelize, DataTypes) {
    return super.init(
      {
        id: {
          type: DataTypes.INTEGER,
          primaryKey: true,
          autoIncrement: true
        },
        name: {
          type: DataTypes.STRING,
          allowNull: true
        }
      },
      {
        modelName: 'Suppliers',
        timestamps: false,
        sequelize
      }
    );
  }
}

What am I doing wrong here?

Answer 1

You have an error in associations. Just change hasOne to hasMany and you are done.