dresende / node-orm2

Object Relational Mapping
http://github.com/dresende/node-orm2
MIT License
3.07k stars 379 forks source link

Corrupted where clause on .one() with a complete object #813

Open maritaria opened 6 years ago

maritaria commented 6 years ago
function defineDatabase(db) {
    let User = db.define("user", {
        name: { type: "text", required: true, unique: true },
        last_seen: { type: 'date', time: true },
        card_url: { type: 'text' },
    });

    let Group = db.define("group", {
        name: { type: "text", required: true },
        chat_id: { type: "text", required: true, unique: true },
    });
    Group.hasMany("members", User, {
            rank: [ "unverified", "member", "admin" ],
        }, { key: true });

    let Ticket = db.define("ticket", {
        //status: [ "started", "expired", "submitted", "completed", "denied" ],
        task: { type: "text" },
        pic: { type: "text" },
        accepted: { type: "boolean", default: false },

        started_at: { type: "date", required: true, time: true },
        finish_before: { type: "date", required: true, time: true },
        signed_at: { type: "date", required: false, time: true },
    }, {
        methods: { /* ... */
        },
    });
    Ticket.hasOne("user", User, { required: true, autoFetch: true });
    Ticket.hasOne("group", Group, { required: true, autoFetch: true });
    Ticket.hasOne("admin", User, { required: false, autoFetch: true });
    User.hasOne("currentTicket", Ticket, { required: false, autoFetch: true });//workaround: disable autofetch
}

The following code creates an error:

db.models.group.get(1, (err, someGroup) => {
someGroup.getMembers().one(user, (err, member) => {});
});

Creates the following SQL if user.currentTicket is set to an instance of Ticket:

