PATCH: Support for arbitrary SQL in relationship definition

[Daniel Ruoso]

Hi,

For some reason this patch is sitting on my local git copy for a while,
and now I'm not sure it was even sent at some point... /me--

So, here goes a patch against current 0.08 trunk to support arbitrary
SQL in the join condition with an included test, which allow something
like:

__PACKAGE__->has_many(
    cds_90s => 'DBICTest::Schema::CD',
    { 'foreign.artist' => 'self.artistid',
      'foreign.year' => \"LIKE '19%'" }
);

daniel


_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

[Peter Rabbitson-2]

Daniel Ruoso wrote:

> Hi,
>
> For some reason this patch is sitting on my local git copy for a while,
> and now I'm not sure it was even sent at some point... /me--
>
> So, here goes a patch against current 0.08 trunk to support arbitrary
> SQL in the join condition with an included test, which allow something
> like:
>
> __PACKAGE__->has_many(
>     cds_90s => 'DBICTest::Schema::CD',
>     { 'foreign.artist' => 'self.artistid',
>       'foreign.year' => \"LIKE '19%'" }
> );
>

It would be much better to introduce real arbitrary support via SQLA
where condition parsing. Only real problem is that a hashref is already
resrved for foreign./self. pairs, and what's even worse - it recently
started taking single unqualified column names too.

My vote would be to add an additional key to this hash, something like
-extra_cond => $arbitrary_sqla_where_structure. Or we could put the
join cond in the join attributes.

In any case - just allowing scalar refs is really weak and incomplete.
We already have all the code to parse arbitrarily complex conditions.
All we need is a consensus on how to do it.

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

[Daniel Ruoso]

Em Qui, 2009-06-11 às 13:10 +0200, Peter Rabbitson escreveu:
> It would be much better to introduce real arbitrary support via SQLA
> where condition parsing. Only real problem is that a hashref is already
> resrved for foreign./self. pairs, and what's even worse - it recently
> started taking single unqualified column names too.
> My vote would be to add an additional key to this hash, something like
> -extra_cond => $arbitrary_sqla_where_structure. Or we could put the
> join cond in the join attributes.

I'm not that all against it, but...

> In any case - just allowing scalar refs is really weak and incomplete.
> We already have all the code to parse arbitrarily complex conditions.
> All we need is a consensus on how to do it.

Using scalar ref to allow arbitrary SQL is consistent with SQLA, since
you can use it in regular SQLA where data structures, so I think it's
not really weak or incomplete...

daniel


_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

[Peter Rabbitson-2]

Daniel Ruoso wrote:

> Em Qui, 2009-06-11 às 13:10 +0200, Peter Rabbitson escreveu:
>> It would be much better to introduce real arbitrary support via SQLA
>> where condition parsing. Only real problem is that a hashref is already
>> resrved for foreign./self. pairs, and what's even worse - it recently
>> started taking single unqualified column names too.
>> My vote would be to add an additional key to this hash, something like
>> -extra_cond => $arbitrary_sqla_where_structure. Or we could put the
>> join cond in the join attributes.
>
> I'm not that all against it, but...
>
>> In any case - just allowing scalar refs is really weak and incomplete.
>> We already have all the code to parse arbitrarily complex conditions.
>> All we need is a consensus on how to do it.
>
> Using scalar ref to allow arbitrary SQL is consistent with SQLA, since
> you can use it in regular SQLA where data structures, so I think it's
> not really weak or incomplete...
>

The problem is that if we release this, the de-facto standard of arbitrary
join conditions will become literal SQL. We might as well all start using
CDBI :) (and besides you already can include literal SQL in {from})

As I say in [1], as soon as a viable general solution is proposed, it will
be written. If not - the 09 dev cycle will come up with a new syntax which
will solve this shortcoming once and for all. (this btw seems more likely,
as any time a discussion to solve this within 08 is solicited, everyone
goes silent)

Cheers

[1] http://lists.scsys.co.uk/pipermail/dbix-class/2009-June/008070.html

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

