instance.getThing() fails on many-to-many with non-primary target key

[papb]

Issue Description

What are you doing?

Here is the link to the SSCCE for this issue: sequelize-sscce#16

Summary:

Ship.belongsToMany(Captain, { through: 'foobar', sourceKey: 'name', targetKey: 'nickname' });
Captain.belongsToMany(Ship, { through: 'foobar', sourceKey: 'nickname', targetKey: 'name' });
// create some records ...
const ship = await Ship.findOne();
console.log(await ship.getCaptains()); // []

What do you expect to happen?

See the SSCCE, ship.getCaptains() should return something

What is actually happening?

See the SSCCE, ship.getCaptains() is returning an empty array

Additional context

This refers to the functionality added by #11311

Environment

• Sequelize version: 5.21.1 (latest at the time of this writing)
• Node.js version: v10.16.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 PUT-YOUR-DIALECT-HERE, with connector library version XXX and database version XXX

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.

[papb]

As shown in the SSCCE, the query is generated incorrectly... For example, in MSSQL:

SequelizeDatabaseError: Conversion failed when converting the nvarchar value 'Jack Sparrow' to data type int.

SQL Generated:

# ...
ON [captain].[id] = [foobar].[captainNickname] 
# ...

SQL Expected:

# ...
ON [captain].[nickname] = [foobar].[captainNickname] 
# ...

[papb]

@fboechats Would you like to try to take this one?

[fboechats]

@papb Sure, I can take this one.

[fboechats]

@papb Hello, I'm kinda lost in this problem, sorry for that. I didn't find how to manage that selection and don't have him thinking it's a number id over a string.

[papb]

@fboechats No problem, thanks for taking the time regardless :)

[alissonmarqui]

I think I have the same problem...

I have these relations:

Category.belongsToMany(models.Product, { as: 'products_children', targetKey: 'category_id', foreignKey: 'ancestor_id', otherKey: 'category_id', through: 'Categoryancestor'});

Product.belongsToMany(models.Category, { as: 'categories_ancestors', sourceKey: 'category_id',  foreignKey: 'category_id', otherKey: 'ancestor_id', through: 'Categoryancestor',});

When i call category.getProducts_children({ limit: 10});

SQL Generated:

#...
INNER JOIN "categories_ancestors" AS "Categoryancestor" ON "Product"."id" = "Categoryancestor"."category_id" AND "Categoryancestor"."ancestor_id" = 3 LIMIT 10
#...

SQL Expected:

#...
INNER JOIN "categories_ancestors" AS "Categoryancestor" ON "Product"."category_id" = "Categoryancestor"."category_id" AND "Categoryancestor"."ancestor_id" = 3 LIMIT 10
#...

The sequelize ignore my targetKey: 'category_id' and use the primary key.