{"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":17,"created_at":"2015-06-15T12:25:15Z","updated_at":"2015-10-04T19:45:45Z","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/145380898","html_url":"https://github.com/sequelize/sequelize/issues/3936#issuecomment-145380898","issue_url":"https://api.github.com/repos/sequelize/sequelize/issues/3936","id":145380898,"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-10-04T19:45:45Z","updated_at":"2015-10-04T19:45:45Z","body":"@renatoargh Unfortunately not"}}