A way to get multi-level relations

[esatterwhite]

This really may be more of a question, but How do you get at relations of relations, etc.

I suppose the classic example here could be a User belongs to many Groups, and Groups have many Permissions.

I want to get all of the Permissions for user X.

Aside from a brute force nested for loop, Does thinky support this?

[neumino]

There is no shortcut to flatten things, but you can do:

User.belongsTo(Groups, 'group', ...);
Groups.hasAndBelongsToMany(Permissions, 'permissions', ...);

User.getJoin({groups: {permissions: true}});

[esatterwhite]

Ya i see that, but then I'm left to do work in process that the dB should be able to do easily.
I can write the query for something like this pretty easily, but then you have to know the names of the tables and indexes on the auto created tables.

But, not quite what I meant. I suppose I'm looking for a way to access things that are more than one table / join a way.

User.groups.permissions.filter({ user_id: < user.id >}).then...

It seems like a reasonable thing for an orm/odm to be able to do. Am I explaining this well enough?

[neumino]

Not really, what's the work left in process?

[neumino]

If you are trying to filter the users'id, you can do

User.filter({user_id: ,,,}).getJoin({groups: {permissions: true}});

If you want to retrieve only the permissions

User.filter({user_id: ,,,}).getJoin({groups: {permissions: true}})('group')('permissions')

If you have a hasAndBelongsTo instead of a belongsTo, you can use concatMap

User.filter({user_id: ,,,}).getJoin({groups: {permissions: true}})('groups').concatMap(function(group) {
  return group('permissions')
})

You can also specify the opposite relations and use getJoin on permissions, but it's probably less efficient if you need just one user. It kind of depends on what you are trying to do.

If you want to write

User.groups.permissions.filter({ user_id: < user.id >})

This is not possible for many reasons, one of them being that it is ambiguous as you can't specify on what filter is being applied on.

[esatterwhite]

Ah, I see. Perhaps starting at the Opposite end of the relation would make a bit more sense. And would at least provide enough information to some kind of sugar syntax.

Permission.getJoin({groups__users=<user.id>}) 

Or something to that effect. just spit-balling. I'm coming from the Django world and missing some of the relationship magic.

this syntax throws an error every time:

User.filter({user_id: ,,,}).getJoin({groups: {permissions: true}})('groups')('permissions')

User.getJoin(...) is not a function

Thanks.

[neumino]

Just to be clear, User here is a model, not a Document.

If User was indeed a document, please how you created your models/relations.

[esatterwhite]

Yes, models

User.hasAndBelongsToMany( Group,'groups', 'id','id' );
Group.hasAndBelongsToMany( User,'users', 'id','id' );

Group.hasAndBelongsToMany( Permission,'permissions', 'id','id' )
Permission.hasAndBelongsToMany( Group,'groups', 'id','id' )

[neumino]

This works for me:

var thinky = require('./lib/thinky.js')();
var type = thinky.type;


var User = thinky.createModel('user', {id: String});
var Group = thinky.createModel('group', {id: String});
var Permission = thinky.createModel('permission', {id: String});

User.hasAndBelongsToMany( Group,'groups', 'id','id' );
Group.hasAndBelongsToMany( User,'users', 'id','id' );

Group.hasAndBelongsToMany( Permission,'permissions', 'id','id' )
Permission.hasAndBelongsToMany( Group,'groups', 'id','id' )

var user = new User({});
var group = new Group({});
var permission = new Permission({});

user.groups = [group];
group.permissions = [permission];

user.saveAll().then(function() {
  User.getJoin({groups: {permissions: true}}).concatMap(function(user) {
    return user('groups').concatMap(function(group) {
      return group('permissions')
    });
  }).run().then(console.log);
})

[esatterwhite]

Hmm. that helps a little bit. But gives an array of arrays.

[
   [ perms from group A ],
   [ perms from group B ],
   [ perms from group C ]
]

Were I just want to get an unique set of permissions.

 [  ... perms from all of my groups ] 

Probably just me not knowing the reql language very well. But I got pretty close by doing this.

