findAll: Include multiple joins to the same model without alias at the definition level.

[alexlatchford]

What you are doing?

This is a contrived example but imagine a Blog that has many Users and each user can have many Blogs. Now I want to find all Blogs a user has and then bring back all the Users for that blog in a single query. I believe this should work by doing an INNER JOIN first to find all the affected blogs, then a LEFT JOIN next to find all the users for each blog.

Trouble is with doing x2 joins to the same table I get an alias conflict and nothing I can seem to try (I've read the docs, the code and frustratingly just tried random combinations of options) and nothing I can try seems to be able to reset the alias of one of these joins successfully.

const Blog = sequelize.define('user', { id: ..., title: Sequelize.STRING });
const User = sequelize.define('user', { id: ..., name: Sequelize.STRING });

Blog.belongsToMany(User);
User.belongsToMany(Blog);

// db.sync(..) etc, fill with data.

// Objective: Find all blogs for a user and return the users foreach blog..

const userId = 'abc123';

await Blog.findAll({
    include: [
        {
            // Do an INNER JOIN to find the blogs that user has access to.
            attributes: [], // Don't return any data here.
            model: User,
            where: { userId }
        },
        {
            // Now perform a 2nd LEFT JOIN to return the same table to return the users.
            model: User
        }
    ]
});

I've tried adding as to alias but because I haven't set it in the belongToMany it doesn't seem to take affect. I shouldn't have to alias the relationship at the definition level to be able to alias at the query level (I think) but the docs seem to suggest otherwise.

What do you expect to happen?

I should be able to alias a query without aliasing the definition. I've tried doing:

    include: [
        {
            // Do an INNER JOIN to find the blogs that user has access to.
            as: 'currentUsers',
            association: Blog.associations.blogUsers,
            attributes: [], // Don't return any data here.
            model: User,
            where: { userId }
        },
        {
            // Now perform a 2nd LEFT JOIN to return the same table to return the users.
            model: User
        }
    ]

But even with this change I still get: SequelizeBaseError: table name "blogUsers" specified more than once even though I've supplied an as option.

What is actually happening?

SequelizeBaseError: table name "blogUsers" specified more than once

Dialect: postgres
Database version: 9.6
Sequelize version: 3.30.2

PS. This is psuedo-code (might work but probably not), my example is a lot more complicated but tried to narrow down the example to just enough.

[gc1508]

I have a similar issue. Sequelize uses twice the same alias in two different levels of the query, which causes an error as it is not able to find one of the referred columns. is there news on this?

[jacinto123]

I have a similar issue. Is there anyway we can get this added?

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. If this is still an issue, just leave a comment 🙂

[fmileto]

Same issue here..

[znamilya]

Any news?

[StojanovicM]

I have the same problem.
If this is not working, is there any other way to do this kind of query?

[henninghorn]

You need to define the "intermediate model" which connects the two models together.
Documentation: Belongs-To-Many associations

So, beside having the User and Blog models you need the following:

const BlogUsers = sequelize.define('blogUsers', {
  role: Sequelize.STRING // Just an example, it is a optional column to the relation
})

// Correction many-to-many association
User.belongsToMany(Blog, { through: BlogUsers }
Blog.belongsToMany(User, { through: BlogUsers }

[akpankit]

same issue, any updates. to this issue ?

[seenivasanpalanisamy]

similar issue, alias name have no effect

[abhi5658]

Similar issue described here: #7754
I have provided a workaround if it helps: #7754 (comment)