search

dresende / node-orm2

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

Design and implement an efficient multi-table traversal api (using relational joins) #555

Open little-big-h opened 9 years ago

little-big-h commented 9 years ago

When traversing deep object graphs, more mature ORMs generate efficient SQL code that uses relational joins to traverse the connections. As far as I can tell by the documentation, there is no way to do the same in node-orm. For example, the code

var orm = require('orm');

orm.settings.set("connection.debug", true);
orm.connect('sqlite://dummy.sqlite', function(err, db) {
        var A = db.define('a', {name:{type:'text'}},{cache: false});
        var B = db.define('b', {name:{type:'text'}},{cache: false});
        var AToB = db.define('atob', {},{cache: false});
        AToB.hasOne('a',A, {reverse:'bs'});
        AToB.hasOne('b',B, {reverse:'as'});
        db.sync(function() {
                var newBs = [];
                for(var i = 0; i < 5; i++)
                        newBs.push({name:'b'+i});
                B.create(newBs, function(err,bs) {
                        var newAs = [];
                        for(var i = 0; i<5; i++)
                                newAs.push({});
                        A.create(newAs, function(err,as) {
                                var conns = [];
                                for(var a in as)
                                        for(var b in bs)
                                                conns.push({a:a,b:b});
                                AToB.create(conns, function(err, conns) {
                                        AToB.findByB({name:'b2'}).each(function(conn) {
                                                A.findByBs(conn).each(function(a) {
                                                        console.log(JSON.stringify(a));
                                                });
                                        });
                                });                                     
                        });
                });
        });
});

fetches all As that are connected to B with the id 2 using one SQL query per AToB instance (effectively calculating the join by hand). The output looks like this:

