[Dbix-class] Should DBIC prefix the table alias on columns in a WHERE?

[Bill Moseley]

I've discussed this on irc, and have worked around the issue I had, but I'm
still curious.

The docs for current_source_alias seems to indicate that the alias will
always be "me." -- or that it may be in the future.  And I'm not clear
"alias" is a valid attribute.

That said there's this code:

my $rs1 =3D $schema->resultset( 'Artist' )->search( { id =3D> 3 } );
print Dumper $rs1->as_query;

my $rs2 =3D $rs1->search(undef, { alias =3D> 'artist' } );
print Dumper $rs2->as_query;


This returns:

$VAR1 =3D \[
            '(SELECT me.id, me.name, me.label FROM artist me WHERE ( id =3D=
 ?
))',
            [
              'id',
              3
            ]
          ];
$VAR1 =3D \[
            '(SELECT artist.id, artist.name, artist.label FROM artist artist
WHERE ( id =3D ? ))',
            [
              'id',
              3
            ]
          ];
SELECT artist.id, artist.name, artist.label FROM artist artist WHERE ( id =
=3D
? ): '3'

So, my question is should (or can??) DBIC add the alias to the "id" in the
WHERE?  The problem, of course, is if there's a join with another table that
also has an "id" column.

I can imagine this is difficult because there are cases where one would not
want to prefix, or it was hard to determine like WHERE ( ABS(id) =3D ? ).
 But, perhaps could test has_column() for the general case.


Why would I want to set the alias?   Why did this come up?  It's obscure and
easy for me to work around, but if you must know read on:

Say my Music data base hash this hierarchy:

  label has artists has cds has tracks

And say a label has a column "disabled" that I want to check for every query
for a label's child objects.

my $artist_rs =3D $schema->resultset('Artist')->search(
    { 'me.id' =3D> $id, 'label.disabled' =3D> 0 },
    { join =3D> 'label' },
 );

my $cd_rs =3D $schema->resultset( 'CD' )->search(
    { 'me.id' =3D> $id, 'label.disabled' =3D> 0 },
    { join =3D>  { artist =3D> 'label' } },
 );

In real life, the label has a number of columns that must be tested -- and
there might be further joins to an access control table to see if the
current user has access to that label, etc.  So, to avoid repeating that
every place an object is fetched I have a method that adds those.  For
example:

sub add_label_checks {
    my $rs =3D shift;
    my $new_rs =3D $rs->search( { 'label.disabled' =3D> 0 } );

    # more tests ....

    return $new_rs;
}

Then can do this:

my $artist_rs =3D $schema->resultset('Artist')->search(
    { 'me.id' =3D> $id },
    { join =3D> 'label' },
 )

$artist_rs =3D add_label_check( $artist_rs );  # add in access checks

All that's needed is to set the join condition up to label.


The problem is then when I want to fetch a label object:

$label_rs =3D $schema->resultset( 'Label' )->search( { 'me.id' =3D> 1 } );

$label_rs =3D add_label_check( $label_rs );

That won't work because add_label_check is using "label.disabled" instead of
"me.disabled",

The obvious solutions are to 1) pass in to add_label_check() what alias to
use - add_label_check( $label.rs, 'me' ); or 2) do something like:

my $rs =3D $schema->resultset( 'Label' )->search( { 'label.id' =3D> 1 }, { =
alias
=3D> 'label' } );
$rs =3D add_label_check( $rs );

#1 is ugly, and #2 is a bit awkward because I have a base Catalyst chained
CRUD class that I use for any object that automatically adds in the primary
key lookup for GET PUT and DELETE requests.  It currently does this:

    my %criteria =3D ( 'me.' . $primary_keys[0] =3D> $id );

Of course I could do this in my CRUD controller:

    my $prefix =3D $self->alias || 'me';
    my %criteria =3D ( $prefix . '.' . $primary_keys[0] =3D> $id );

But it would be nice if I could simply do this:

    my %criteria =3D ( $primary_keys[0] =3D> $id );

Because later I could do $new_rs =3D $rs->search(undef, { alias =3D> 'label=
' }
); and DBIC would know to add the prefix to the un-prefixed "id".



-- =

Bill Moseley
moseley at hank.org
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20100512/2f0=
ce85f/attachment.htm