[Daniel Ruoso]

Em Dom, 2009-06-21 às 13:57 +0200, Peter Rabbitson escreveu:
> Daniel Ruoso wrote:
> > Using scalar ref to allow arbitrary SQL is consistent with SQLA, since
> > you can use it in regular SQLA where data structures, so I think it's
> > not really weak or incomplete...
> As I say in [1], as soon as a viable general solution is proposed, it will
> be written. If not - the 09 dev cycle will come up with a new syntax which
> will solve this shortcoming once and for all. (this btw seems more likely,
> as any time a discussion to solve this within 08 is solicited, everyone
> goes silent)

Hmm... At the risk of overlooking a lot of already settled issues, I'm
not sure I agree that the declaration of the schema is at the same level
as the query. I mean, the impact of a change in the schema declaration
is considerably smaller than a change in the query syntax.

I agree with you that a change in the query syntax needs to be generic
to the level you express, but the schema declaration is much less
fragile, imho, and thus it wouldn't be so problematic to make a cheap
choice.

I'm pushing this a bit because I'm already using this in production, in
a software that is going public this week... I wouldn't like to have it
as a monkey patch...

daniel


_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

[Rob Kinyon]

On Wed, Jun 10, 2009 at 16:23, Daniel Ruoso<[hidden email]> wrote:

> Hi,
>
> For some reason this patch is sitting on my local git copy for a while,
> and now I'm not sure it was even sent at some point... /me--
>
> So, here goes a patch against current 0.08 trunk to support arbitrary
> SQL in the join condition with an included test, which allow something
> like:
>
> __PACKAGE__->has_many(
>    cds_90s => 'DBICTest::Schema::CD',
>    { 'foreign.artist' => 'self.artistid',
>      'foreign.year' => \"LIKE '19%'" }
> );

I've been thinking about this for a while, then I forgot to reply
until ribasushi poked me about it.

I understand the desire to build something like this. Everyone wants
everything to be accessible from everywhere. Here's my problem:

A relationship is nothing more than the following:
    * An installed method that generates a resultset
    * a piece of metadata used by search() and update() to do automagical things

Your cds_90s example could be better written as follows:

    sub cds_decade {
        my $self = shift;
        my ($decade) = @_;

        return $self->cds({
            year => { like => "19$decade" },
        });
    }

    sub cds_90s { shift->cds_decade( '90' ) }

Unless, of course, you actually want to join on cds_90s, in which case
you might be better served to use a subquery in your join clause.

Now, I'm not arguing that it shouldn't be done. However, I'm still
trying to understand the usecase-space. APIs are forever - while we
have workarounds, it behooves us to think things through.

Rob

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

[Peter Rabbitson-2]

Rob Kinyon wrote:

> On Wed, Jun 10, 2009 at 16:23, Daniel Ruoso<[hidden email]> wrote:
>> Hi,
>>
>> For some reason this patch is sitting on my local git copy for a while,
>> and now I'm not sure it was even sent at some point... /me--
>>
>> So, here goes a patch against current 0.08 trunk to support arbitrary
>> SQL in the join condition with an included test, which allow something
>> like:
>>
>> __PACKAGE__->has_many(
>>    cds_90s => 'DBICTest::Schema::CD',
>>    { 'foreign.artist' => 'self.artistid',
>>      'foreign.year' => \"LIKE '19%'" }
>> );
>
> I've been thinking about this for a while, then I forgot to reply
> until ribasushi poked me about it.
>
> I understand the desire to build something like this. Everyone wants
> everything to be accessible from everywhere. Here's my problem:
>
> A relationship is nothing more than the following:
>     * An installed method that generates a resultset
>     * a piece of metadata used by search() and update() to do automagical things
>
> Your cds_90s example could be better written as follows:
>
>     sub cds_decade {
>         my $self = shift;
>         my ($decade) = @_;
>
>         return $self->cds({
>             year => { like => "19$decade" },
>         });
>     }
>
>     sub cds_90s { shift->cds_decade( '90' ) }
>
> Unless, of course, you actually want to join on cds_90s, in which case
> you might be better served to use a subquery in your join clause.
>
> Now, I'm not arguing that it shouldn't be done. However, I'm still
> trying to understand the usecase-space. APIs are forever - while we
> have workarounds, it behooves us to think things through.
>

