{"action":"created","issue":{"url":"https://api.github.com/repos/sequelize/sequelize/issues/3936","labels_url":"https://api.github.com/repos/sequelize/sequelize/issues/3936/labels{/name}","comments_url":"https://api.github.com/repos/sequelize/sequelize/issues/3936/comments","events_url":"https://api.github.com/repos/sequelize/sequelize/issues/3936/events","html_url":"https://github.com/sequelize/sequelize/issues/3936","id":88409161,"number":3936,"title":"LEFT JOIN instead of INNER JOIN in findAndCountAll, count part","user":{"login":"Orvacko","id":1727586,"avatar_url":"https://avatars.githubusercontent.com/u/1727586?v=3","gravatar_id":"","url":"https://api.github.com/users/Orvacko","html_url":"https://github.com/Orvacko","followers_url":"https://api.github.com/users/Orvacko/followers","following_url":"https://api.github.com/users/Orvacko/following{/other_user}","gists_url":"https://api.github.com/users/Orvacko/gists{/gist_id}","starred_url":"https://api.github.com/users/Orvacko/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/Orvacko/subscriptions","organizations_url":"https://api.github.com/users/Orvacko/orgs","repos_url":"https://api.github.com/users/Orvacko/repos","events_url":"https://api.github.com/users/Orvacko/events{/privacy}","received_events_url":"https://api.github.com/users/Orvacko/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":9,"created_at":"2015-06-15T12:25:15Z","updated_at":"2015-06-25T08:54:00Z","closed_at":null,"body":"Is there any solution to do LEFT JOIN instead of INNER JOIN? I tryed to search but nothing found.\r\n\r\nExample:\r\n- i use datatables, and request for example contact.name\r\n- i use findAndCountAll method\r\n```\r\nfinder.include = [\r\n        {model: db.User, attributes: ['id', 'nickname'], paranoid: false, required: true},\r\n        {model: db.Contact, attributes: ['id', 'name', 'surname'], paranoid: false, where: {is_main_contact: true}}\r\n    ];\r\n```\r\n(count part):\r\nSELECT count(`company`.`id`) AS `count` FROM `acrm_companies` AS `company` INNER JOIN `acrm_users` AS `user` ON `company`.`created_by` = `user`.`id` INNER JOIN `acrm_contacts` AS `contact` ON `company`.`company_id` = `contact`.`company_company_id` AND `contact`.`is_main_contact` = true WHERE (`company`.`deleted` IS NULL AND (`company`.`is_latest` = true));\r\n\r\n- but, when company has no contact, than it will be not showed in result beacuse of INNER JOIN\r\n- but, i can't remove `required: true / where: {..}`, because when you search in datatables, query want to search in contact.name (it come as attribute from datatable) but it fail (because `required: false` don't include contact model)\r\n\r\nSo, solution can be LEFT JOIN, which include all companies and they have not to have contacts, something like this:\r\n\r\nSELECT count(`company`.`id`) AS `count` FROM `acrm_companies` AS `company` INNER JOIN `acrm_users` AS `user` ON `company`.`created_by` = `user`.`id` __LEFT__ JOIN `acrm_contacts` AS `contact` ON `company`.`company_id` = `contact`.`company_company_id` AND `contact`.`is_main_contact` = true WHERE (`company`.`deleted` IS NULL AND (`company`.`is_latest` = true));\r\n"},"comment":{"url":"https://api.github.com/repos/sequelize/sequelize/issues/comments/115168784","html_url":"https://github.com/sequelize/sequelize/issues/3936#issuecomment-115168784","issue_url":"https://api.github.com/repos/sequelize/sequelize/issues/3936","id":115168784,"user":{"login":"janmeier","id":1210224,"avatar_url":"https://avatars.githubusercontent.com/u/1210224?v=3","gravatar_id":"","url":"https://api.github.com/users/janmeier","html_url":"https://github.com/janmeier","followers_url":"https://api.github.com/users/janmeier/followers","following_url":"https://api.github.com/users/janmeier/following{/other_user}","gists_url":"https://api.github.com/users/janmeier/gists{/gist_id}","starred_url":"https://api.github.com/users/janmeier/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/janmeier/subscriptions","organizations_url":"https://api.github.com/users/janmeier/orgs","repos_url":"https://api.github.com/users/janmeier/repos","events_url":"https://api.github.com/users/janmeier/events{/privacy}","received_events_url":"https://api.github.com/users/janmeier/received_events","type":"User","site_admin":false},"created_at":"2015-06-25T08:54:00Z","updated_at":"2015-06-25T08:54:00Z","body":"Why should the include be kept if it is not required? The query will return both rows which have any contacts attached, and those which doesn't. The number of companies should still be the same, without and without the include part right. The only case is when required is true, and the join is limiting the number of company rows"}}