(orm/mysql) DROP TABLE IF EXISTS `user`
(orm/mysql) DROP TABLE IF EXISTS `group`
(orm/mysql) DROP TABLE IF EXISTS `group_members`
(orm/mysql) DROP TABLE IF EXISTS `ticket`
(orm/mysql) SHOW TABLES LIKE 'user'
(orm/mysql) CREATE TABLE `user` (`name` VARCHAR(255) NOT NULL, `last_seen` DATETIME, `card_url` VARCHAR(255), `id` INT(11) NOT NULL AUTO_INCREMENT, `currentticket_id` INTEGER, PRIMARY KEY (`id`))
(orm/mysql) SELECT index_name, column_name, non_unique FROM information_schema.statistics WHERE table_schema = 'fetguard' AND table_name = 'user'
(orm/mysql) CREATE UNIQUE INDEX `name_unique` ON `user` (`name`)
(orm/mysql) SHOW TABLES LIKE 'group'
(orm/mysql) CREATE TABLE `group` (`name` VARCHAR(255) NOT NULL, `chat_id` VARCHAR(255) NOT NULL, `id` INT(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`))
(orm/mysql) SELECT index_name, column_name, non_unique FROM information_schema.statistics WHERE table_schema = 'fetguard' AND table_name = 'group'
(orm/mysql) CREATE UNIQUE INDEX `chat_id_unique` ON `group` (`chat_id`)
(orm/mysql) SHOW TABLES LIKE 'group_members'
(orm/mysql) CREATE TABLE `group_members` (`group_id` INTEGER NOT NULL, `members_id` INTEGER NOT NULL, `rank` ENUM ('unverified','member','admin'), PRIMARY KEY (`group_id`, `members_id`))
(orm/mysql) SELECT index_name, column_name, non_unique FROM information_schema.statistics WHERE table_schema = 'fetguard' AND table_name = 'group_members'
(orm/mysql) CREATE INDEX `group_id_index` ON `group_members` (`group_id`)
(orm/mysql) CREATE INDEX `members_id_index` ON `group_members` (`members_id`)
(orm/mysql) SHOW TABLES LIKE 'ticket'
(orm/mysql) CREATE TABLE `ticket` (`task` VARCHAR(255), `pic` VARCHAR(255), `accepted` TINYINT(1), `started_at` DATETIME NOT NULL, `finish_before` DATETIME NOT NULL, `signed_at` DATETIME, `id` INT(11) NOT NULL AUTO_INCREMENT, `user_id` INTEGER NOT NULL, `group_id` INTEGER NOT NULL, `admin_id` INTEGER, PRIMARY KEY (`id`))
(orm/mysql) INSERT INTO `group` (`name`, `chat_id`, `id`) VALUES ('Haven', '1', NULL)
(orm/mysql) INSERT INTO `group` (`name`, `chat_id`, `id`) VALUES ('Test', '2', NULL)
(orm/mysql) INSERT INTO `user` (`name`, `last_seen`, `card_url`, `id`, `currentticket_id`) VALUES ('maritaria', NULL, NULL, NULL, NULL)
(orm/mysql) INSERT INTO `user` (`name`, `last_seen`, `card_url`, `id`, `currentticket_id`) VALUES ('clltrldmg', NULL, NULL, NULL, NULL)
(orm/mysql) SELECT `name`, `chat_id`, `id` FROM `group` WHERE `name` = 'Test' LIMIT 1
(orm/mysql) SELECT `name`, `last_seen`, `card_url`, `id`, `currentticket_id` FROM `user` WHERE `name` = 'maritaria' LIMIT 1
(orm/mysql) SELECT `name`, `last_seen`, `card_url`, `id`, `currentticket_id` FROM `user` WHERE `name` = 'clltrldmg' LIMIT 1
(orm/mysql) DELETE FROM `group_members` WHERE `group_id` = 2 AND `members_id` = 1
(orm/mysql) DELETE FROM `group_members` WHERE `group_id` = 2 AND `members_id` = 2
(orm/mysql) INSERT INTO `group_members` (`rank`, `group_id`, `members_id`) VALUES ('unverified', 2, 1)
(orm/mysql) INSERT INTO `group_members` (`rank`, `group_id`, `members_id`) VALUES ('member', 2, 2)
(orm/mysql) SELECT `name`, `last_seen`, `card_url`, `id`, `currentticket_id` FROM `user` WHERE `name` = 'maritaria' LIMIT 1
(orm/mysql) SELECT `name`, `chat_id`, `id` FROM `group` WHERE `chat_id` = '2' LIMIT 1
(orm/mysql) SELECT `t1`.`name`, `t1`.`last_seen`, `t1`.`card_url`, `t1`.`id`, `t1`.`currentticket_id`, `t2`.`rank` FROM `user` `t1` JOIN `group_members` `t2` ON `t2`.`members_id` = `t1`.`id` WHERE (`t2`.`group_id` = 2) AND (`name` = 'maritaria' AND `last_seen` IS NULL AND `card_url` IS NULL AND `id` = 1 AND `currentticket_id` IS NULL)
(orm/mysql) INSERT INTO `ticket` (`user_id`, `group_id`, `started_at`, `finish_before`, `task`, `pic`, `accepted`, `signed_at`, `id`, `admin_id`) VALUES (1, 2, '2017-10-02 18:28:10.272', '2017-10-02 18:29:10.273', 'PM me a live pic with a thumb on your nose', NULL, 0, NULL, NULL, NULL)
(orm/mysql) UPDATE `user` SET `currentticket_id` = 1 WHERE `id` = 1
(orm/mysql) SELECT `name`, `last_seen`, `card_url`, `id`, `currentticket_id` FROM `user` WHERE `name` = 'maritaria' LIMIT 1
(orm/mysql) SELECT `task`, `pic`, `accepted`, `started_at`, `finish_before`, `signed_at`, `id`, `user_id`, `group_id`, `admin_id` FROM `ticket` WHERE `id` = 1 LIMIT 1
(orm/mysql) SELECT `name`, `chat_id`, `id` FROM `group` WHERE `chat_id` = '2' LIMIT 1
(orm/mysql) SELECT `t1`.`name`, `t1`.`last_seen`, `t1`.`card_url`, `t1`.`id`, `t1`.`currentticket_id`, `t2`.`rank` FROM `user` `t1` JOIN `group_members` `t2` ON `t2`.`members_id` = `t1`.`id` WHERE (`t2`.`group_id` = 2) AND (`name` = 'maritaria' AND `last_seen` IS NULL AND `card_url` IS NULL AND `id` = 1 AND `currentticket_id` = 1 AND `currentTicket` = `task` = 'PM me a live pic with a thumb on your nose', `pic` = NULL, `accepted` = false, `started_at` = '2017-10-02 18:28:10.000', `finish_before` = '2017-10-02 18:29:10.000', `signed_at` = NULL, `id` = 1, `user_id` = 1, `group_id` = 2, `admin_id` = NULL)
Error: ER_BAD_FIELD_ERROR: Unknown column 'currentTicket' in 'where clause'