A classical use-case is "right side condition on a left-join". There is
no way to emulate those appropriately with WHERE - the condition has to
reside in the join spec itself: i.e. you need to get ALL artists, and
all cds issued in a *specific year*. If you do a regular join - you get
only artists with CDs. If you do a (standard for has_many) left join -
you populate the right side of the join with CDs you don't want, and there
is no way to WHERE them out.

There are more contrived examples involving the "last" join hack. e.g. the
following will get me a resultset of the *last* row for every specific order.

SELECT _last.*
  FROM orders _last
  LEFT JOIN orders _next ON
        _last.order_number = _next.order_number
    AND _last.seen_time < _next.seen_time


Granted all of this can be done with subqueries, but 1) joins are much easier
on the database and 2) subqueries are not *that* flexible, and are out-right
unusable on multi-column PKs (we don't have sane SQLA multi-column IN support,
so hacks like [1] are necessary)

So there.

[1] http://dev.catalyst.perl.org/svnweb/bast/checkout/DBIx-Class/0.08/trunk/lib/DBIx/Class/Storage/DBI/MultiColumnIn.pm

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

[Rob Kinyon]

On Mon, Jun 29, 2009 at 10:13, Peter Rabbitson<[hidden email]> wrote:
> A classical use-case is "right side condition on a left-join". There is
> no way to emulate those appropriately with WHERE - the condition has to
> reside in the join spec itself: i.e. you need to get ALL artists, and
> all cds issued in a *specific year*. If you do a regular join - you get
> only artists with CDs. If you do a (standard for has_many) left join -
> you populate the right side of the join with CDs you don't want, and there
> is no way to WHERE them out.

I'm not seeing this. Code, please?

--
Thanks,
Rob Kinyon

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

[David Ihnen]

Some javascript/style in this post has been disabled (why?)

Rob Kinyon wrote:

On Mon, Jun 29, 2009 at 10:13, Peter Rabbitson[hidden email] wrote:
  

A classical use-case is "right side condition on a left-join". There is
no way to emulate those appropriately with WHERE - the condition has to
reside in the join spec itself: i.e. you need to get ALL artists, and
all cds issued in a *specific year*. If you do a regular join - you get
only artists with CDs. If you do a (standard for has_many) left join -
you populate the right side of the join with CDs you don't want, and there
is no way to WHERE them out.
    

I'm not seeing this. Code, please?

  

I want to help because this is a CRITICAL FEATURE, as far as I'm concerned.  I have to jump through hoops and repetitive queries within dbix-class to get my permissions data structure to resolve answers because of this lack... It constrains my application because I can't execute the same queries I would otherwise.

In advanced query creation, there are data sets that you cannot declare without using subqueries or multiple conditions in a join.

I can't show you code that does what he asks because it is impossible for DBIx::Class to describe that relationship. 

"Give me data on artists who didn't release a cd in 1994"

The SQL would be like

select artists.* from artists LEFT JOIN cds ON (cds.artist = artists.artist AND cd.year = 1994) WHERE cd.year IS NULL;

You can't do any other query (than utilizing a subquery to limit the joined set) to describe the same data set.  Can you think of one that you can declare in DBIx-Class without hacking in a subquery?

David



_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

[Rob Kinyon]

On Mon, Jun 29, 2009 at 19:43, David Ihnen<[hidden email]> wrote:

