Multiple Database Association

[codecnmc]

Issue Creation Checklist

• I have read the contribution guidelines

Feature Description

Different database to association.

Is your feature request related to a problem? Please describe.

I try to different database to association.
Immediate to association in model associate is unuseful ,Sequelize tips another database model is undefined.
Try to use setTimeout function to delay belongsTo function is called,Sequelize success init not tips any error.I check database foreign keys is not created.I try to call interface but sequelize tips Table 'xxxx.xxxxx' doesn't exist"

Describe the solution you'd like

sequelize create query

select count(`t_user`.`id`) AS `count` FROM `t_user` AS `t_user` LEFT OUTER JOIN `t_stu_class` AS `classInfo` ON `t_user`.`id` = `classInfo`.`class_id` AND (`classInfo`.`deletedAt` IS NULL) WHERE (`t_user`.`deletedAt` IS NULL AND (`t_user`.`school_id` = 'xxx' AND `t_user`.`class_id` = 'xx'));

but correct query is

select count(`t_user`.`id`) AS `count` FROM `t_user` AS `t_user` LEFT OUTER JOIN **xxx.`t_stu_class`** AS `classInfo` ON `t_user`.`class_id` = `classInfo`.`id` AND (`classInfo`.`deletedAt` IS NULL) WHERE (`t_user`.`deletedAt` IS NULL AND (`t_user`.`school_id` = 'xxx' AND `t_user`.`class_id` = 'xx'));

I hope different database to association come true
thank you

// If applicable, add a code snippet showing how your feature would be used in a real use-case
 async getUserList(class_id, current_page, page_size = 20) {
        let { ctx, app } = this
        let { school_id } = ctx
        let limit = page_size
        let offset = (current_page - 1) * page_size
        let where = {
            school_id,
        }
        if (class_id) {
            where['class_id'] = class_id
        }
        let { rows, count } = await app.model.Student.findAndCountAll({
            where,
            offset,
            limit,
            include: [{
                association: 'classInfo',
                attributes: ['grade', 'major', 'class_name']
            }],
            order:[['id','desc']],
            attributes: ['uuid', 'nick_img', 'nick_name', 'stu_name', 'stu_phone', 'stu_number', 'enable', 'stu_gender', 'created_time'],
        })
        if (!rows) {
            return ctx.body = ctx.createMeta(400, "getUserListError:data is not  exist", [])
        }
           return ctx.body = ctx.createMeta(200, "getUsetListSuccess", { rows, count })
}

Why should this be in Sequelize

New database maybe depend on another database table,But always use raw queries to left inner join query is too slow,Every time need to think about this query statement or double query find A and then find where xxx:A.xxx in B

Describe alternatives/workarounds you've considered

1. use raw queries to left inner join query
2. double query

Additional context

Feature Request Checklist

Is this feature dialect-specific?

• No. This feature is relevant to Sequelize as a whole.
• Yes. This feature only applies to the following dialect(s):mysql

Would you be willing to implement this feature 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.

[github-actions]

This issue has been automatically marked as stale because it has been open for 14 days without activity. It will be closed if no further activity occurs within the next 14 days. If this is still an issue, just leave a comment or remove the "stale" label. 🙂

[ephys]

We don't have enough information to help you with this, please provide a SSCCE that replicates your problem.

Are you trying to join two tables that are in different databases (as in servers) or different schemas?
The former is not supported, you need to implement it in your business logic.
The latter is, I think, supported

[codecnmc]

We don't have enough information to help you with this, please provide a SSCCE that replicates your problem.

Are you trying to join two tables that are in different databases (as in servers) or different schemas? The former is not supported, you need to implement it in your business logic. The latter is, I think, supported

Yeah, I try to join two tables in different schema in same serve, not distributed server schema.
This module is under development just depend on one table in other schema,but this table is important , Create in same schema will huge tables and disorder in schema.
I‘m noob coder , I don't know this issue is useful or necessary.
Thank you for reply

[Jopchumba]

@codecnmc that's indeed awesome,as for now you may need to include it your business logic

[wielgi]

I have been using modified sequelize for some time now with multiple databases and associations.

All it takes to fix sequelize is couple lines of code at
sequelize/lib/dialects/abstract/query-generator.js:720

if (this._dialect.supports.schemas) {
  if (param.schema) {
    if (param.schema.split(".").length === 2) { //<------------------------ starts here
      const [databaseName, schemaName] = param.schema.split(".")
      table += `[${databaseName}].[${schemaName}].`;
    } else { //<-------------------------------------------------------------- ends here
      table += `${this.quoteIdentifier(param.schema)}.`;
    }
  }
  table += this.quoteIdentifier(param.tableName);
}

then all you have to do is to add database name to your model's schema like this:

{
  tableName: "users",
  schema: "databaseName.schema",
}

and end query will look like this:

FROM [databaseName].[schema].users

All cross database associations will work perfectly :-)