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
--- /opt/local/perls/build-trunk/lib/site_perl/5.12.2/DBIx/Class/Helper/ResultSet/SetOperations.pm 2012-03-01 01:57:05.000000000 +0000
+++ /home/bsm3/sillitoe/svn/cpan/trunk/Cath-WWW/t/../../Cath/lib/DBIx/Class/Helper/ResultSet/SetOperations.pm 2012-03-27 11:16:15.000000000 +0100
@@ -68,8 +68,8 @@
my $attrs = $_->_resolved_attrs;
- $self->throw_exception('ResultSets do not all have the same selected columns!')
- unless $self->_compare_arrays($as, $attrs->{as});
+ $self->throw_exception("ResultSets do not all have the same selected columns!\n" . join(", ", sort @$as) . "\n" . join(", ", sort @{$attrs->{as}}) )
+ unless $self->_compare_arrays([sort @$as], [sort @{$attrs->{as}}]);
my ($sql, $bind) = $self->result_source->storage->_select_args_to_query(
$attrs->{from}, $attrs->{select}, $attrs->{where}, $attrs
_______________________________________________
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]
