Named joins

[mwielbut]

It would be incredibly useful if you could do named joins to support multiple joins against one table. Maybe something like:

.getJoin({
    best_friends: {
        _table: 'relationships',
        _apply: function(sequence) {
            return sequence.filter({
                type: 'bff'
            });
        }
    },
    family: {
        _table: 'relationships',
        _apply: function(sequence) {
            return sequence.filter({
                type: 'related'
            });
        }
    },
    relationship_count: {
        _table: 'relationships',
        _apply: function(sequence) {return sequence.count() },
        _array: false
    }
})

[neumino]

I'm not sure to understand your question. is what you are looking for the type option in hasAndBeolongsToMany?

[mwielbut]

Hi @neumino, good point. It's a bad example on my part. The exact situation I'm facing actually deals with filters on multiple fields and has a dynamic component. Suppose you had 2 tables, Company and Employee (a one-to-many relationship). I want to be able to pull out all companies and their employees and group by 3 "roles" (manager, executive, and driver) and ALSO provide a variable cityCode from the front-end to filter out only employees in a certain location.

Something like this:

.getJoin({
    managers: {
        _table: 'employee',
        _apply: function(sequence) {
            return sequence.filter({
                role: 'manager',
                location: cityCode
            });
        }
    },
    executives: {
        _table: 'employee',
        _apply: function(sequence) {
            return sequence.filter({
                role: 'executive',
                location: cityCode
            });
        }
    },
    drivers: {
        _table: 'employee',
        _apply: function(sequence) {
            return sequence.filter({
                role: 'driver',
                location: cityCode
            });
        }
    },
})

I don't think that hasAndBeolongsToMany works for this situation. Or maybe it can be enhanced?

[neumino]

Hum, I'm not sure what the problem, nothing prevents you from defining multiple relations between two models.

Company.hasMany(Employee, "manager", "id", "companyId");
Company.hasMany(Employee, "executive", "id", "companyId");
Company.hasMany(Employee, "driver", "id", "companyId");

Then you can filter things with _apply

Company.get(...).getJoin({
  manager: { _apply: function(sequence) { return sequence.filter({location: '...'}) } },
  executive: { _apply: function(sequence) { return sequence.filter({location: '...'}) } },
  driver: { _apply: function(sequence) { return sequence.filter({location: '...'}) } },
})

[mwielbut]

Sure, i guess, but they just seem like placeholders then. The hasMany for manager doesn't actually define the specific criteria to return managers. It still needs the filter in _apply to say role: 'manager'.

Either way, that's not really the end goal. I may have hundreds of roles in the Employee table. In some queries I need to return the results in 3 buckets ("manager", "executive", "driver") but other times I may have 4 ("cook", "waiter", "host", "sommelier") or 5, etc.. It really depends on what the user selects.

My question was really more at the idea of being able to define relationships at runtime for use in the query so that it could be dynamic. I thought it could be awesome to be able to define my getJoin argument dynamically and then pass it in. So if a user selects Programmer, Manager, Designer as the buckets the system could just build the JSON defining those relationships and pass it in to getJoin.

At the moment i have to return everything in one "employee" join and then just separate them on the server side. I feel like it would be more efficient if it was pushed to ReQL.

[mwielbut]

Like maybe a new _aliasOf field that could play off a relationship? So if I define this:

Company.hasMany(Employee, "employees", "id", "companyId");

Then this would be pretty cool:

.getJoin({
    managers: {
        _aliasOf: 'employees',
        _apply: function(sequence) {
            return sequence.filter({
                role: 'manager',
                location: cityCode
            });
        }
    },
    executives: {
        _aliasOf: 'employees',
        _apply: function(sequence) {
            return sequence.filter({
                role: 'executive',
                location: cityCode
            });
        }
    },
    drivers: {
        _aliasOf: 'employees',
        _apply: function(sequence) {
            return sequence.filter({
                role: 'driver',
                location: cityCode
            });
        }
    },
})

[neumino]

You cannot create relations at runtime as those are performed with indexes. The alisaOf now can be replaced with a convoluted merge

Something like this

Model.getJoin({employee: { _apply: function(sequence) {
  return sequence.filter({
    role: 'manager',
    location: cityCode
  });
}}).merge({managers: r.row("employee").getJoin({employee: { _apply: function(sequence) {
  return sequence.filter({
    role: 'driver',
    location: cityCode
  });
}}).merge({driver: r.row("employee")

[marshall007]

@mwielbut this is a pretty neat idea. In the meantime, I think a group could get you pretty close to what you want.

.getJoin({
  employees: {
    _apply: function(sequence) {
      return sequence.filter({ location: cityCode }).group('role').ungroup();
    }
  }
})

You could do some further manipulation in ReQL to get the exact data structure you're looking for if necessary, but this gets you the data in a pretty efficient way at least.

[mwielbut]

Great, thanks for the suggestions! Will try them out.