> Rob Kinyon wrote:
>
> On Mon, Jun 29, 2009 at 10:13, Peter Rabbitson<[hidden email]> wrote:
>
>
> A classical use-case is "right side condition on a left-join". There is
> no way to emulate those appropriately with WHERE - the condition has to
> reside in the join spec itself: i.e. you need to get ALL artists, and
> all cds issued in a *specific year*. If you do a regular join - you get
> only artists with CDs. If you do a (standard for has_many) left join -
> you populate the right side of the join with CDs you don't want, and there
> is no way to WHERE them out.
>
>
> I'm not seeing this. Code, please?
>
>
>
> I want to help because this is a CRITICAL FEATURE, as far as I'm concerned.
> I have to jump through hoops and repetitive queries within dbix-class to get
> my permissions data structure to resolve answers because of this lack... It
> constrains my application because I can't execute the same queries I would
> otherwise.
>
> In advanced query creation, there are data sets that you cannot declare
> without using subqueries or multiple conditions in a join.
>
> I can't show you code that does what he asks because it is impossible for
> DBIx::Class to describe that relationship.
>
> "Give me data on artists who didn't release a cd in 1994"
>
> The SQL would be like
>
> select artists.* from artists LEFT JOIN cds ON (cds.artist = artists.artist
> AND cd.year = 1994) WHERE cd.year IS NULL;

Your query is functionally identical to:
select artists.* from artists left join ( select cds.* from cds where
cds.year = 1994 ) cds on ( cds.artist = artist.artist ) WHERE cds.year
is null;

DBIC and SQLA have never claimed that they can generate every possible
SQL query. The only claim we try to achieve is to be able to generate
a set of queries that is 1-to-N mappable to the set of all queries.

No, this isn't the answer you're looking for. And, no, I haven't
thought through all the ramifications of being able to actually use
this from userland. But, hopefully this breaks one of the logjams

Thanks,
Rob

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

[Ivan Fomichev]

Hello, great module. Instead of arguing if it is a useful patch or not
and if it should be applied or there are weird workarounds easily
available, just make your own DBIx::Class component and release it on
CPAN. Who need will load this component. Good luck. Ivan.

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

[David Ihnen]

Ivan Fomichev wrote:
> Hello, great module. Instead of arguing if it is a useful patch or not
> and if it should be applied or there are weird workarounds easily
> available, just make your own DBIx::Class component and release it on
> CPAN. Who need will load this component. Good luck. Ivan.
>  
Can you even DO that in a component?

Essentially you're suggesting writing your own DBIx::Class::Relationship?

The thought makes my eyes water.

David



_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

[Peter Rabbitson-2]

David Ihnen wrote:

> Ivan Fomichev wrote:
>> Hello, great module. Instead of arguing if it is a useful patch or not
>> and if it should be applied or there are weird workarounds easily
>> available, just make your own DBIx::Class component and release it on
>> CPAN. Who need will load this component. Good luck. Ivan.
>>  
> Can you even DO that in a component?
>
> Essentially you're suggesting writing your own DBIx::Class::Relationship?
>
> The thought makes my eyes water.
>

And you on the other hand are suggesting that we include *some* support,
no matter how well thought through (or not). I stand by my criticism, that
the only sql-in-scalarref we could responsibly publicize is the virtual
view construct, which is built for this very purpose. Problem of course is
that the view definitions (virtual or not) are static, i.e. we can't push
placeholders into the join condition.

So really this discussion is coming down to: "A call for relationship
attribute proposals to specify query-time join conditions, harnessing the
full power of SQLA"

Cheers

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

[David Ihnen]

Some javascript/style in this post has been disabled (why?)

Peter Rabbitson wrote:

David Ihnen wrote:
  

Ivan Fomichev wrote:
    

Hello, great module. Instead of arguing if it is a useful patch or not
and if it should be applied or there are weird workarounds easily
available, just make your own DBIx::Class component and release it on
CPAN. Who need will load this component. Good luck. Ivan.
  
      

Can you even DO that in a component?

Essentially you're suggesting writing your own DBIx::Class::Relationship?

The thought makes my eyes water.

    

And you on the other hand are suggesting that we include *some* support,
no matter how well thought through (or not). 

That the feature is well thought out is important I agree.

