{"action":"created","issue":{"url":"https://api.github.com/repos/sequelize/sequelize/issues/3224","labels_url":"https://api.github.com/repos/sequelize/sequelize/issues/3224/labels{/name}","comments_url":"https://api.github.com/repos/sequelize/sequelize/issues/3224/comments","events_url":"https://api.github.com/repos/sequelize/sequelize/issues/3224/events","html_url":"https://github.com/sequelize/sequelize/issues/3224","id":58905103,"number":3224,"title":"ERROR in ON condition while doing Many to Many joins in complex queries","user":{"login":"sahilgarg25","id":4540933,"avatar_url":"https://avatars.githubusercontent.com/u/4540933?v=3","gravatar_id":"","url":"https://api.github.com/users/sahilgarg25","html_url":"https://github.com/sahilgarg25","followers_url":"https://api.github.com/users/sahilgarg25/followers","following_url":"https://api.github.com/users/sahilgarg25/following{/other_user}","gists_url":"https://api.github.com/users/sahilgarg25/gists{/gist_id}","starred_url":"https://api.github.com/users/sahilgarg25/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/sahilgarg25/subscriptions","organizations_url":"https://api.github.com/users/sahilgarg25/orgs","repos_url":"https://api.github.com/users/sahilgarg25/repos","events_url":"https://api.github.com/users/sahilgarg25/events{/privacy}","received_events_url":"https://api.github.com/users/sahilgarg25/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/sequelize/sequelize/labels/bug","name":"bug","color":"d7e102"}],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":1,"created_at":"2015-02-25T13:59:09Z","updated_at":"2015-02-25T14:18:49Z","closed_at":null,"body":"\r\nI have below 3 models\r\n```javascript\r\n\r\nmodule.exports = function(sequelize, DataTypes) {\r\n    var CATEGORY = 'category';\r\n    var TAG = 'post_tag';\r\n    var FEATURED = 'Featured';\r\n    var POST_IMAGE_KEY = '_thumbnail_id';\r\n\r\n    var termTaxonomy = sequelize.define(\"termTaxonomy\", {\r\n        id: {\r\n            type: DataTypes.BIGINT,\r\n            field: 'term_taxonomy_id',\r\n            primaryKey: true\r\n        },\r\n        taxonomy: DataTypes.STRING,\r\n        description: DataTypes.TEXT,\r\n        parent: DataTypes.BIGINT,\r\n        count: DataTypes.BIGINT\r\n    }, {\r\n        tableName: 'wp_term_taxonomy',\r\n        timestamps: false,\r\n\r\n        getterMethods: {\r\n            name: function() {\r\n                if (this.term)\r\n                    return this.term.name;\r\n                else\r\n                    return;\r\n            },\r\n            slug: function() {\r\n                if (this.term)\r\n                    return this.term.slug;\r\n                else\r\n                    return;\r\n            }\r\n        },\r\n\r\n        classMethods: {\r\n            associate: function(models) {\r\n                termTaxonomy.belongsTo(models.term, {\r\n                    foreignKey: 'term_id'\r\n                });\r\n                termTaxonomy.belongsToMany(models.post, {\r\n                    through: 'wp_term_relationships',\r\n                    foreignKey: 'term_taxonomy_id'\r\n                });\r\n            }\r\n        }\r\n    });\r\n\r\n    return termTaxonomy;\r\n};\r\n\r\n\r\nmodule.exports = function(sequelize, DataTypes) {\r\n    var termRelationship = sequelize.define(\"termRelationship\", {\r\n        objectId: {\r\n            type: DataTypes.BIGINT,\r\n            field: 'object_id',\r\n            primaryKey: true\r\n        },\r\n        termTaxonomyId: {\r\n            type: DataTypes.BIGINT,\r\n            field: 'term_taxonomy_id',\r\n            primaryKey: true\r\n        },\r\n        termOrder: {\r\n            type: DataTypes.INTEGER,\r\n            field: 'term_order'\r\n        }\r\n    }, {\r\n        tableName: 'wp_term_relationships',\r\n        timestamps: false\r\n    });\r\n    return termRelationship;\r\n};\r\n\r\n\r\nmodule.exports = function(sequelize, DataTypes) {\r\n    var PUBLISH = 'publish';\r\n    var POST = 'post';\r\n    var POST_IMAGE_KEY = '_thumbnail_id';\r\n\r\n    var post = sequelize.define(\"post\", {\r\n        id: {\r\n            type: DataTypes.BIGINT,\r\n            field: 'ID',\r\n            primaryKey: true\r\n        },\r\n        author: {\r\n            type: DataTypes.BIGINT,\r\n            field: 'post_author'\r\n        },\r\n        date: {\r\n            type: DataTypes.DATE,\r\n            field: 'post_date'\r\n        },\r\n        dateGMT: {\r\n            type: DataTypes.DATE,\r\n            field: 'post_date_gmt'\r\n        },\r\n        content: {\r\n            type: DataTypes.TEXT,\r\n            field: 'post_content'\r\n        },\r\n        title: {\r\n            type: DataTypes.TEXT,\r\n            field: 'post_title'\r\n        },\r\n        excerpt: {\r\n            type: DataTypes.TEXT,\r\n            field: 'post_excerpt'\r\n        },\r\n        status: {\r\n            type: DataTypes.STRING,\r\n            field: 'post_status'\r\n        },\r\n        commentStatus: {\r\n            type: DataTypes.STRING,\r\n            field: 'comment_status'\r\n        },\r\n        pingStatus: {\r\n            type: DataTypes.STRING,\r\n            field: 'ping_status'\r\n        },\r\n        password: {\r\n            type: DataTypes.STRING,\r\n            field: 'post_password'\r\n        },\r\n        name: {\r\n            type: DataTypes.STRING,\r\n            field: 'post_name'\r\n        },\r\n        toPing: {\r\n            type: DataTypes.TEXT,\r\n            field: 'to_ping'\r\n        },\r\n        pinged: {\r\n            type: DataTypes.TEXT,\r\n            field: 'pinged'\r\n        },\r\n        modified: {\r\n            type: DataTypes.DATE,\r\n            field: 'post_modified'\r\n        },\r\n        modifiedGMT: {\r\n            type: DataTypes.DATE,\r\n            field: 'post_modified_gmt'\r\n        },\r\n        contentFiltered: {\r\n            type: DataTypes.TEXT,\r\n            field: 'post_content_filtered'\r\n        },\r\n        parent: {\r\n            type: DataTypes.BIGINT,\r\n            field: 'post_parent'\r\n        },\r\n        guid: {\r\n            type: DataTypes.STRING,\r\n            field: 'guid'\r\n        },\r\n        menuOrder: {\r\n            type: DataTypes.INTEGER,\r\n            field: 'menu_order'\r\n        },\r\n        type: {\r\n            type: DataTypes.STRING,\r\n            field: 'post_type'\r\n        },\r\n        mimeType: {\r\n            type: DataTypes.STRING,\r\n            field: 'post_mime_type'\r\n        },\r\n        commentCount: {\r\n            type: DataTypes.BIGINT,\r\n            field: 'comment_count'\r\n        }\r\n    }, {\r\n        tableName: 'wp_posts',\r\n        timestamps: false,\r\n\r\n        getterMethods: {\r\n            imageURL: function() {\r\n                if (this.postMeta && this.postMeta.length > 0 && this.postMeta[0].post)\r\n                    return this.postMeta[0].post.guid;\r\n                else\r\n                    return;\r\n            }\r\n        },\r\n\r\n        classMethods: {\r\n            associate: function(models) {\r\n                post.belongsToMany(models.termTaxonomy, {\r\n                    through: 'wp_term_relationships',\r\n                    foreignKey: 'object_id'\r\n                });\r\n                post.hasMany(models.postMeta, {\r\n                    foreignKey: 'post_id'\r\n                });\r\n                post.hasOne(models.postViews, {\r\n                    foreignKey: 'post_id'\r\n                });\r\n            },\r\n\r\n            getMatchingPosts: function(models, key, categoryId, count) {\r\n                logger.info(key + \" id \" + categoryId);\r\n                return post.findAll({\r\n                    required: true,\r\n                    attributes: ['name', 'title', 'content'],\r\n                    include: [{\r\n                        model: models.postMeta,\r\n                        required: false,\r\n                        attributes: [],\r\n                        include: {\r\n                            model: models.post,\r\n                            required: false,\r\n                            attributes: ['guid']\r\n                        },\r\n                        where: {\r\n                            metaKey: POST_IMAGE_KEY\r\n                        }\r\n                    }, {\r\n                        model: models.termTaxonomy,\r\n                        required: true,\r\n                        attributes: [],\r\n                        where: {\r\n                            id: categoryId\r\n                        }\r\n                    }],\r\n                    where: {\r\n                        status: PUBLISH,\r\n                        type: POST,\r\n                        $or: {\r\n                            title: {\r\n                                $like: '%' + key + '%'\r\n                            },\r\n                            content: {\r\n                                $like: '%' + key + '%'\r\n                            }\r\n                        }\r\n                    },\r\n                    limit: count\r\n                });\r\n            }\r\n        }\r\n    });\r\n\r\n    return post;\r\n};\r\n\r\n```\r\n\r\nNow if I call the function \r\nmodels.post.getMatchingPosts(models, \"q\", 16, 5);\r\n\r\nBelow Query gets executed:\r\nSELECT \r\n`post`.*, \r\n`postMeta`.`meta_id` AS `postMeta.id`, \r\n`postMeta.post`.`ID` AS `postMeta.post.id`, \r\n`postMeta.post`.`guid` AS `postMeta.post.guid`, \r\n`termTaxonomies`.`term_taxonomy_id` AS `termTaxonomies.id`, \r\n`termTaxonomies.wp_term_relationships`.`term_taxonomy_id` AS `termTaxonomies.wp_term_relationships.term_taxonomy_id`, \r\n`termTaxonomies.wp_term_relationships`.`object_id` AS `termTaxonomies.wp_term_relationships.object_id` \r\nFROM (\r\n\tSELECT \r\n\t`post`.`ID` AS `id`, \r\n\t`post`.`post_name` AS `name`, \r\n\t`post`.`post_title` AS `title`, \r\n\t`post`.`post_content` AS `content` \r\n\tFROM `wp_posts` AS `post` \r\n\tWHERE \r\n\t\t(`post`.`post_title` LIKE '%q%' OR `post`.`post_content` LIKE '%q%') \r\n\t\tAND `post`.`post_status` = 'publish' \r\n\t\tAND `post`.`post_type` = 'post' AND \r\n\t\t(\tSELECT `wp_term_relationships`.`term_taxonomy_id` \r\n\t\t\tFROM `wp_term_relationships` AS `wp_term_relationships` \r\n\t\t\tINNER JOIN `wp_term_taxonomy` AS `termTaxonomy` \r\n\t\t\tON `wp_term_relationships`.`termTaxonomyId` = `termTaxonomy`.`ID` \r\n\t\t\tWHERE (`post`.`id` = `wp_term_relationships`.`object_id`) LIMIT 1 \r\n\t\t) IS NOT NULL \r\n\tLIMIT 5) AS `post` \r\nLEFT OUTER JOIN `wp_postmeta` AS `postMeta` \r\n\tON `post`.`id` = `postMeta`.`post_id` AND `postMeta`.`meta_key` = '_thumbnail_id' \r\nLEFT OUTER JOIN `wp_posts` AS `postMeta.post` \r\n\tON `postMeta`.`meta_value` = `postMeta.post`.`ID` \r\nINNER JOIN (`wp_term_relationships` AS `termTaxonomies.wp_term_relationships` \r\n\tINNER JOIN `wp_term_taxonomy` AS `termTaxonomies` \r\n\t\tON `termTaxonomies`.`term_taxonomy_id` = `termTaxonomies.wp_term_relationships`.`termTaxonomyId`) \r\n\tON `post`.`ID` = `termTaxonomies.wp_term_relationships`.`object_id` AND `termTaxonomies`.`term_taxonomy_id` = '16';\r\n\r\n\r\nThis query throws multiple error while doing many to many join between termtaxonomy and Post:\r\nError or below lines: \r\n\r\nUnknown column  (`wp_term_relationships`.`termTaxonomyId) AND (`termTaxonomy`.`ID`)\r\nON `wp_term_relationships`.`termTaxonomyId` = `termTaxonomy`.`ID` \r\n\r\nUnknown column (`termTaxonomies.wp_term_relationships`.`termTaxonomyId`)\r\nON `termTaxonomies`.`term_taxonomy_id` = `termTaxonomies.wp_term_relationships`.`termTaxonomyId`\r\n\r\nHow ever if i change the query this column name to \r\n`wp_term_relationships`.`term_taxonomy_id\r\n`termTaxonomy`.`term_taxonomy_id`\r\n`termTaxonomies.wp_term_relationships`.`term_taxonomy_id`\r\nThe query runs successfully.\r\n\r\nI am using below versions\r\nsequelize: 2.0.3\r\nsequelize-cli: 1.2.0"},"comment":{"url":"https://api.github.com/repos/sequelize/sequelize/issues/comments/75966547","html_url":"https://github.com/sequelize/sequelize/issues/3224#issuecomment-75966547","issue_url":"https://api.github.com/repos/sequelize/sequelize/issues/3224","id":75966547,"user":{"login":"mickhansen","id":699049,"avatar_url":"https://avatars.githubusercontent.com/u/699049?v=3","gravatar_id":"","url":"https://api.github.com/users/mickhansen","html_url":"https://github.com/mickhansen","followers_url":"https://api.github.com/users/mickhansen/followers","following_url":"https://api.github.com/users/mickhansen/following{/other_user}","gists_url":"https://api.github.com/users/mickhansen/gists{/gist_id}","starred_url":"https://api.github.com/users/mickhansen/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/mickhansen/subscriptions","organizations_url":"https://api.github.com/users/mickhansen/orgs","repos_url":"https://api.github.com/users/mickhansen/repos","events_url":"https://api.github.com/users/mickhansen/events{/privacy}","received_events_url":"https://api.github.com/users/mickhansen/received_events","type":"User","site_admin":false},"created_at":"2015-02-25T14:18:49Z","updated_at":"2015-02-25T14:18:49Z","body":"Thanks for the report, will take a look at a fix when i can.\r\nAny way you can provide a smaller test case, or even better provide a PR with a failing unit test to help speed the process along?"}}