Urrrgghhh... sorry - please ignore previous email.
This was simply a fail on my behalf.
For the record:
sub swap_query_and_match {
my $self = shift;
my @cols = $self->result_source->columns;
my @mapped_cols = map {
if (/^match_/) { s/match_/query_/ }
elsif (/^query_/) { s/query_/match_/ }
$_;
} @cols;
return $self->search_rs(
undef,
{
select => \@mapped_cols,
as => \@cols,
}
);
}
Should read:
sub swap_query_and_match {
my $self = shift;
my @cols = $self->result_source->columns;
my @mapped_cols = map {
my $col = $_;
if ($col =~ /^match_/) { $col =~
s/match_/query_/ }
elsif ($col =~ /^query_/) { $col =~
s/query_/match_/ }
$col;
} @cols;
return $self->search_rs(
undef,
{
select => \@mapped_cols,
as => \@cols,
}
);
}
Apologies if that ended up wasting anyone's time...
--
Dr Ian Sillitoe
Orengo Group, Structural and Molecular Biology
University College London
On 27 March 2012 12:48, Ian Sillitoe <[email protected]> wrote:
> Hello all,
>
> Apologies if this is long winded - I figured it might be useful to give
> some context. I have a table that can be simplified to:
>
> SCORES( query_id, match_id, query_length, match_length, score )
>
> which contains scores for an all against all comparison. It is only a
> half-matrix - to return all scores for a particular 'id' I use a union
> where one half searches for query_id = $ID and the other searches for
> match_id = $ID (and the latter swaps all the query/match fields around so
> the id in question is always 'query' not 'match') - i.e.
>
> SELECT
> query_id, match_id, query_length, match_length, score
> FROM
> scores
> WHERE
> query_id = "$ID" and match_id != "$ID"
>
> UNION
>
> SELECT
> query_id as match_id,
> match_id as query_id,
> query_length as match_length,
> match_length as query_length,
> score
> FROM
> scores
> WHERE
> query_id != "$ID" and match_id = "$ID"
>
> I originally had defined this as a view on a custom result source which
> worked fine in DBIC. However it's a bit of a pain to maintain (in reality
> there are more query and match related fields) - so on a recent code tidy I
> figured it would be better to try and do the union properly in DBIC.
>
> I was trying to get something like:
>
> My::Schema->resultset( 'Scores' )->force_query_id( $ID )->all;
>
> Done with:
>
> package My::Schema::ResultSet::Scores;
> use My::Moose; # provides Moose, Types and
> MooseX::Params::Validate
> extends 'DBIx::Class::ResultSet';
>
> __PACKAGE__->load_components(qw{ Helper::ResultSet::SetOperations });
>
> sub force_query_id {
> my $self = shift;
> my ( $domain_id ) = pos_validated_list( \@_,
> { isa => 'DomainID', coerce => 1 },
> );
>
> my $rs1 = $self->search_rs(
> { query_id => $domain_id, match_id => { '!=' => $domain_id } },
> );
>
> my $rs2 = $self->search_rs(
> { query_id => { '!=' => $domain_id }, match_id => $domain_id }
> )->swap_query_and_match;
>
> return $rs1->union( $rs2 );
> }
>
>
> sub swap_query_and_match {
> my $self = shift;
>
> my @cols = $self->result_source->columns;
> my @mapped_cols = map {
> if (/^match_/) { s/match_/query_/ }
> elsif (/^query_/) { s/query_/match_/ }
> $_;
> } @cols;
>
> return $self->search_rs(
> undef,
> {
> select => \@mapped_cols,
> as => \@cols,
> }
> );
> }
>
> However, this was throwing:
>
> ResultSets do not all have the same selected columns! called at
> DBIx/Class/Helper/ResultSet/SetOperations.pm line 71
>
> Which seems to be because the union needs the list of resolved 'as'
> attributes to be identical between all resultsets involved in the union.
>
> # DBIx/Class/Helper/ResultSet/SetOperations.pm:71
>
> $self->throw_exception("ResultSets do not all have the same selected
> columns!")
> unless $self->_compare_arrays($as, $attrs->{as});
>
> I can see that the fields need to be identical, but does it matter about
> the order? I changed my local version to:
>
> $self->throw_exception("ResultSets do not all have the same selected
> columns!" )
> unless $self->_compare_arrays([sort @$as], [sort
> @{$attrs->{as}}]);
>
> which now works for me (and passes the DBIx::Class::Helpers tests), but
> I'm not sure whether that was there for a reason - is that patch sane?
>
> Many thanks,
>
> --
> Dr Ian Sillitoe
> Orengo Group, Structural and Molecular Biology
> University College London
>
>
>
_______________________________________________
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/[email protected]
