CODEWITHSUNDEEP
node.jssequelize.js
TMA
TMA

Reputation: 1499

Sequelize: Bind multiple models for join query and create custom columns

I'd like to apply a join and groupBy in Sequelize v5 that will fetch the records from five models tables and return the records as below format. 

{
  "data": [
    {
      "products": {
        "id": "01",
        "name": "lorium",
        "description": "ipsum",
        "product_images": [
          {
            "url": "", // From images tbl
            "image_type": "Front" // From imge_types tbl
          },
          {
            "url": "",
            "image_type": "Back"
          }
        ]
      },
      "vendor": {
        "first_name": "john",
        "last_name": "doe"
      }
    }
  ]
}

I have created separate all five models and assign association to them.

Product Model::

const Product = SQLize.define('product', {
    id: { type: DataTypes.INTEGER.UNSIGNED, autoIncrement: true, primaryKey: true, }
    product_title: { type: new DataTypes.STRING(255) },
    vendor_id: { type: DataTypes.INTEGER.UNSIGNED }
}); 
Product.hasMany(ProductImages, {foreignKey: 'product_id', targetKey: 'id', as :'product_img_refs'})

export { Product };

Vendor Model::

const Vendor = SQLize.define('vendor', {
  id: { type: DataTypes.INTEGER.UNSIGNED, autoIncrement: true, primaryKey: true, },
  first_name: { type: DataTypes.STRING(100) },
  last_name: { type: DataTypes.STRING(100) }
});
Product.belongsTo(Vendor, {foreignKey: 'id'})
Vendor.hasOne(Product, {foreignKey: 'id'})

export { Vendor }

Product Images Model::

const ProductImages = SQLize.define('product_images', {
  id: { type: DataTypes.INTEGER.UNSIGNED, autoIncrement: true, primaryKey: true, },
  product_id: { type: DataTypes.INTEGER },
  product_image_id: { type: DataTypes.INTEGER }
  img_type_id: { type: DataTypes.INTEGER }
});

export {ProductImages}

Images Model::

const ImagesModel = SQLize.define('images', {
  id: { type: DataTypes.INTEGER.UNSIGNED, autoIncrement: true, primaryKey: true, },
  img_url: { type: DataTypes.STRING }
});
export { ImagesModel }

Image Types Model::

const ImageTypes = SQLize.define('image_types', {
  id: { type: DataTypes.INTEGER.UNSIGNED, autoIncrement: true, primaryKey: true, },
  image_type: { type: DataTypes.STRING }
});
export { ImageTypes }

Below is the repository file on which i have performed the SQLize operation: Updated::

public async getProductData() {
  var prodData = Product.findAll({
    include: [
      { model: Vendor, as: 'vendor' }, 
      { model: ProductImages, as: 'product_img_refs' }
    ]
  });
  return prodData;
}

I am not getting the correct way to bind the all models that will return me a result as described in the above json format.

Upvotes: 0

Views: 766

Answers (2)

FrostyZombi3
FrostyZombi3

Reputation: 629

To get the nested output as shown in the question, you would need to create associations between the following:

  • ProductImages and ImagesModel
  • ProductImages and ImageTypes

Once done, you can nest the models in the findAll options as shown below:

// Create associations (depends on your data, may be different)
ProductImages.hasOne(ImagesModel);
ProductImages.hasOne(ImageTypes);

// Return product data with nested models
let prodData = Product.findAll({
    include: [
        { model: Vendor, as: 'vendor' },
        {
            model: ProductImages, as: 'product_img_refs',
            include: [
                { model: ImagesModel }, // Join ImagesModel onto ProductImages (add aliases as needed)
                { model: ImageTypes } // Join ImageTypes onto ProductImages (add aliases as needed)
            ]
        }
    ]
});

Upvotes: 1

Darshil Mehta
Darshil Mehta

Reputation: 122

I found the issue. you were trying to include ProductImages model into Vendor. As per your association, ProductImages associate with Product not with Vendor.

So please try this 

let prodData = Product.findAll({
    include: [
        { model: Vendor, as: 'vendor' },
        { model: ProductImages }
    ]
});

Upvotes: 0

Related Questions