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

[Moritz Onken]

Am 30.06.2009 um 18:10 schrieb 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
>

Hi David,

work is in progress to add this feature to DBIC. The proposed syntax  
can be found here: http://dev.catalyst.perl.org/svnweb/bast/revision/?rev=6890

feel free to add your own test case to this branch (http://dev.catalyst.perl.org/repos/bast/DBIx-Class/0.08/branches/extended_rels/ 
)!

cheers,

moritz