[Dbix-class] PATCH: Support for arbitrary SQL in relationship definition

[David Ihnen]

Rob Kinyon wrote:
> Now, if someone can provide a solid use-case, then I'll be onboard.
> But, adding features for the sake of adding features is how you end up
> with a crappy API.
>
> Rob
Lets see if I can argue the use case.  I tripped over this limitation 
and I *think* it was when I was doing this part of the implementation

I have a data structure formed like so;

Each client has a set of users.  Clients are enumerated in a separate 
table, foreign keyed from the user table.  They use the same structure 
for their hierarchy.

Each user record thus has a client value, as well as a left and a right 
value.  The data structure is maintained so that the left and right 
numbers are sequential - supervisor has left/right of 1/4, his single 
subordinate has left/right of 2/3 - on insert of another subordinate, 
supervisor becomes 1/6 and the second subordinate is 4/5.   A 
sub-subordinate on the second user would be 5/6, moving his super to 4/7 
and the top super to 1/8. 

This allows an arbitrary depth of organizational structure but allows 
straightforward queries for inheritance/supervisor permission checks.

If the client is the same and their left and/or right are between your 
own, they are your subordinate.

If the client is the same and you are between somebody else's left and 
right, they are your supervisor.

The complication comes when I want to describe the relationships 'users 
subordinates' or 'users supervisors'.  The constraint of 'in the same 
client' is part of the join term - not just a where clause.

So I'd like to say something like this in my user model class:

package DB:Schema::samp_user;

__PACKAGE__->has_many('subordinates' => 'DB::Schema::samp_user', { 
'subordinates.client' => 'samp_user.client', 'subordinates.lft' => { 
'-between' => [ 'samp_user.lft', 'samp_user.right' ] } } );

But I can't, because you can't specify complicated join conditions like 
that.

So what do you think, is that a solid use case?

David