problem with underscored and polymorphic

[zgid123]

Issue Description

What are you doing?

I want to include an polymorphic association when using findAndCountAll. But I faced a problem with that at the first query (it is the count query) at the first time. After facing that issue, I ran the query again and didn't face it anymore.

Here is the quick example:

  const sequelize = new Sequelize('sqlite::memory:');
  const Product = sequelize.define('Product', {}, {
    paranoid: true,
    timestamps: true,
    underscored: true,
    tableName: 'products',
  });
  const Media = sequelize.define('Media', {
    resourceType: DataTypes.STRING,
    resourceId: DataTypes.STRING,
  }, {
    timestamps: true,
    underscored: true,
    tableName: 'media',
  });
  Product.hasMany(Media, {
    foreignKey: 'resourceId',
    scope: {
      resourceType: 'Product',
    },
  });
  await sequelize.sync({force: true});

  await Product.findAndCountAll({include: [Media]});

What do you expect to happen?

The result must be success with list products and each product has list media

What is actually happening?

The first time I ran that, it raised error "media.resoureType does not exist."

Here the query:

SELECT count("Product"."id") AS "count" FROM "products" AS "Product" LEFT OUTER JOIN "media" AS "media" ON "Product"."id" = "media"."resource_id" AND "media"."resourceType" = 'Product' WHERE ("Product"."deleted_at" IS NULL);
Executing (default): SELECT "Product"."id", "media"."id" AS "media.id", "media"."resource_id" AS "media.resourceId", "media"."resource_type" AS "media.resourceType" FROM "products" AS "Product" LEFT OUTER JOIN "media" AS "media" ON "Product"."id" = "media"."resource_id" AND "media"."resource_type" = 'Product' WHERE ("Product"."deleted_at" IS NULL)

This issue happens the first time I start server, I run the query again after fail, the query is correct. For temporary solution, I define association like this

  Product.hasMany(Media, {
    foreignKey: 'resourceId',
    scope: {
      resource_type: 'Product',
    },
  });

Environment

• Sequelize version: 5.21.3
• Node.js version: v11.13.0
• Operating System: macos
• If TypeScript related: TypeScript version: 3.7.3

Issue Template Checklist

How does this problem relate to dialects?

• I think this problem happens regardless of the dialect.
• I think this problem happens only for the following dialect(s):
• I don't know, I was using postgresql database version 11.5

Would you be willing to resolve this issue by submitting a Pull Request?

• Yes, I have the time and I know how to start.
• Yes, I have the time but I don't know how to start, I would need guidance.
• No, I don't have the time, although I believe I could do it if I had the time...
• No, I don't have the time and I wouldn't even know how to start.

[demsey2]

Hi @zgid123 I am having the same issue, did you find any solution?

[demsey2]

my problem exists for many to many relations
my temp workaround is to use underscore: false for that table

[demsey2]

I spent some time debugging the whole library and this is what I found
in src/model.js line 697 we have

if (through.scope) {
        include.through.where = include.through.where
          ? { [Op.and]: [include.through.where, through.scope] }
          : through.scope;
      }

through.scope is assigned to through.where as a reference so when later we modify through.where and convert to underscores to match db names it will automatically change the through.scope

my fix for that is a simple clone

Utils.cloneDeep(through.scope)

not sure if that is the best way to fix that

[kirlev]

This issue should not be closed as it is not fixed.

[infinitizon]

This is a big problem that doesn't seem to want to go away.

Has anyone found a solution?