{"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":4,"created_at":"2015-02-19T14:14:41Z","updated_at":"2015-02-19T14:34:57Z","closed_at":null,"body":"Hi fellas !\r\n\r\nI'm stuck since since 2 days now, with an ambigous SQL attribute problem.\r\nI would like to list all the rooms which doesn't contain any bookings during the time laps precised.\r\n\r\nSo I found my SQL query, it goes like this :\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/75061218","html_url":"https://github.com/sequelize/sequelize/issues/3167#issuecomment-75061218","issue_url":"https://api.github.com/repos/sequelize/sequelize/issues/3167","id":75061218,"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},"created_at":"2015-02-19T14:34:57Z","updated_at":"2015-02-19T14:34:57Z","body":"Here's my room where condition : \r\n```\r\n      query.where = orm.or(\r\n        { \"bookings.dateEnd\": { lt: start }},\r\n        { \"bookings.dateStart\": { gt: end}},\r\n        orm.and(\"`Bookings`.`dateEnd` IS NULL\", \"`Bookings`.`dateStart` IS NULL\")\r\n      );\r\n```\r\nThat's generate the following SQL code :\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\nON Room.id = Bookings.roomId \r\n\tWHERE (Room.bookings.dateEnd < '2015-02-20 01:00:00' \r\n\t\tOR Room.bookings.dateStart > '2015-02-22 00:59:59' \r\n\t\tOR (Bookings.dateEnd IS NULL AND Bookings.dateStart IS NULL));\r\n\r\n``` \r\n\r\nAnd throw the SQL error : \r\n```\r\nSequelizeDatabaseError: ER_BAD_FIELD_ERROR: Unknown column 'Room.bookings.dateEnd' in 'where clause'\r\n\r\n```\r\n"}}