{"action":"created","issue":{"url":"https://api.github.com/repos/sequelize/sequelize/issues/3023","labels_url":"https://api.github.com/repos/sequelize/sequelize/issues/3023/labels{/name}","comments_url":"https://api.github.com/repos/sequelize/sequelize/issues/3023/comments","events_url":"https://api.github.com/repos/sequelize/sequelize/issues/3023/events","html_url":"https://github.com/sequelize/sequelize/issues/3023","id":55861920,"number":3023,"title":"Question: MySQL Cross-Schema Relationship","user":{"login":"CameronJ","id":1232011,"avatar_url":"https://avatars.githubusercontent.com/u/1232011?v=3","gravatar_id":"","url":"https://api.github.com/users/CameronJ","html_url":"https://github.com/CameronJ","followers_url":"https://api.github.com/users/CameronJ/followers","following_url":"https://api.github.com/users/CameronJ/following{/other_user}","gists_url":"https://api.github.com/users/CameronJ/gists{/gist_id}","starred_url":"https://api.github.com/users/CameronJ/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/CameronJ/subscriptions","organizations_url":"https://api.github.com/users/CameronJ/orgs","repos_url":"https://api.github.com/users/CameronJ/repos","events_url":"https://api.github.com/users/CameronJ/events{/privacy}","received_events_url":"https://api.github.com/users/CameronJ/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/sequelize/sequelize/labels/discussion","name":"discussion","color":"d4c5f9"},{"url":"https://api.github.com/repos/sequelize/sequelize/labels/question","name":"question","color":"02d7e1"}],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":24,"created_at":"2015-01-29T06:29:39Z","updated_at":"2015-02-23T16:25:36Z","closed_at":null,"body":"Is there a way to support a relationship that goes to 2 different schemas (they are hosted on the same database, if that makes it easier)?  I have been trying, but it seems like no matter which direction I setup the relationship, it tries to look at the same schema as the original lookup.\r\n\r\nThe code that I am using is:\r\n\r\n```javascript\r\nvar client = {\r\n\tschema1: new Sequelize(\"schema1\", \"username\", \"password\", {\r\n\t\thost: config.database.client.host,\r\n\t\tport: config.database.client.port\r\n\t}),\r\n\tschema2: new Sequelize(\"schema2\", \"username\", \"password\", {\r\n\t\thost: config.database.client.host,\r\n\t\tport: config.database.client.port\r\n\t})\r\n};\r\n\r\ntable.user = client.schema1.define(\"user\", {\r\n\tid: {type: Sequelize.INTEGER(11).UNSIGNED, primaryKey: true, autoIncrement: true},\r\n\tusername: Sequelize.STRING(255),\r\n\tcreated_time: Sequelize.DATE,\r\n\tupdated_time: Sequelize.DATE\r\n\t}, {\r\n\ttableName: \"user\",\r\n\tcreatedAt: \"created_time\",\r\n\tupdatedAt: \"updated_time\"\r\n});\r\n\r\ntable.user_x_site = client.schema1.define(\"user_x_site\", {\r\n\tuser_id: {type: Sequelize.INTEGER(11).UNSIGNED, primaryKey: true},\r\n\tsite_id: {type: Sequelize.INTEGER(11).UNSIGNED, primaryKey: true},\r\n\tcreated_time: Sequelize.DATE,\r\n\tupdated_time: Sequelize.DATE\r\n}, {\r\n\ttableName: \"user_x_site\",\r\n\tcreatedAt: \"created_time\",\r\n\tupdatedAt: \"updated_time\"\r\n});\r\n\r\ntable.site = client.schema2.define(\"client_site\", {\r\n\tid: {type: Sequelize.INTEGER(11).UNSIGNED, primaryKey: true, autoIncrement: true},\r\n\tname: Sequelize.STRING(255),\r\n\turl: Sequelize.STRING(255),\r\n\tcreated_time: Sequelize.DATE,\r\n\tupdated_time: Sequelize.DATE\r\n}, {\r\n\ttableName: \"client_site\",\r\n\tcreatedAt: \"created_time\",\r\n\tupdatedAt: \"updated_time\"\r\n});\r\n\r\ntable.user.belongsToMany(table.site, {\r\n\tthrough: table.user_x_site,\r\n\tforeignKey: \"user_id\",\r\n\totherKey: \"site_id\"\r\n});\r\ntable.site.belongsToMany(table.user, {\r\n\tthrough: table.user_x_site,\r\n\tforeignKey: \"site_id\",\r\n\totherKey: \"user_id\"\r\n});\r\n\r\n\r\nAfter the sync, I am attempting to query the table using:\r\ntable.user.findOne({\r\n\twhere: {id: 2},\r\n\tinclude: [\r\n\t\t{model: table.site}\r\n\t]\r\n}).then(function(result) {\r\n\tconsole.log(result);\r\n\tnext();\r\n}).catch(function(err) {\r\n\tnext(err);\r\n});\r\n```\r\n\r\nThanks in advance for the help."},"comment":{"url":"https://api.github.com/repos/sequelize/sequelize/issues/comments/75576023","html_url":"https://github.com/sequelize/sequelize/issues/3023#issuecomment-75576023","issue_url":"https://api.github.com/repos/sequelize/sequelize/issues/3023","id":75576023,"user":{"login":"durango","id":677683,"avatar_url":"https://avatars.githubusercontent.com/u/677683?v=3","gravatar_id":"","url":"https://api.github.com/users/durango","html_url":"https://github.com/durango","followers_url":"https://api.github.com/users/durango/followers","following_url":"https://api.github.com/users/durango/following{/other_user}","gists_url":"https://api.github.com/users/durango/gists{/gist_id}","starred_url":"https://api.github.com/users/durango/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/durango/subscriptions","organizations_url":"https://api.github.com/users/durango/orgs","repos_url":"https://api.github.com/users/durango/repos","events_url":"https://api.github.com/users/durango/events{/privacy}","received_events_url":"https://api.github.com/users/durango/received_events","type":"User","site_admin":false},"created_at":"2015-02-23T16:25:36Z","updated_at":"2015-02-23T16:25:36Z","body":"The reason for the prefix on schemas for MySQL is because MySQL doesn't support schemas (as we already know) and (this being the more important part of the logic/reason) various scripts such as Wordpress, phpMyAdmin, etc. prefix their tables names (e.g. `wp_`) to act as \"table namespaces\" in order to avoid table name collisions (kind of the point of schemas minus alleviating pain for those who are OCD lol).\r\n\r\nBecause MySQL doesn't have true support for schemas, the prefix or \"table namespacing\" has become the ... well not traditional and I don't want to say _official_ or _proper_ because that's not either.. but I guess.. the \"generic\" way of implementing schemas into MySQL and at the time I thought that was most appropriate (I really just needed schemas to work with Postgres & Sequelize but then got yelled at when I didn't make something for sqlite or mysql :P)\r\n\r\nI also wanted to quickly say, schemas is not Postgres specific (it may have seemed like that from my comment).. schemas are in fact a part of SQL-ANSI.\r\n\r\nAnd sorry for the late reply guys, just got back from the Caribbean and there was very limited internet and sand isn't the best for computers lol :)"}}