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

[Peter Rabbitson]

Bill Moseley wrote:
>     my $rs1 = $schema->resultset( 'Artist' )->search( { id => 3 } );
>     print Dumper $rs1->as_query;
> 
> ...
> 
> 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.

No. You qualify yourself as DBIC can not possibly know what you meant.

> 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 = $schema->resultset('Artist')->search(
>     { 'me.id <http://me.id>' => $id, 'label.disabled' => 0 },
>     { join => 'label' },
>  );
> 
> my $cd_rs = $schema->resultset( 'CD' )->search(
>     { 'me.id <http://me.id>' => $id, 'label.disabled' => 0 },
>     { join =>  { artist => '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 = shift;
>     my $new_rs = $rs->search( { 'label.disabled' => 0 } );
> 
>     # more tests ....
> 
>     return $new_rs;
> }
> 
> Then can do this:
> 
> my $artist_rs = $schema->resultset('Artist')->search(
>     { 'me.id <http://me.id>' => $id },
>     { join => 'label' },
>  )
> 
> $artist_rs = add_label_check( $artist_rs );  # add in access checks
> 
> All that's needed is to set the join condition up to label.

Why?

my $new_rs = $rs->search ({ 'label.disabled' => 0}, { join => 'label' });

> 
> 
> The problem is then when I want to fetch a label object:
> 
> $label_rs = $schema->resultset( 'Label' )->search( { 'me.id
> <http://me.id>' => 1 } );
> 
> $label_rs = add_label_check( $label_rs );
> 
> That won't work because add_label_check is using "label.disabled"
> instead of "me.disabled",

Right. What you should be doing in add_label_checks is simply test
if the $rs fed to you is a Label $rs or something else.

> #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 = ( 'me.' . $primary_keys[0] => $id );

This is totally wrong. This is *exactly* what current_source_alias is
for. You should use it in place of 'me'

> Of course I could do this in my CRUD controller:
> 
>     my $prefix = $self->alias || 'me';
>     my %criteria = ( $prefix . '.' . $primary_keys[0] => $id );
> 
> But it would be nice if I could simply do this:
> 
>     my %criteria = ( $primary_keys[0] => $id );
> 
> Because later I could do $new_rs = $rs->search(undef, { alias => 'label'
> } ); and DBIC would know to add the prefix to the un-prefixed "id".

Again - this will fail spectacularly when chaining is in play, and in
any case can not be done cleanly, as search-condition introspection is
simply lacking.