{"action":"created","issue":{"url":"https://api.github.com/repos/sequelize/sequelize/issues/3167","labels_url":"https://api.github.com/repos/sequelize/sequelize/issues/3167/labels{/name}","comments_url":"https://api.github.com/repos/sequelize/sequelize/issues/3167/comments","events_url":"https://api.github.com/repos/sequelize/sequelize/issues/3167/events","html_url":"https://github.com/sequelize/sequelize/issues/3167","id":58212926,"number":3167,"title":"[Need help] Ambigous where condition","user":{"login":"sebay00","id":765118,"avatar_url":"https://avatars.githubusercontent.com/u/765118?v=3","gravatar_id":"","url":"https://api.github.com/users/sebay00","html_url":"https://github.com/sebay00","followers_url":"https://api.github.com/users/sebay00/followers","following_url":"https://api.github.com/users/sebay00/following{/other_user}","gists_url":"https://api.github.com/users/sebay00/gists{/gist_id}","starred_url":"https://api.github.com/users/sebay00/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/sebay00/subscriptions","organizations_url":"https://api.github.com/users/sebay00/orgs","repos_url":"https://api.github.com/users/sebay00/repos","events_url":"https://api.github.com/users/sebay00/events{/privacy}","received_events_url":"https://api.github.com/users/sebay00/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":1,"created_at":"2015-02-19T14:14:41Z","updated_at":"2015-02-19T14:17:32Z","closed_at":null,"body":"Hi fellas !\r\n\r\nI'm stuck since since 2 days now, with an ambigous SQL attribute problem. \r\nHere's my problem, I would like to do this SQL query : \r\n```\r\nSELECT Room.id, Room.name, Room.capacity, Room.createdAt, Room.updatedAt, Bookings.id \r\n\tAS Bookings.id, Bookings.dateStart \r\n\tAS Bookings.dateStart, Bookings.dateEnd \r\n\tAS Bookings.dateEnd, Bookings.createdAt \r\n\tAS Bookings.createdAt, Bookings.updatedAt \r\n\tAS Bookings.updatedAt, Bookings.roomId \r\n\tAS Bookings.roomId, Bookings.creatorId \r\n\tAS Bookings.creatorId\r\nFROM Rooms AS Room\r\nLEFT OUTER JOIN Bookings AS Bookings\r\n    ON Room.id = Bookings.roomId\r\n\tWHERE (Bookings.dateEnd  IS NULL \r\n\t\tOR Bookings.dateEnd < '2015-02-20 01:00:00' \r\n\t\tOR Bookings.dateStart > '2015-02-22 00:59:59' )\r\n```\r\n\r\nHere's the sequelize code I made : \r\n```\r\n      query.include = [{\r\n        model : Sequelize.models.Booking,\r\n        required: false,\r\n        where : Sequelize.or(\r\n          { \"dateEnd\": { lt: start }},\r\n          { \"dateStart\": { gt: end}},\r\n          Sequelize.or(\"`Bookings`.`dateEnd` IS NULL\", \"`Bookings`.`dateStart` IS NULL\")\r\n        )\r\n      }];\r\n\r\n```\r\n\r\nAnd here's the generated SQL : \r\n```\r\n SELECT Room.id, Room.name, Room.capacity, Room.createdAt, Room.updatedAt, Bookings.id \r\n\t AS Bookings.id, Bookings.dateStart \r\n\t AS Bookings.dateStart, Bookings.dateEnd \r\n\t AS Bookings.dateEnd, Bookings.createdAt \r\n\t AS Bookings.createdAt, Bookings.updatedAt \r\n\t AS Bookings.updatedAt, Bookings.roomId \r\n\t AS Bookings.roomId, Bookings.creatorId \r\n\t AS Bookings.creatorId \r\n FROM Rooms AS Room \r\n LEFT OUTER JOIN Bookings AS Bookings \r\n\t ON Room.id = Bookings.roomId \r\n\t AND (Bookings.dateEnd < '2015-02-20 01:00:00' \r\n\t OR Bookings.dateStart > '2015-02-22 00:59:59' \r\n\t OR (Bookings.dateEnd IS NULL OR Bookings.dateStart IS NULL));\r\n\r\n```\r\n\r\nAS you can see, all the where condition I'd like is in the LEFT JOIN condition.\r\nI wish I could do \"Bookings.dateEnd\" in the room where condition.\r\n\r\n:sos: Help ! :sos: "},"comment":{"url":"https://api.github.com/repos/sequelize/sequelize/issues/comments/75058259","html_url":"https://github.com/sequelize/sequelize/issues/3167#issuecomment-75058259","issue_url":"https://api.github.com/repos/sequelize/sequelize/issues/3167","id":75058259,"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-19T14:17:32Z","updated_at":"2015-02-19T14:17:32Z","body":"You are adding them to `include.where` so they get added to the join.\r\nUse them on the top level where if you want them top leve."}}