User.define('getAllPermissions', function( ){
    var groupLink = this._model._joins.groups.link
    var permLink  = this._model._joins.groups.model._joins.permissions.link
    var user_id = this.id;

    return new Promise(function( resolve, reject ){
        r.db('hive')
          .table('hive_auth_permission')
          .eqJoin('id'
                , r.db('hive').table(permLink)
                ,{index:'hive_auth_permission_id'}
          )
          .zip()
          .eqJoin('hive_auth_group_id'
                , r.db('hive').table(groupLink)
                ,{index:'hive_auth_group_id'}
            )
          .zip()
          .filter({hive_auth_user_id:user_id})
          .map( r.row.merge(function( row ){ 
                    return {"id":row("hive_auth_permission_id")}
                }
            )
          )
          .without(['hive_auth_permission_id'])
          .run()
          .then(function( perms ){
                resolve( perms );
          })
    });
});

But I can't seem to use .distinct() in this, and this still requires me to know a lot about some of the intermediate tables and indexes created by thinky.

Sorry if I'm talking in circles on this one.

[neumino]

I'm not sure what you executed, but this code:

var thinky = require('./lib/thinky.js')();
var type = thinky.type;


var User = thinky.createModel('user', {id: String});
var Group = thinky.createModel('group', {id: String});
var Permission = thinky.createModel('permission', {id: String});

User.hasAndBelongsToMany( Group,'groups', 'id','id' );
Group.hasAndBelongsToMany( User,'users', 'id','id' );

Group.hasAndBelongsToMany( Permission,'permissions', 'id','id' )
Permission.hasAndBelongsToMany( Group,'groups', 'id','id' )

var user1 = new User({id: 'first'});
var group1 = new Group({});
var group2 = new Group({});
var permission1 = new Permission({});
var permission2 = new Permission({});
var permission3 = new Permission({});

var permission4 = new Permission({});
var permission5 = new Permission({});

user1.groups = [group1, group2];
group1.permissions = [permission1, permission2, permission3];
group2.permissions = [permission4, permission5];

var user2 = new User({id: 'second'});
var group3 = new Group({});
var permission6 = new Permission({});
var permission7 = new Permission({});

user2.groups = [group3];
group3.permissions = [permission6, permission7];

user1.saveAll().then(function() {
  return user2.saveAll();
}).then(function() {
  User.filter({id: 'first'}).getJoin({groups: {permissions: true}}).concatMap(function(user) {
    return user('groups').concatMap(function(group) {
      return group('permissions')
    });
  }).execute().then(function(result) {
    result.toArray().then(function(result) {
      console.log(result)
    });
  });
})

Prints

[ { id: '2af36ccd-b356-4bf9-809b-7e40989f3be6' },
  { id: '7c8d9b61-7825-4701-9737-7152a818ff27' },
  { id: '0a4ab7ba-343b-4f13-bd25-6d0e5e125046' } ]

My guess is that when you copied my script, you used map instead of concatMap.

Also you can just add a distinct at the end of your query if you want. it should work without problem.

[esatterwhite]

Ya, you're right I, missed a line when I copied. Thanks

[neumino]

Glad we got it to work :)

[esatterwhite]

So if I actually want to get model instances back from that I would need to start from the other end of the relation and do something like

Permission
     .getJoin({groups: {users: true}}).concatMap(function(pm) {
    return pm('groups').merge(pm)
    }).concatMap(function(gr) {
    return gr('users').merge(gr)
    }).run().then(console.log)

Is the an efficient way to do this? Is there a better way?

[marshall007]

@esatterwhite I don't think there would be a way to do this in the query. If you call .run() to get model instances back, thinky is going to attempt to validate each document as if it were a Permission. This is similar to the issue outlined in #186, but basically thinky is going to assume the query returns Permission documents.

[marshall007]

You could do the reduction client-side, which might look something like this:

Permission.getJoin({groups: {users: true}}).run().then(function (permissions) {
  return permissions.reduce(function (all, doc) {
    return all.concat(doc, doc.groups, doc.groups.map(function (group) {
      return group.user;
    }))
  }, [])
});

[esatterwhite]

I actually got run() to work and return instances as long as I can surface the field names as top level keys. That part actually works. It just the filtering that I'm having trouble with

[neumino]

So there's no external API now (there used to be an internal API, but I think I removed it a long time ago), but you can do

var query = User.filter({id: 'first'}).getJoin({groups: {permissions: true}}).concatMap(function(user) {
    return user('groups').concatMap(function(group) {
      return group('permissions')
    });
  })
query._model = Permissions;
query.run().then(function(result) {
    result.toArray().then(function(result) {
      console.log(result)
    });
  });