I stand by my criticism, that
the only sql-in-scalarref we could responsibly publicize is the virtual
view construct, which is built for this very purpose. Problem of course is
that the view definitions (virtual or not) are static, i.e. we can't push
placeholders into the join condition.

So really this discussion is coming down to: "A call for relationship
attribute proposals to specify query-time join conditions, harnessing the
full power of SQLA"

  

But the question seemed to be 'I don't understand why you would need this' and 'is there a need for this feature' and I must vociferously respond "IT IS USEFULE" and "YES THERE IS".  As for 'is there a need to do it this way' to which I'd agree with you, probably not this way, its awfully cludgy to use the refs.

BTW, I am okay with the join terms *not* being query time dynamic/bindable stuff - in my application I want to join based on two separate relations between the two tables, not just one, and not using a query-time parameter that affects the relationship spec.

David


_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

[Rob Kinyon]

On Tue, Jun 30, 2009 at 11:14, David Ihnen<[hidden email]> wrote:

> But the question seemed to be 'I don't understand why you would need this'
> and 'is there a need for this feature' and I must vociferously respond "IT
> IS USEFULE" and "YES THERE IS".  As for 'is there a need to do it this way'
> to which I'd agree with you, probably not this way, its awfully cludgy to
> use the refs.
>
> BTW, I am okay with the join terms *not* being query time dynamic/bindable
> stuff - in my application I want to join based on two separate relations
> between the two tables, not just one, and not using a query-time parameter
> that affects the relationship spec.

I don't think anyone has said "This is a bad idea." For my part, I've
been pushing back to try and get some use-cases that cannot be written
as methods or rewritten with subqueries.

My point in all this is that relationships should be created when you
want to use that join in a query later or via X_related(). Otherwise,
it's nothing more than an auto-generated method that returns a
resultset against another table with a where condition set for you. I
think that a lot of people are trying to create relationships where a
relationship may not be appropriate. Just chain resultsets!

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

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

[Peter Rabbitson-2]

David Ihnen wrote:

> Peter Rabbitson wrote:
>> David Ihnen wrote:
>>  
>>> Ivan Fomichev wrote:
>>>    
>>>> Hello, great module. Instead of arguing if it is a useful patch or not
>>>> and if it should be applied or there are weird workarounds easily
>>>> available, just make your own DBIx::Class component and release it on
>>>> CPAN. Who need will load this component. Good luck. Ivan.
>>>>  
>>>>      
>>> Can you even DO that in a component?
>>>
>>> Essentially you're suggesting writing your own DBIx::Class::Relationship?
>>>
>>> The thought makes my eyes water.
>>>
>>>    
>>
>> And you on the other hand are suggesting that we include *some* support,
>> no matter how well thought through (or not).
> That the feature is well thought out is important I agree.
>> I stand by my criticism, that
>> the only sql-in-scalarref we could responsibly publicize is the virtual
>> view construct, which is built for this very purpose. Problem of course is
>> that the view definitions (virtual or not) are static, i.e. we can't push
>> placeholders into the join condition.
>>
>> So really this discussion is coming down to: "A call for relationship
>> attribute proposals to specify query-time join conditions, harnessing the
>> full power of SQLA"
>>
>>  
>
> But the question seemed to be 'I don't understand why you would need
> this' and 'is there a need for this feature' and I must vociferously
> respond "IT IS USEFULE" and "YES THERE IS".  As for 'is there a need to
> do it this way' to which I'd agree with you, probably not this way, its
> awfully cludgy to use the refs.
>
> BTW, I am okay with the join terms *not* being query time
> dynamic/bindable stuff - in my application I want to join based on two
> separate relations between the two tables, not just one, and not using a
> query-time parameter that affects the relationship spec.
>

This is available in official versions *today*. The synopsis of [1] shows
*exactly* what you want to do. Note that is_virtual(1) will keep it from
littering the database as well.

P.S. A good payment for this advice would be a cookbook entry diff :)

[1] http://search.cpan.org/~ribasushi/DBIx-Class-0.08107/lib/DBIx/Class/ResultSource/View.pm

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

[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




_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

[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

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...