(orm/sqlite) SELECT * FROM sqlite_master WHERE type = 'table' and name = 'a'
(orm/sqlite) CREATE TABLE `a` (`name` TEXT, `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT)
(orm/sqlite) SELECT * FROM sqlite_master WHERE type = 'table' and name = 'b'
(orm/sqlite) CREATE TABLE `b` (`name` TEXT, `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT)
(orm/sqlite) SELECT * FROM sqlite_master WHERE type = 'table' and name = 'atob'
(orm/sqlite) CREATE TABLE `atob` (`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, `a_id` INTEGER, `b_id` INTEGER)
(orm/sqlite) INSERT INTO `b` (`name`) VALUES ('b0')
(orm/sqlite) INSERT INTO `b` (`name`) VALUES ('b1')
(orm/sqlite) INSERT INTO `b` (`name`) VALUES ('b2')
(orm/sqlite) INSERT INTO `b` (`name`) VALUES ('b3')
(orm/sqlite) INSERT INTO `b` (`name`) VALUES ('b4')
(orm/sqlite) INSERT INTO `a` (`name`) VALUES (NULL)
(orm/sqlite) INSERT INTO `a` (`name`) VALUES (NULL)
(orm/sqlite) INSERT INTO `a` (`name`) VALUES (NULL)
(orm/sqlite) INSERT INTO `a` (`name`) VALUES (NULL)
(orm/sqlite) INSERT INTO `a` (`name`) VALUES (NULL)
(orm/sqlite) INSERT INTO `atob` (`a_id`, `b_id`) VALUES (NULL, NULL)
(orm/sqlite) UPDATE `atob` SET `a_id` = 0 WHERE `id` = 1
(orm/sqlite) UPDATE `atob` SET `a_id` = 0, `b_id` = 0 WHERE `id` = 1
(orm/sqlite) INSERT INTO `atob` (`a_id`, `b_id`) VALUES (NULL, NULL)
(orm/sqlite) UPDATE `atob` SET `a_id` = 0 WHERE `id` = 2
(orm/sqlite) UPDATE `atob` SET `a_id` = 0, `b_id` = 1 WHERE `id` = 2
(orm/sqlite) INSERT INTO `atob` (`a_id`, `b_id`) VALUES (NULL, NULL)
(orm/sqlite) UPDATE `atob` SET `a_id` = 0 WHERE `id` = 3
(orm/sqlite) UPDATE `atob` SET `a_id` = 0, `b_id` = 2 WHERE `id` = 3
(orm/sqlite) INSERT INTO `atob` (`a_id`, `b_id`) VALUES (NULL, NULL)
(orm/sqlite) UPDATE `atob` SET `a_id` = 0 WHERE `id` = 4
(orm/sqlite) UPDATE `atob` SET `a_id` = 0, `b_id` = 3 WHERE `id` = 4
(orm/sqlite) INSERT INTO `atob` (`a_id`, `b_id`) VALUES (NULL, NULL)
(orm/sqlite) UPDATE `atob` SET `a_id` = 0 WHERE `id` = 5
(orm/sqlite) UPDATE `atob` SET `a_id` = 0, `b_id` = 4 WHERE `id` = 5
(orm/sqlite) INSERT INTO `atob` (`a_id`, `b_id`) VALUES (NULL, NULL)
(orm/sqlite) UPDATE `atob` SET `a_id` = 1 WHERE `id` = 6
(orm/sqlite) UPDATE `atob` SET `a_id` = 1, `b_id` = 0 WHERE `id` = 6
(orm/sqlite) INSERT INTO `atob` (`a_id`, `b_id`) VALUES (NULL, NULL)
(orm/sqlite) UPDATE `atob` SET `a_id` = 1 WHERE `id` = 7
(orm/sqlite) UPDATE `atob` SET `a_id` = 1, `b_id` = 1 WHERE `id` = 7
(orm/sqlite) INSERT INTO `atob` (`a_id`, `b_id`) VALUES (NULL, NULL)
(orm/sqlite) UPDATE `atob` SET `a_id` = 1 WHERE `id` = 8
(orm/sqlite) UPDATE `atob` SET `a_id` = 1, `b_id` = 2 WHERE `id` = 8
(orm/sqlite) INSERT INTO `atob` (`a_id`, `b_id`) VALUES (NULL, NULL)
(orm/sqlite) UPDATE `atob` SET `a_id` = 1 WHERE `id` = 9
(orm/sqlite) UPDATE `atob` SET `a_id` = 1, `b_id` = 3 WHERE `id` = 9
(orm/sqlite) INSERT INTO `atob` (`a_id`, `b_id`) VALUES (NULL, NULL)
(orm/sqlite) UPDATE `atob` SET `a_id` = 1 WHERE `id` = 10
(orm/sqlite) UPDATE `atob` SET `a_id` = 1, `b_id` = 4 WHERE `id` = 10
(orm/sqlite) INSERT INTO `atob` (`a_id`, `b_id`) VALUES (NULL, NULL)
(orm/sqlite) UPDATE `atob` SET `a_id` = 2 WHERE `id` = 11
(orm/sqlite) UPDATE `atob` SET `a_id` = 2, `b_id` = 0 WHERE `id` = 11
(orm/sqlite) INSERT INTO `atob` (`a_id`, `b_id`) VALUES (NULL, NULL)
(orm/sqlite) UPDATE `atob` SET `a_id` = 2 WHERE `id` = 12
(orm/sqlite) UPDATE `atob` SET `a_id` = 2, `b_id` = 1 WHERE `id` = 12
(orm/sqlite) INSERT INTO `atob` (`a_id`, `b_id`) VALUES (NULL, NULL)
(orm/sqlite) UPDATE `atob` SET `a_id` = 2 WHERE `id` = 13
(orm/sqlite) UPDATE `atob` SET `a_id` = 2, `b_id` = 2 WHERE `id` = 13
(orm/sqlite) INSERT INTO `atob` (`a_id`, `b_id`) VALUES (NULL, NULL)
(orm/sqlite) UPDATE `atob` SET `a_id` = 2 WHERE `id` = 14
(orm/sqlite) UPDATE `atob` SET `a_id` = 2, `b_id` = 3 WHERE `id` = 14
(orm/sqlite) INSERT INTO `atob` (`a_id`, `b_id`) VALUES (NULL, NULL)
(orm/sqlite) UPDATE `atob` SET `a_id` = 2 WHERE `id` = 15
(orm/sqlite) UPDATE `atob` SET `a_id` = 2, `b_id` = 4 WHERE `id` = 15
(orm/sqlite) INSERT INTO `atob` (`a_id`, `b_id`) VALUES (NULL, NULL)
(orm/sqlite) UPDATE `atob` SET `a_id` = 3 WHERE `id` = 16
(orm/sqlite) UPDATE `atob` SET `a_id` = 3, `b_id` = 0 WHERE `id` = 16
(orm/sqlite) INSERT INTO `atob` (`a_id`, `b_id`) VALUES (NULL, NULL)
(orm/sqlite) UPDATE `atob` SET `a_id` = 3 WHERE `id` = 17
(orm/sqlite) UPDATE `atob` SET `a_id` = 3, `b_id` = 1 WHERE `id` = 17
(orm/sqlite) INSERT INTO `atob` (`a_id`, `b_id`) VALUES (NULL, NULL)
(orm/sqlite) UPDATE `atob` SET `a_id` = 3 WHERE `id` = 18
(orm/sqlite) UPDATE `atob` SET `a_id` = 3, `b_id` = 2 WHERE `id` = 18
(orm/sqlite) INSERT INTO `atob` (`a_id`, `b_id`) VALUES (NULL, NULL)
(orm/sqlite) UPDATE `atob` SET `a_id` = 3 WHERE `id` = 19
(orm/sqlite) UPDATE `atob` SET `a_id` = 3, `b_id` = 3 WHERE `id` = 19
(orm/sqlite) INSERT INTO `atob` (`a_id`, `b_id`) VALUES (NULL, NULL)
(orm/sqlite) UPDATE `atob` SET `a_id` = 3 WHERE `id` = 20
(orm/sqlite) UPDATE `atob` SET `a_id` = 3, `b_id` = 4 WHERE `id` = 20
(orm/sqlite) INSERT INTO `atob` (`a_id`, `b_id`) VALUES (NULL, NULL)
(orm/sqlite) UPDATE `atob` SET `a_id` = 4 WHERE `id` = 21
(orm/sqlite) UPDATE `atob` SET `a_id` = 4, `b_id` = 0 WHERE `id` = 21
(orm/sqlite) INSERT INTO `atob` (`a_id`, `b_id`) VALUES (NULL, NULL)
(orm/sqlite) UPDATE `atob` SET `a_id` = 4 WHERE `id` = 22
(orm/sqlite) UPDATE `atob` SET `a_id` = 4, `b_id` = 1 WHERE `id` = 22
(orm/sqlite) INSERT INTO `atob` (`a_id`, `b_id`) VALUES (NULL, NULL)
(orm/sqlite) UPDATE `atob` SET `a_id` = 4 WHERE `id` = 23
(orm/sqlite) UPDATE `atob` SET `a_id` = 4, `b_id` = 2 WHERE `id` = 23
(orm/sqlite) INSERT INTO `atob` (`a_id`, `b_id`) VALUES (NULL, NULL)
(orm/sqlite) UPDATE `atob` SET `a_id` = 4 WHERE `id` = 24
(orm/sqlite) UPDATE `atob` SET `a_id` = 4, `b_id` = 3 WHERE `id` = 24
(orm/sqlite) INSERT INTO `atob` (`a_id`, `b_id`) VALUES (NULL, NULL)
(orm/sqlite) UPDATE `atob` SET `a_id` = 4 WHERE `id` = 25
(orm/sqlite) UPDATE `atob` SET `a_id` = 4, `b_id` = 4 WHERE `id` = 25
(orm/sqlite) SELECT `t1`.`id`, `t1`.`a_id`, `t1`.`b_id` FROM `atob` `t1` JOIN `b` `t2` ON `t2`.`id` = `t1`.`b_id` WHERE `t2`.`name` = 'b2'
(orm/sqlite) SELECT `t1`.`name`, `t1`.`id` FROM `a` `t1` JOIN `atob` `t2` ON `t2`.`a_id` = `t1`.`id` WHERE `t2`.`id` = 4 AND `t2`.`a_id` = 0 AND `t2`.`b_id` = 3
(orm/sqlite) SELECT `t1`.`name`, `t1`.`id` FROM `a` `t1` JOIN `atob` `t2` ON `t2`.`a_id` = `t1`.`id` WHERE `t2`.`id` = 9 AND `t2`.`a_id` = 1 AND `t2`.`b_id` = 3
(orm/sqlite) SELECT `t1`.`name`, `t1`.`id` FROM `a` `t1` JOIN `atob` `t2` ON `t2`.`a_id` = `t1`.`id` WHERE `t2`.`id` = 14 AND `t2`.`a_id` = 2 AND `t2`.`b_id` = 3
(orm/sqlite) SELECT `t1`.`name`, `t1`.`id` FROM `a` `t1` JOIN `atob` `t2` ON `t2`.`a_id` = `t1`.`id` WHERE `t2`.`id` = 19 AND `t2`.`a_id` = 3 AND `t2`.`b_id` = 3
(orm/sqlite) SELECT `t1`.`name`, `t1`.`id` FROM `a` `t1` JOIN `atob` `t2` ON `t2`.`a_id` = `t1`.`id` WHERE `t2`.`id` = 24 AND `t2`.`a_id` = 4 AND `t2`.`b_id` = 3
{"name":null,"id":1}
{"name":null,"id":2}
{"name":null,"id":3}
{"name":null,"id":4}

However, the last six (select) queries could be replaced by a single

SELECT `t1`.`name`, `t1`.`id` FROM `a` `t1` 
JOIN `atob` `t2` ON `t2`.`a_id` = `t1`.`id` 
JOIN `b` `t3` ON `t2`.`b_id` = `t3`.`id` 
WHERE `t3`.`name` = 'b2'

if the API and the implementation supported it.